使用PHP中的SHOW TABLES Query结果从MYSQL数据库表中回显字段数据

Question

I have a PHP script that creates a new table in a database which pre-establishes the fields: id, firstName, lastName, and some others. The table name is determined from $_GET's of firstName and lastName from the URL.

So, url.com/script.php?fn=Bob&ln=Smith yields the table name BobSmith.

When the script that creates the table is submitted, the page refreshes and values can be inserted into the table using another script.

What I'm trying to do is create a separate page that displays all of the tables while echoing the values from the firstName and lastName fields, limited to 1 result, as all of the rows in the table will have the same firstName and lastName.

So what I'm looking for is a way of showing the results from the SHOW TABLES query, but using data from each respective table.

Example:

<?php
$query1 = mysql_query("SHOW TABLES");
$result1 = mysql_list_tables("customers")
$num_rows = mysql_num_rows($result1);

for ($i = 0; $i < $num_rows; $i) {
$table_name = mysql_tablename($result1, $i);
}

$query2 = "SELECT * FROM $table_name";
$result2 = mysql_query($query2);

while($row = mysql_fetch_object($result2)) { ?>
<a href="script.php?fn=<?php echo $row->firstName; ?>&ln=<?php echo $rowLastName; ?>"><?php echo $row->firstName; ?> <?php echo $row->LastName; ?></a>
<?php } ?>

I know that's not really functional code. I'm just not sure how to put it all together or if I'm even approaching this the right way.

Answer 1

Something like:

$sql = "SHOW TABLES;"
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
   $table = $row[0];
   $sql2 = "SELECT * FROM `" . mysql_real_escape_string($table) . "`";
   $result2 = mysql_query($sql2) or die(mysql_error());
   while($data = mysql_fetch_assoc($result2)) {
          ... echo out table data here ...
   }
}

Technically, though, you shouldn't be using show tables for this. While it is a handy command, the "proper" method is to query the information_schema db:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='your_db_name'