Page 1 of 1

TOOLS : Run Query Against DB, Display Results in HTML

Posted: Wed Aug 03, 2005 3:45 pm
by appeet
Hello .,

Any one know of good tools / prewritten tools that query a table in the database (basically run my query) and output the resultset in a clean, presentable tabular display (inside HTML).

Thanks in advance.

Regards,

Posted: Wed Aug 03, 2005 3:57 pm
by pickle
phpMyAdmin does a pretty good job. Sounds like a pretty neat project to be honest...

Posted: Wed Aug 03, 2005 4:46 pm
by feyd
Moved to Databases.. *grumble*

Posted: Wed Aug 03, 2005 4:48 pm
by feyd
misposted by appeet:
appeet wrote:Thanks for the response.

Agreed! However I was looking for some tool that I can use which will create the page (for the non admin users, site visitors)

Regards,

Posted: Wed Aug 03, 2005 4:56 pm
by pickle
The project wouldn't be that difficult to do. All you'd need to do is loop through each field returned, while looping through each row, and output that.

Below is some code I wrote to just dump the table structure of each table in a database. It was written for my personal use so I make no promises of the code cleanliness, but it could still prove useful.


(Spacing's off from the original code)

Code: Select all

<?php
//*****************************************
// Dump the page head
//*****************************************
echo <<<HEAD
<html>
<head>
<title>
DB Schema dump
</title>
<style>
body,td,th{
  font-size:10px;
  font-family:Verdana,sans-serif;
}
.table_head{
  background-color:#BC1616;
}
caption{
  text-align:left;
}
.table{
  background-color:#ADB46D;
  margin:15px;
  border:1px solid #333333;
}
.table_cell,.table_head{
 padding:0px 5px 0px 5px;
}
.caption{
 padding:3px;
 font-size:12px;
 font-variant:small-caps;
  letter-spacing:2px;
  font-weight:bold;
}
</style>
</head>
<body>
HEAD;


// if POST vars haven't been generated, display the form
if(!$_POST)
{
  //*****************************************
  // Output the form
  //*****************************************  

  echo <<<FORM
<form name = "form" method = "POST" action = "$PHP_SELF">
<table>
<tr>
<td>
    Username:
</td>
<td>
    <input type = "text" name = "username">
</td>
</tr>
<tr>
<td>
    Password:
</td>
<td>
    <input type = "password" name = "password">
</td>
</tr>
<tr>
<td>
    Database
</td>
<td>
    <input type = "text" name = "database">
</td>
</tr>
<tr>
<td colspan = "2" align = "center">
    <input type = "submit" value = "Generate" name = "generate_submit">
</td>
</tr>
</table>
</form>
FORM;
}//if !$_POST
else
{
  //*****************************************
  // Display the tables
  //*****************************************

  $conn = mysql_connect('localhost',$_POST[username],$_POST[password]);
  $db = mysql_select_db($_POST[database],$conn);

  //get a list of all tables
  $query = <<<SQL
SHOW TABLES
SQL;
  $list_result = mysql_query($query,$conn);

  if(mysql_errno() == 1046)
  {
    echo "No such database.";
    exit();
  }
  else if(mysql_errno() != 0)
  {
    echo "DB error: ";
    echo mysql_errno();
    echo "<br />";
    echo "DB Error message: ";
    echo mysql_error();
    exit();
  }

  //display each one
  while($list_row = mysql_fetch_row($list_result))
  {
    echo <<<TABLE
<table class = "table" cellspacing = "1">
<tr>
<td colspan = "99" class = "caption">
      $list_row[0]
</td>
</tr>
<tr>
      <th class = "table_head">
      Field
      </th>
      <th class = "table_head">
      Type
      </th>
      <th class = "table_head">
      Null
      </th>
      <th class = "table_head">
      Key
      </th>
      <th class = "table_head">
      Default
      </th>
      <th class = "table_head">
      Extra
      </th>
</tr>
TABLE;

    $query = <<<SQL
DESC
      $list_row[0]
SQL;
    $desc_result = mysql_query($query,$conn);

    while($desc_row = mysql_fetch_assoc($desc_result))
    {
      echo <<<ROW
<tr>
	<td class = "table_cell">
	$desc_row[Field]
        </td>
	<td class = "table_cell">
	$desc_row[Type]
        </td>
	<td class = "table_cell">
	$desc_row[Null]
        </td>
	<td class = "table_cell">
	$desc_row[Key]
        </td>
	<td class = "table_cell">
	$desc_row[Default]
        </td>
	<td class = "table_cell">
	$desc_row[Extra]
        </td>
</tr>
ROW;
    }//loop on each row in a table
  }//loop on each table in the list
}//if $_POST has been submitted

?>