extracting data frm mysql database and displaying in dropdown list

[bimeldip]

Hi,
i would like to display the list of tables in a database in a drop down list
then when user selects a table, the table will be dispalyed on the page.
So far i've done this:

 <?
 $dbname = 'mysql_dbname';
 
// Get records from database ().
 $sql = "show tables from $dbname";
 $result=mysql_query($sql);
 
if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}
 
 // Show records by while loop.
 while($row=mysql_fetch_row($result)){
 echo "Table: {$row[0]}\n";
 
 ?>
<option value="<? echo $row[0]?>" <?if($row[0]==$select){ echo "selected"; } ?>><? echo $row[0]; ?></option>
<?
// End while loop.
}
?>
  </select> 
  <input type="submit" name="Submit" value="Select" />
</form>
<hr>
<p>
  <?
 If you have selected from list box.
if(isset($select)&&$select!=""){
 
// Get records from database (table "name_list").
$result=mysql_query("show columns from $select");
$row=mysql_fetch_assoc($result);
?>

But i am unable to extract the list of the database into the drop down list .
Because of that i am unable to proceed with displaying the table content at the page.
Please do help.

[nathj]

Hi bimeldip

For future reference you should wrap code in code tag - it makes it easier to read.

When you say it is not working what exactly is not workiong for you - what is it that you expect to happen and what is it that is actually happening?

I have done something like this - written the results of SQL to a dropdown list and the code I use is below. I should sy that the SQL result has been put into an associative array before this code executes.
[php]
<?php
echo "<select name=" . "'" . "list" . $lnGetType . "'" . "id=" . "'" . "list" . $lnGetType . "'" . ">";
foreach($laResults as $lcDataLine)
{
echo "<option value=" . $lcDataLine['ID'] . " ";
if($lcDataLine['isDefault'])
{
echo "selected='selected'";
}
echo ">" . $lcDataLine['description'] ."</option>";
}
echo "</select>";
?>
[/php]

There are obvioulsy nuances in that snippet that are particular to my database and my query (not shown) but the general idea should get you up and running.

I find code easier to read and maintain if I don't switch between languases as I go. Keeping it all in the most appropriate language is much better.

[bimeldip]

hi,
i did say whay i was expecting out of the codes- at the end of the post
I did explain i want to display the list of the tables in the database.
And i dont really get your codes...I guess its too complex for my liking.I am currently trying to understand it. But something tells me the codes are to display the content of a field in a dropdown list not to display the list of tables found in a certain database.
thanks

[nathj]

Hi,

I know in some database applications you can select from the datasbe just the same as you can table. I don't know if that is possible in MySQL.

The one option would be to have a table, in the dtasbase, that lists all the tables in the datasbe. You could then run the select from that table and output the resuls in a drop down box using the code I posted before.

I did have a quick hunt around to see if you could run SQL on the database as a table but I couldn't find anyting.

Perhaps a new table to list the tables is required?

Cheers
nathj

[rpnew]

I'm not an expert of PHP but few suggestions (and information rater)

TO nathj

well.. it works like “show tables from db_name” in MySql...

where db_name is your database name....



TO bimeldip
try this

while(list($row)=mysql_fetch_row($result)){
echo "Table: {$row[0]}\n";
 

instead of

while($row=mysql_fetch_row($result)){
echo "Table: {$row[0]}\n";

[pbmods]

rpnew, please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

[pbmods]

Heya, bimeldip.

Try this:

mysql_select_db($dbname);
$_res = mysql_query('SHOW TABLES');
 

[bimeldip]

Hey,
Thanks for the help,i managed to display the list of the tables that are in the database in the drop down list.
Now i would like to display the content of the chosen table from the drop down list. For instance, a user choses "table A" from the drop down list,
Then the content of "table A" will be displayed below.
The content may be the fields and the columns.

<option value="<? echo $row?>" <?if($row==$select){ echo "selected"; } ?>><? echo $row; ?></option>
<?
// End while loop.
}
?>
  </select> 
  <input type="submit" name="Submit" value="Select" />
</form>
<hr>
<p>
  <?
// If you have selected from list box.
if(isset($select)&&$select!=""){
 
// Get records from database (table "name_list").
$result=mysql_query("show columns from $row");
$row1=mysql_fetch_assoc($result);
?>

[pbmods]

Bimeldip, please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.