多表查询

I am looking for some input on querying multiple tables,

I currently have a list which contains the day, (each day the reports where made.)

$list = mysql_query("SELECT * FROM list ORDER BY id");

while ($row = mysql_fetch_assoc($list)){
$drop_list[] = $row['day']; 
}

My end goal is to create a query which checks a unique row from each table,

I was thinking arround the lines of something like this.

foreach ($drop_list as $v) {
$daily = mysql_query("SELECT * FROM $v WHERE ID = 1");
while ($row = mysql_fetch_assoc($daily)){
    $id     = $row['id'];
    $name   = $row['name']; 
    $age    = $row['age'];
    $day    = $row['day'];
}
echo "<tr><td>$id</td><td>$name</td><td>$age</td><td>$day</td></tr>";       
}

Then put that into a function and echo it out in between the table tag.

I am sure the code works, (Have not tested yet Typing this from tablet) but was curious if using foreach item in array query the data from DB and echo it out to give me the daily results for the id in array?

Also curious if other have different method to accomplish this?

The short answer

Running several SQL queries inside a foreach loop will hurt performance. There is properly a better solution where everything is fetched in one query. Database queries are expensive and should be optimized as much as possible to reduce loading times of the webpage and save resources for other simultaneous requests. You can download the MySQL Workbench to help write queries as well as optimize them using the analyzer tool. There are plenty of tutorials on how to use this program around the web.

A possible solution

I assume you know the tables which you want to query and that the list of them stays the same for long periods of time. I would then fetch everything inside one query using multiple SELECT statements and the UNION keyword. This assumes the columns inside the different tables are the same. By looking at the code it seems they all declare the required columns.

SELECT * FROM table1 WHERE id = 1
UNION ALL
SELECT * FROM table2 WHERE id = 1
UNION ALL
SELECT * FROM table3 WHERE id = 1

This will fetch every single row from each of the listed tables where the id equals 1. By appending the ALL keyword to the union statement we assure duplicate rows from all the tables are also returned.

One big disadvantage of this solution is that we have no reference to from which table each row originates from. If this is required some more complex SQL queries are properly necessary, but I would still recommend combining the queries into one.

Important!

Please note that the mysql_* functions are deprecated. They are not supported anymore and security holes are not patched. I strongly recommend switching to the PDO or MySQLi extensions. They provide the better solutions in security and performance for PHP.

Side note

By looking at your code I really do not understand, why you have several tables all declaring the same columns? This seems redundant to me, but maybe I lack some more insight. It would be more effective to have only one table to maintain.

I hope this can help guide you, happy coding!