Double While loop not working

Question

Based on advice that I have been given on StackOverflow, I tried the query below but it did not work. I'm trying to get a list of the 25 most recently-added values for "site" in the database, regardless of what table they are in. The code below give the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in domain.php on line 82

Line 82 has while ($rowa = mysql_fetch_array($indexa))

Any ideas why it's not working?

echo "<table class=\"samples\">";
$index = mysql_query("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='sitefeather'");
while ($row = mysql_fetch_array($index))
{

$indexa = mysql_query("select site FROM index order by createdatetime desc limit 25");
while ($rowa = mysql_fetch_array($indexa))
{

  echo '<tr><td><a href="sitelookup3.php?entry='.urlencode($rowa['site']).'&searching=yes&search=search">'.$rowa['site'].'</a></td></tr>';
}

}
echo "</table>";

Answer 1

You probably want a variable in there in place of index. Maybe this?

$indexa = mysql_query("select site FROM {$row['TABLE_NAME']} order by createdatetime desc limit 25");

However, um... what are you doing? I don't know what exactly you're trying to accomplish but there are very loud alarms bells going off in my head. Having a dynamic table name in a query is a major red flag and is a sign of poor database design.

My database has a variable number of tables, all with the same structure.

That's bad.

What's in these tables? Let us help you get all this data into one table.

The most straightforward way is to create a single table with an extra column containing the name of the table you're currently storing each row in. Instead of having tables "foo", "bar", and "baz", create a single table with a column containing either "foo", "bar", or "baz" as the string value.

Answer 2

Your innerquery is not relevant with the outer query, you might like to try this

<?php
echo "<table class=\"samples\">";
$index = mysql_query("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='jammulinks'");
while ($row = mysql_fetch_row($index))
{
$indexa = mysql_query("select site FROM $row[0] order by createdatetime desc limit 25");//assuming you have site and createddatetime column there.
while ($rowa = mysql_fetch_array($indexa))
{

  echo '<tr><td><a href="sitelookup3.php?entry='.urlencode($rowa['site']).'&searching=yes&search=search">'.$rowa['site'].'</a></td></tr>';
}

}
echo "</table>";
?>

Answer 3

The query

select site FROM index order by createdatetime desc limit 25

shouldn't work. "index" is a reserved word.

Do you want to use $row['TABLE_NAME'] in there instead?

$indexa = mysql_query("select site FROM " + $row['TABLE_NAME'] + " order by createdatetime desc limit 25");

Answer 4

I think what you're trying to do here, is use the first query ($index) to do a SELECT on all the table names returned. In that case, you should be doing something like this:

echo "<table class=\"samples\">";
// Get a list of table names from the schema
$index = mysql_query("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='sitefeather'");
while ($row = mysql_fetch_array($index))
{
    // For every table in the schema, select site from it
    $indexa = mysql_query("select site FROM {$row['table_name']} order by createdatetime desc limit 25");
    while ($rowa = mysql_fetch_array($indexa))
    {
        echo '<tr><td><a href="sitelookup3.php?entry='.urlencode($rowa['site']).'&searching=yes&search=search">'.$rowa['site'].'</a></td></tr>';
    }

}
echo "</table>";

I'm not sure if it will error if the site column does not exist in some of the tables you're querying, but be aware.

Answer 5

Consider that you can use reserved words for table names and columns if you escape the token with backticks or as a dereference of a table.

mysql> create table `index` ( id int(11) );
Query OK, 0 rows affected (0.06 sec)

mysql> show tables like 'index';
+----------------------------+
| Tables_in_umbrella (index) |
+----------------------------+
| index                      | 
+----------------------------+
1 row in set (0.00 sec)

mysql> select * from `index`;
Empty set (0.00 sec)