PHP - SHOW TABLES

Ask about a PHP problem here.
Post Reply
ToxicGod
Posts: 3
Joined: Sun Nov 27, 2011 3:06 pm

PHP - SHOW TABLES

Post by ToxicGod »

Okay so I'm trying to make a database code generator.
<?php
// includes the xml file to this page
include 'settings.php';

// Reads the xml file
$hosts = new SimpleXMLElement($xmlstr);

// This value corresponds to the one in the settings.php file. This number always starts at 0!
$database_number = 0;
// Enter the number of tables in your database. This number always starts at 0!
$num_of_tables = 2;
// Gets the host name corresponding to the $num value
$host=$hosts->database[$database_number]->host;
// Gets the database name corresponding to the $num value
$name=$hosts->database[$database_number]->name;
// Gets the database username corresponding to the $num value
$username=$hosts->database[$database_number]->username;
// Gets the database password corresponding to the $num value
$password=$hosts->database[$database_number]->password;

/**
 * Don't mess with anything below!
 */
// Connects to the host
$connect = mysql_connect($host, $username, $password);
// Connects to the database
$mysql_select_db = mysql_select_db($name);

// if either connection to the host or database has failed it will kill the page
if(!$connect || !$mysql_select_db) die('Error connecting to the database! Please try again later!');

$sql = "SHOW TABLES FROM $name";
$result = mysql_query($sql);

$row = mysql_fetch_row($result);

echo "
    <form action='' method=''>
    <select>
";
for($i = 0; $i <= $num_of_tables; $i++) {
    echo "<option>{$row[$i]}</option>";
}
echo "</form></select>";
?>
My Problem is that the for loop will only put one option out instead of how ever many tables there are in the database.
I have 2 tables in my database. test and users. The code above is only adding test to the list.

Edit: I found out that the query is only returning 1 table but I don't know why.
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: PHP - SHOW TABLES

Post by jacek »

Try running SHOW TABLE in phpmyadmin, you should see the problem. It returns a list of table names, as in, one in each row not one in each column as you are trying to use.

You want to look over the query result as you would for a normal select.
while (($row = mysql_fetch_array($result)) !== false){
    // In here $row[0] contains one of the table names.
}
Something else worth pointing out, if ou just run "SHOW TABLES" with no FROM part, it will give you all of the tables that the user used in the mysql_connect() is allowed to SELECT from (ie, just their tables). That might help you simplify the code a little, maybe even your XML since you don't need to store a list of table names in there.
Image
ToxicGod
Posts: 3
Joined: Sun Nov 27, 2011 3:06 pm

Re: PHP - SHOW TABLES

Post by ToxicGod »

Thank you very much!
Post Reply