gentlebreeze gentlebreeze - 1 year ago 63
SQL Question

Dropdown of tables to show content

This script allows the user to choose a table from the dropdown and then displays the contents of the chosen table.

  1. At the moment the DB connection is working.

  2. A list of tables is shown in the dropdown.

Problem: No content from the database table is shown.
I have checked through the code and everything looks OK, but it still only seems to partially work.

//update this to your DB connection details.
$dbh = "localhost";
$dbn = "dbname";
$dbu = "dbuser";
$dbp = "dbpass";

$conn = mysql_connect($dbh,$dbu,$dbp) or die("Unable to connect do database.");
mysql_select_db($dbn, $conn) or die("Unable to select database.");

//Some vars for Order by and Limit.
if (!isset($ordBy)){
$ordBy = 1;
if (!isset($ps)){
$ps = 0;
if (!isset($ord)){
$ord = 1;
if ($ord == 1){
$tOrder = "ASC";
} else {
$tOrder = "DESC";

//Tables drop-down

$result = mysql_query("SHOW TABLES FROM $dbn") or die("Cannot list table names.");
echo "
<form name=\"table_browser\" action=\"".$PHP_SELF."\" method=\"GET\" >
<select name=\"t\" onChange=\"javascript:submit();\">
<option>Select a table</option>
while ($row = mysql_fetch_row($result)){
echo " <option value=".$row[0].">".$row[0]."</option>\n";
echo " </select>

if (!isset($t)){
die("Please select a table");

//Get number of rows in $t and then select
$result = mysql_query("SELECT COUNT(*) FROM $t") or die("The requested table doesn't exist.");
$total = mysql_result($result,0);
$qry = "SELECT * FROM $t ORDER BY ".$ordBy." ".$tOrder." LIMIT ".($ps*20).",20 ";

if (isset($qry)) {
$result = mysql_query($qry) or die("The requested table doesn't exist.");
$i = 0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result);
if (!$meta) {
echo "No information available on the table<br />\n";
$name[$i] = $meta->name;
//Display table details
echo "Rows ".($ps*20+1)." to ".((($ps+1)*20 < $total) ? (($ps+1)*20) : ($total))." of $total from table:

<b>$meta->table</b>\n<br /><br />\n";

//Count results
if ($ps > 0) {
echo "<a href=\"browse.php?t=$t&ps=".($ps-1)."&ordBy=$ordBy&ord=$ord\">20 Previous</a> - ";
} else {
echo "20 Previous - ";
if ((($ps+1)*20) < $total ){
echo "<a href=\"browse.php?t=$t&ps=".($ps+1)."&ordBy=$ordBy&ord=$ord\">Next 20</a>\n";
} else {
echo "Next 20\n";

//Column names
echo "<br /><br />\n<table>\n <tr>\n";
for ($j = 0; $j < $i; $j++){
echo " <td><b><a href=\"browse.php?t=$t&ps=$ps&ordBy=$name[$j]&ord=".(-$ord)."\">$name[$j]</a></b>";
if ($ordBy == $name[$j]) {

echo "<img src=\"images/arrow$ord.gif\">";
echo "</td>\n";
echo " </tr>\n";

while ($row = mysql_fetch_array($result)){
echo " <tr onmouseover=\"'#DDDDDD'\" onmouseout=\"''\">";
for ($j = 0; $j < $i; $j++){
echo "<td>".$row[$name[$j]]."</td>";
echo "</tr>\n";
echo "</table>\n";

Answer Source

@gentlebreeze - my eyes hurt from trying to read that - but I can't see where you are actually setting the variable called $t - which is used to determine the table. You should have

$t = $_GET['t'];

somewhere before the line:

....if (!isset($t)){....

because you need to use it in the line:

....$result = mysql_query("SELECT COUNT(*) FROM $t"....

and you should not be using mysql_query either - old and now deprecated. You should switch to PDO and bound variables.