[PHP] Using ine table to search another?

Seachmall · 10-10-2007 3:14pm #1

I have two tables:

Table A:
___________________  
|ID | Name | Surname |
|1  | John  | Smith     |
|2  | Jane  | Smith     |
|__|______|________|

Table B:
 __________________
| ID |  Hair    | Eyes |
| 1  | Brown  | Brown|
| 2  |  Blonde| Blue  |
|___|_______|_____ |

(^^Its kinda difficult to make out but you get the jist)

and I need to search for and get the name of everyone who has blue eyes. How can I do this (remebering that there will be more than listed above)? I'll need to store them in an array but how do I use that array in an sql command?

This is what I have:

[PHP]$newId = mysql_query("SELECT IdNo FROM TableB WHERE Eyes=Blue");
$newIdArray=mysql_fetch_array($newId);
$new = mysql_query("SELECT * FROM TableA WHERE IdNo = $newIdArray");
$new_row=mysql_fetch_array($new);[/PHP]

Any help appreciated,
Thanks

Dun · 10-10-2007 4:09pm

Use an SQL join to bring both back at once.

SELECT tableA.name, tableB.data

FROM    tableA
INNER JOIN tableB
ON (tableA.key = tableB.key)

seamus · 10-10-2007 4:35pm

Easiest way to construct JOINs is to start with a single table.

How would you get a list of rows from table B that have blue eyes?

So this would be

select tableb.* from tableb WHERE eyes = 'Blue'

Using your example, this will return the below results:

-----------------------
| ID |  Hair   | Eyes |
-----------------------
| 2  |  Blonde | Blue |
-----------------------

Now, you just need to link table A to that. Because we know that ID is unique in table A, we don't need to do anything fancy with the joins.

The below query will link the two tables:

SELECT tableA.*, tableB.*
FROM tableA, tableB
WHERE
tableB.eyes = 'Blue'
AND tableA.ID = tableB.ID

This query will give the below result

--------------------------------------------
|ID | Name | Surname | ID |  Hair   | Eyes |
--------------------------------------------
| 2 | Jane | Smith   | 2  |  Blonde | Blue |
--------------------------------------------

Seachmall · 11-10-2007 7:01pm

Is there a way to select all from a table except one?

SELECT tableB. * EXCEPT 'IdNo' or something to that effect?

Thanks for the help.

seamus · 11-10-2007 7:06pm

Heh, no. If you don't want all of the columns, then you need to specify the ones that you do want.

forbairt · 11-10-2007 8:49pm

As Séamus said ...

SELECT
 tA.ID as ID,
 tA.Name as Name,
 tA.Surname as Surname,
 tB.Hair as Hair,
 tB.Eyes as Eyes
FROM 
  tableA as tA, 
  tableB as tB
WHERE
tB.eyes = 'Blue'
AND tA.ID = tB.ID

You'll have to select them individually ....

of course you may not have to say tableA.Name ... Name you should get away with as it isn't duplicated in the two tables

[PHP] Using ine table to search another?

Comments