Junxiang Bai Junxiang Bai - 10 months ago 38
MySQL Question

How do you select data from two mySQL tables and sort them in php?

I am working on a project which requires me to cross reference two tables. So the first table will show what item belong to which options (eg. itemA, itemB, itemC in option1 and itemB, itemC, itemD in option2). In the second table, it is a temporary table which shows which item is scanned (1 for scanned and 2 for not scanned).

When the user lands on the page, they are supposed to see a table which shows the items inside an option and the status of the option. When I use one single select statement, I am able to select the items under option1 which are scanned, but the items which are unscanned will not display. Is there a way to run two select statement to retrieve the data from both table then display onto the page?

Table 1 items
ID | itemName | Options
1 | Torchlight | 1,3
2 | Baskets | 2,3
3 | Banner | 1,2

Table 2 scanned
ID | itemName | Status
1 | TorchLight | 0
2 | Baskets | 1
3 | Banner | 1

Answer Source

You can run as many database queries as you need to retrieve all the data needed for your page. Databases and PHP will perform different tasks differently and this is why it's important to understand strenghts and weaknesses of both PHP and your DB engine, but there's no restriction to it.

Perhaps you're running your queries with mysql_query PHP function, which will return a result resource, feel free to store this resource to a variable and run your other select statement the same way, storing the result resource in another variable.

For example, let's say the following code queries the database for your tables and check them to print your HTML:

$sql1 = 'SELECT option , item FROM table1';
$query1 = mysql_query($sql1);

$sql2 = 'SELECT item, scan FROM table2';
$query2 = mysql_query($sql2);

$items_status = array();
while ($row = mysql_fetch_assoc($query2)) {
   $items_status{$row{'item'}} = $row{'scan'};

while ($row = mysql_fetch_assoc($query1)) {
    if (array_key_exists($row{'item'}, $items_status) && $items_status{$row{'item'}} == 1) {
        // print your html for scanned element
    } else {
        // print your html for not scanned element

Hope it helps you understand how to relate data.

As very well pointed by @Shadow, this is not the best way to do it, the mysql_* functions are not the best choice as they are outdated, perhaps using PDO or mysqli functions would be better. As @Fred -ii- pointed, maybe your need can be solved with a subquery or a join query too.

My sole intention here was to demonstrate that yes, you can do more than one database query per request. As I said in the begin of the answer, to understand well the database and the programing language you're using will help you take the best decisions and do the best to maintain your code clean and achieve good performances.