TOOLS : Run Query Against DB, Display Results in HTML

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
appeet
Forum Newbie
Posts: 3
Joined: Wed Jul 27, 2005 3:11 pm

TOOLS : Run Query Against DB, Display Results in HTML

Post 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,
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

phpMyAdmin does a pretty good job. Sounds like a pretty neat project to be honest...
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Moved to Databases.. *grumble*
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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,
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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

?>
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply