Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHP MySQL Select ID from one table and information from another table

Tags:

php

mysql

I have two tables, one table is called queuelist and the other is call info. In the queuelist table it just lists different IDs. I am trying to get the 'clientID' from that table and match it with the 'ID' in the other table that contains all of the info and display it back on the page. Here is how the tables look:

Table - queuelist

ID | clientID
-------------
1  | 589
2  | 254
3  | 486

Table - info

ID   | Name  | Phone
--------------------
256  | Bob   | 5551231234
486  | Jack  | 5551231234
589  | Jill  | 5551231234
like image 965
austinh Avatar asked Dec 13 '11 15:12

austinh


People also ask

WHERE clause is used in MySQL for?

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.


2 Answers

This is what they call joining tables, you should use a query like this:

SELECT i.ID, i.Name, i.Phone FROM `queuelist` AS q
LEFT JOIN `info` AS i ON (
    q.clientID = i.ID
);

I'm using aliases for shorter notation in the above query (queuelist becomes q and info becomes i) and then set the join condition (the bit between the ON()) to be the clientID from the queuelist table should match the ID in the info table.

Also see http://dev.mysql.com/doc/refman/5.0/en/join.html for more details.

like image 77
Oldskool Avatar answered Sep 22 '22 03:09

Oldskool


You need to use an inner join

  select * from queuelist as ql inner join info as i on ql.clientID = i.ID

Though you might want to replace * with specific field names e.g

  select ql.clientID, i.fieldname FROM....
like image 21
TommyBs Avatar answered Sep 20 '22 03:09

TommyBs