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:

Expand|Select|Wrap|Line Numbers

 
 <?

 $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

Expand|Select|Wrap|Line Numbers

 
while(list($row)=mysql_fetch_row($result)){

echo "Table: {$row[0]}\n";

instead of

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 
<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.

extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

re: extracting data frm mysql database and displaying in dropdown list

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about