Extracting table info after using myql_list_tables.

[clive73]

Hi all,

Hopefully someone can show me where I'm going wrong.

I'm attempting to write some code that will list all the tables in a database and then extract the same information from each table. The output would be something like:

TABLE_1
info1
info2

TABLE_2
info1
info2

etc.....

I can get it to list all the tables (the number could vary - so not want to hard code) by using:

$result= mysql_list_tables(database);

while ($row = mysql_fetch_row($result))
{
print "$row[0]";
}

and can select the required information from all the tables if I list them individually. How do I combine the two? I thought that the select code would go inside the while loop (a loop within a loop would appear to be the logical approach as I have similar commands working in c++) but it throws up mysql errors.


thanks for any advice!

Clive.

[ShrineDesigns]

try

$data = array();

$db = mysql_connect();
mysql_select_db('db', $db);

$tbls = mysql_list_tables();

while(list($tbl) = each($tbls))
{
    $result = mysql_query("SELECT `info1`, `info2` FROM `$tbl`");

    while($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        $data[] = $row
    }
    mysql_free_result($result);
} 

[NogDog]

FYI, mysql_list_tables() is deprecated, and the prefered method to get that info is now...

$result = mysql_query("SHOW TABLES"); 

[clive73]

Thanks for that. Can get the tables listed fine but experiencing problems with displaying the actual data. I've tried putting the display loop inside the while ($row....) loop and outside.

while(list($tbl) = each($tbls)) 
{ 
    $result = mysql_query("SELECT `info1`, `info2` FROM `$tbl`"); 

    while($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
    { 
        $data[] = $row 
 /* PUT LOOP HERE ?? */
    } 
    mysql_free_result($result); 
} 

I get the error:

Warning: Variable passed to each() is not an array or object in multitables3.php, with the line referring to the while (list($tbl) = each($tbls)) line.

Would I be correct in thinking that all the data is read into the $data array and then can be simply displayed using the using method of:

while ($a_row = mysql_fetch_row($data))   
{
  foreach ($a_row as $field)
    print "$field";
} 

This works fine when reading from a single table. Apologies for what is probably an easy answer but I'm still very new to all this.

Thanks,

Clive.

[ShrineDesigns]

FYI, mysql_list_tables() is deprecated, and the prefered method to get that info is now...

$result = mysql_query("SHOW TABLES"); 

i couldn't remember the syntax for that, so i did it that way, thanks

this should work

 $data = array();

$db = mysql_connect();
mysql_select_db('db', $db);

$tbls = @mysql_query("SHOW TABLES", $db);

while(list($tbl) = mysql_fetch_array($tbls, MYSQL_NUM))
{
    $result = @mysql_query("SELECT `info1`, `info2` FROM `$tbl`", $db);

    while($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        $data[] = $row
    }
    mysql_free_result($result);
} 

[clive73]

It doesn't seem to like the nested loop or the mysql_free_result lines. Throws up the errors:

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

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in multitables.php on line 42

And to display the data should it be after the two while loops - after filling the $data array - or immediately at that point?

Thanks.