[Resolved] Simple, generic table content viewer.

Joseph_Sliker

Hi;

My continuing quest to keep my projects/life as simple as possible....

I'm trying to write a simple page that will allow my users to view the complete contents of MySQL database tables...perhaps with some filtering and/or sorting of records, but first things first.

I understand how to write a php page to show the contents of specific tables, but what I have in mind is something that will allow them to select from among a certain set of tables...which will have varying numbers of columns... and have the result page created on the fly (rather than me having to create five different pages for the five different tables).

I thought about using a two-part query system that would start with something like

$query="show columns from $TABLENAME";

...in order to get the column count and names...and then create some kind of loop using the results from that query to create the table structure and contents needed...I'm sure I could build it that way, but I'll bet there is a really, really simple way that I don't know about.

Putting data into and then getting it back out of Arrays confuses the heck out of me, but I suspect using these is the easiest thing to do.

Is there a simple, simple set of commands that will just dump the contents of a requested table into an array (including the field names) and a corresponding/simple command or set of commands that will display the array contents as a table on an .html or .php page?

And no, I don't want to give them access to phpMyAdmin...most of my users wouldn't have a clue as to where to start and the others could do too much damage.

Thanks

Joseph_Sliker

I posted this thread in the wrong place and can't seem to delete it. I restarted it in the DATABASE section. Replies there would probably be better.

eoghain

Well this is a fairly simple thing to do:

<?php
$tablename = $_POST['tablename'];
$sql = "SELECT * FROM $tablename";

/* Connecting, selecting database */
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
   or die("Could not connect : " . mysql_error());
echo "Connected successfully";
mysql_select_db("my_database") or die("Could not select database");

/* Performing SQL query */
$result = mysql_query($sql) or die("Query failed : " . mysql_error());

/* Printing results in HTML */
$row = 1;
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   if ($row == 1) {
      echo "\t<tr>\n";
      foreach ($line as $col_key => $col_value) {
          echo "\t\t<th>$col_key</th>\n";
      }
      echo "\t</tr>\n";
   }
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
   $row++;
}
echo "</table>\n";

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);
?>

You can make it more complex as you define your needs but this simple little code will take in a table name and display the contents into an HTML table, you'll probably want to add multipage support as you start using this on larger datasets.

This is basicly a copy of the sample code they give at php.net for [man]mysql[/man]. All that I added was the code to use the posted tablename and the code to display the column names in a header row.

Joseph_Sliker

I knew there had to be an easier way! (This is what I get for trying to learn this stuff from somewhere in the middle...). It works just great. Thank you so much!

I sandwiched your code between some HTML/CSS formatting stuff to change the "raw" appearance a little bit. i.e. ...

<html>

<head>
<title>Data Management: Table Contents</title>
<style type="text/css">
TH {color: silver; background-color: navy; padding: 1%}
BODY {font-family: "ARIAL", serif }
</style>

</head>
<body>

[your code went right in here, with the database/connection stuff filled in]

</body>
<html>

Now all I've gotta do is write my WHERE/ORDER BY clause and selection form page.
😃