PHP MySQL Drop Down List Issue

Luke Warm · February 15, 2011

Hi,

I'm developing a PHP/MySQL application to create databases much like phpMyAdmin but just for localhost.

I have everything working except in my TABLE listings. I get the TABLE listing OK, connect to a database that I specify in a SESSION, am able to list the TABLES in a drop down, and list the field attributes in a textarea.

My problem is when I do a query of the tables, the last table's field attributes are the only ones listed so I'd like to pick a table from the drop down and show it's field attributes in the textarea. So far, I've searched for a remedy to no avail.

Here's my code for the drop down after all connections are made:

echo "<form name='frm1'><p style='text-align:left;padding-left:24px;'>Available tables: ";
		echo "<select name='tables'>";
		$sql = "SHOW TABLES FROM $database";
		$result = mysql_query($sql);

		while ($row = mysql_fetch_row($result)) {
    				echo "<option value='{$row[0]}' selected>{$row[0]}</option>\n";
			$table = $row[0];
		}
		echo "</select></p>\n";
		echo "<p style='text-align:left;padding-left:24px;'>Table Fields:";
		echo "<pre style='text-align:left;padding-left:24px;font-size:1.2em;'><textarea cols='68' rows='60' name='info'>";
		$result = mysql_query("SHOW COLUMNS FROM $table");
		if (!$result) {
   				 echo 'Could not run query: ' . mysql_error();
    			exit;
		}
		if (mysql_num_rows($result) > 0) {
    				while ($row = mysql_fetch_assoc($result)) {
        			print_r($row);
    				}
		echo "\n</textarea></pre></form>";

All the information is stored in SESSION variables obtained from a form:

session_start();
$_SESSION['hostname'] = $_POST['hostname'];
$_SESSION['user'] = $_POST['user'];
$_SESSION['passwd'] = $_POST['passwd'];
$_SESSION['database'] = $_POST['database'];
$_SESSION['table'] = $_POST['table'];

If someone could help, it would be greatly appreciated. Thanks.

ronverdonk · February 17, 2011

Where do you submit the form? Btw: your form method is, by default, GET. So your $_POST will not work.

Luke Warm · February 17, 2011

The form gets submitted to itself "

<?php echo $_SERVER['PHP_SELF']; ?>

" and the method is POST.

ronverdonk · February 17, 2011

I sure must be missing some code then:

a. you specify no method, so the default method is GET

b. you have no submit link or button, so it never gets submitted.

All your code does now, as you have shown, is to show you some tables in a select box and the attributes of the last table in the select.

So where is the submit button/link?

Luke Warm · February 17, 2011

Here's the entire page code:

<?
include("includes/header.html");
session_start();
$_SESSION['hostname'] = $_POST['hostname'];
$_SESSION['user'] = $_POST['user'];
$_SESSION['passwd'] = $_POST['passwd'];
$_SESSION['database'] = $_POST['database'];
$_SESSION['table'] = $_POST['table'];
?>
<div id="container">
<div id="header">Create DB</div>
<div id="breadcrumbs">
<?php
if(!$_SESSION['passwd'] && !$_SESSION['database'] && !$_SESSION['table']){
echo "<font color='red'>Not connected to server!</font>";
} else {
echo "<font color='green'>Server: ".$_SESSION['hostname']."<br/>";

if(!$_SESSION['database']){
	echo "</font>";
}else{
	echo "Database: ".$_SESSION['database']."</font>";
}
}
?>
</div>
<div id="nav">
<p class="active"><a href="#" onclick="javascript:window.open('connect_db.php','_self');"><img src="server_conn_ico.png" align="left" />Connect</a></p>
<p><a href="create_db.php"><img src="server_folder_ico.png" align="left" />Create Database</a></p>
<p><a href="drop_db.php"><img src="drop_ico.png" align="left" />Drop Database</a></p>
<p><a href="create_table.php"><img src="status_ico.png" align="left" />Add Table</a></p>
<p><a href="add_fields.php"><img src="server_ico.png" align="left" />Add Fields</a></p>
<p><a href="delete_table.php"><img src="status_delete_ico.png" align="left" />Delete Table</a></p>
<p><a href="http://localhost/phpmyadmin/" target="_blank"><img src="phpmyadmin_ico.png" align="left" />phpMyAdmin</a></p>
<p><a href="connect_info.php"><img src="health_ico.png" align="left" />System Info</a></p>
<p><a href="error_log.php"><img src="logs_ico.png" align="left" />Server Logs</a></p>
</div>
<div id="controls"><a href="javascript:self.close()"><img src="controls.png" /></a></div>
<div id="main">
<div id="content">
<?php
$action = htmlspecialchars($_GET['action'], ENT_QUOTES);
?>

<?php if (!$action) { ?>

<h1>MySQL Connection</h1>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="create_db" method="post">

<table cellpadding="2">
	<tr>
		<td>Hostname:</td>
		<td><input type="text" name="hostname" id="hostname" value="localhost" size="20" tabindex="1" /></td>
		<td></td>
	</tr>
	<tr>
		<td>Username:</td>
		<td><input type="text" name="user" id="user" value="" size="20" tabindex="2" /></td>
		<td></td>
	</tr>
	<tr>
		<td>Password:</td>
		<td><input type="password" name="passwd" id="passwd" value="" size="20" tabindex="3" /></td>
		<td></td>
	</tr>
	<tr>
		<td>Database:</td>
		<td><input type="text" name="database" id="database" value="" size="20" tabindex="4" /></td>
		<td style="text-align:left;">(optional)</td>
	</tr>
	<tr>
		<td colspan="3" style="text-align:center;"><input type="submit" id="submit" value="  Connect  " tabindex="5" />   <input type="reset" value="   Clear   " name="reset"></td>
	</tr>
</table>

</form>

<?php } ?>

<?php if ($action == "test") {

$hostname = trim($_POST['hostname']);
$user = trim($_POST['user']);
$passwd = trim($_POST['passwd']);
$database = trim($_POST['database']);
$table = trim($_POST['table']);

$link = mysql_connect("$hostname", "$user", "$passwd");
	if (!$link) {
		echo "<h1>Could not connect to the server '" . $hostname . "'</h1>\n";
        	echo mysql_error();
	echo "<p align='center'><input type='button' value='  Back  ' onclick='javascript:history.back();'></p>";
	}else{
		//echo "<h1>Successfully connected to the server '" . $hostname . "'</h1>\n";
		printf("MySQL client info: %s\n", mysql_get_client_info());
//			printf("MySQL host info: %s\n", mysql_get_host_info());
	}
if ($link && !$database) {
	echo "<p>No database name was given.</p><p style='text-align:left;padding-left:24px;width:80%;'>Available databases:</p>\n";
	$db_list = mysql_list_dbs($link);
	echo "<pre style='text-align:left;padding-left:24px;font-size:1em;line-height:12px;'>\n";
	while ($row = mysql_fetch_object($db_list)) {
     		echo $row->Database . "\n";
	}
	echo "</pre>\n";
}
if ($database) {
    		$dbcheck = mysql_select_db("$database");
	if (!$dbcheck) {
        	echo "<p align='center'>".mysql_error()."</p>";
	echo "   <input type='button' value='  Cancel  ' onclick='javascript:history.back();'></p>";
	}else{
		echo "<form name='frm1' method='get' action=''><p style='text-align:center;width:37%;float:left;margin-top:0px;'>Available tables: ";
		echo "<select name='tables'>";

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

		while ($row = mysql_fetch_row($result)) {
    				echo "<option value='{$row[0]}' selected>{$row[0]}</option>\n";
			$table = $row[0];
		}
		echo "</select></p>";
		echo "<p style='text-align:left;padding-left:24px;float:right;width:60%;margin-top:-15px;border:1px solid #d6d6d6;'>Table Fields: $table";
		echo "<pre style='text-align:left;padding-left:24px;font-size:1.2em;width:50%;float:right;'>"; 
		$result = mysql_query("SHOW COLUMNS FROM $table");
		if (!$result) {
   				echo "<p>The database '" . $database . "' contains no tables.</p>\n";
    			exit;
		}
		if (mysql_num_rows($result) > 0) {
    				while ($row = mysql_fetch_assoc($result)) {
        			print_r ($row);
    				}

			echo "\n</pre></form>"; 
		} else {

			//echo mysql_error();
			echo '<p style=\'text-align:center;margin-bottom:-100px;\'><input type="button" value="  Create New Table  " onclick="javascript:window.open(\'create_table.php\',\'_self\');">';
			echo "   <input type='button' value='  Cancel  ' onclick='javascript:history.back();'></p>";

		}
	}
}
}

include("includes/footer.html");
?>

Hope this helps. Thank you.

kickstart · February 17, 2011

Hi

You need to either do a JOIN between the 2 mysql queries to get the tables and the detail info (never tried joining system queries like that), or store each table name as you loop round them.

The 2nd is the least efficient but probably the easiest to change your code for. Change $table to an array and just add a new element each time in the first loop. Then loop around the $table array.

Something like this (please excuse any typos)

<?
include("includes/header.html");
session_start();
$_SESSION['hostname'] = $_POST['hostname'];
$_SESSION['user'] = $_POST['user'];
$_SESSION['passwd'] = $_POST['passwd'];
$_SESSION['database'] = $_POST['database'];
$_SESSION['table'] = $_POST['table'];
?>
<div id="container">
<div id="header">Create DB</div>
<div id="breadcrumbs">
<?php
if(!$_SESSION['passwd'] && !$_SESSION['database'] && !$_SESSION['table']){
echo "<font color='red'>Not connected to server!</font>";
} else {
echo "<font color='green'>Server: ".$_SESSION['hostname']."<br/>";

if(!$_SESSION['database']){
	echo "</font>";
}else{
	echo "Database: ".$_SESSION['database']."</font>";
}
}
?>
</div>
<div id="nav">
<p class="active"><a href="#" onclick="javascript:window.open('connect_db.php','_self');"><img src="server_conn_ico.png" align="left" />Connect</a></p>
<p><a href="create_db.php"><img src="server_folder_ico.png" align="left" />Create Database</a></p>
<p><a href="drop_db.php"><img src="drop_ico.png" align="left" />Drop Database</a></p>
<p><a href="create_table.php"><img src="status_ico.png" align="left" />Add Table</a></p>
<p><a href="add_fields.php"><img src="server_ico.png" align="left" />Add Fields</a></p>
<p><a href="delete_table.php"><img src="status_delete_ico.png" align="left" />Delete Table</a></p>
<p><a href="http://localhost/phpmyadmin/" target="_blank"><img src="phpmyadmin_ico.png" align="left" />phpMyAdmin</a></p>
<p><a href="connect_info.php"><img src="health_ico.png" align="left" />System Info</a></p>
<p><a href="error_log.php"><img src="logs_ico.png" align="left" />Server Logs</a></p>
</div>
<div id="controls"><a href="javascript:self.close()"><img src="controls.png" /></a></div>
<div id="main">
<div id="content">
<?php
$action = htmlspecialchars($_GET['action'], ENT_QUOTES);
?>

<?php if (!$action) { ?>

<h1>MySQL Connection</h1>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="create_db" method="post">

<table cellpadding="2">
	<tr>
		<td>Hostname:</td>
		<td><input type="text" name="hostname" id="hostname" value="localhost" size="20" tabindex="1" /></td>
		<td></td>
	</tr>
	<tr>
		<td>Username:</td>
		<td><input type="text" name="user" id="user" value="" size="20" tabindex="2" /></td>
		<td></td>
	</tr>
	<tr>
		<td>Password:</td>
		<td><input type="password" name="passwd" id="passwd" value="" size="20" tabindex="3" /></td>
		<td></td>
	</tr>
	<tr>
		<td>Database:</td>
		<td><input type="text" name="database" id="database" value="" size="20" tabindex="4" /></td>
		<td style="text-align:left;">(optional)</td>
	</tr>
	<tr>
		<td colspan="3" style="text-align:center;"><input type="submit" id="submit" value="  Connect  " tabindex="5" />   <input type="reset" value="   Clear   " name="reset"></td>
	</tr>
</table>

</form>

<?php } ?>

<?php if ($action == "test") {

$hostname = trim($_POST['hostname']);
$user = trim($_POST['user']);
$passwd = trim($_POST['passwd']);
$database = trim($_POST['database']);
$table = trim($_POST['table']);

$link = mysql_connect("$hostname", "$user", "$passwd");
	if (!$link) {
		echo "<h1>Could not connect to the server '" . $hostname . "'</h1>\n";
        	echo mysql_error();
	echo "<p align='center'><input type='button' value='  Back  ' onclick='javascript:history.back();'></p>";
	}else{
		//echo "<h1>Successfully connected to the server '" . $hostname . "'</h1>\n";
		printf("MySQL client info: %s\n", mysql_get_client_info());
//			printf("MySQL host info: %s\n", mysql_get_host_info());
	}
if ($link && !$database) {
	echo "<p>No database name was given.</p><p style='text-align:left;padding-left:24px;width:80%;'>Available databases:</p>\n";
	$db_list = mysql_list_dbs($link);
	echo "<pre style='text-align:left;padding-left:24px;font-size:1em;line-height:12px;'>\n";
	while ($row = mysql_fetch_object($db_list)) {
     		echo $row->Database . "\n";
	}
	echo "</pre>\n";
}
if ($database) 
{
    		$dbcheck = mysql_select_db("$database");
	if (!$dbcheck) 
	{
        	echo "<p align='center'>".mysql_error()."</p>";
	echo "   <input type='button' value='  Cancel  ' onclick='javascript:history.back();'></p>";
	}
	else
	{
		echo "<form name='frm1' method='get' action=''><p style='text-align:center;width:37%;float:left;margin-top:0px;'>Available tables: ";
		echo "<select name='tables'>";

		$sql = "SHOW TABLES FROM $database";
		$result = mysql_query($sql);
		$table = array();

		while ($row = mysql_fetch_row($result)) 
		{
    			echo "<option value='{$row[0]}' selected>{$row[0]}</option>\n";
			$table[] = $row[0];
		}
		echo "</select></p>";
		if (count($table) == 0)
		{
			echo "<p>The database '" . $database . "' contains no tables.</p>\n";
		}
		else
		{
			foreach($table AS $aTable)
			{
				echo "<p style='text-align:left;padding-left:24px;float:right;width:60%;margin-top:-15px;border:1px solid #d6d6d6;'>Table Fields: $aTable";
				echo "<pre style='text-align:left;padding-left:24px;font-size:1.2em;width:50%;float:right;'>"; 
				$result = mysql_query("SHOW COLUMNS FROM $aTable");
				while ($row = mysql_fetch_assoc($result)) 
				{
					print_r ($row);
				}

				echo "\n</pre>"; 
			}
		}
		//echo mysql_error();
		echo '<p style=\'text-align:center;margin-bottom:-100px;\'><input type="button" value="  Create New Table  " onclick="javascript:window.open(\'create_table.php\',\'_self\');">';
		echo "   <input type='button' value='  Cancel  ' onclick='javascript:history.back();'></p>";
		echo "</form>"; 
	}
}
}

include("includes/footer.html");
?>

All the best

Keith

Luke Warm · February 17, 2011

Running the loops is actually better. Rather than trying to run a query each time, looping to show them all works fine.

I appreciate the help.

Thanks, Keith

Sign In

PHP MySQL Drop Down List Issue

Recommended Posts

Luke Warm

Link to comment

Share on other sites

ronverdonk

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

ronverdonk

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information