Luke Warm Posted February 15, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
ronverdonk Posted February 17, 2011 Share Posted February 17, 2011 Where do you submit the form? Btw: your form method is, by default, GET. So your $_POST will not work. Quote Link to comment Share on other sites More sharing options...
Luke Warm Posted February 17, 2011 Author Share Posted February 17, 2011 The form gets submitted to itself " <?php echo $_SERVER['PHP_SELF']; ?> " and the method is POST. Quote Link to comment Share on other sites More sharing options...
ronverdonk Posted February 17, 2011 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Luke Warm Posted February 17, 2011 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Luke Warm Posted February 17, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.