How to Display & Structure MySQL With PHP

When you design and create a MySQL database, you should lay out the information in an easy-to-read format. The two main reasons are: you will want to test the database content for inconsistencies; the data must be displayed to the end user. So, for both reasons, it is vital to display data in a clear and concise format. PHP, the easy-to-use web programming language, is particularly well suited to these tasks.

Instructions

1

Open a text editor, such as Notepad, and write a segment of PHP code that connects to your MySQL server and selects the database you want to display. Do this using PHP's "mysql_connect" and "mysql_select_db" functions. Here is the final code (you must supply your own database details):

$dbInfo = array('host' => 'HOST', 'username' => 'USERNAME', 'password' => 'PASSWORD', 'dbName' => 'DB_NAME');

$link = mysql_connect($dbInfo['host'], $dbInfo['username'], $dbInfo['password']) or die('Unable to connect');

mysql_select_db($dbInfo['dbName']) or die('Unable to connect to DB ' . $dbInfo['dbName']);

2

Write a section of code that retrieves a list of tables in the current database. Loop through the result set and insert each table name into an array. Use PHP's native "mysql_query" and "mysql_fetch_array" functions. The final code should look like this:

$result = mysql_query("SHOW TABLES");

while($row = mysql_fetch_array($result))

$tableArr[] = $row[0];

3

Loop through the array of table names using a "foreach" loop. Each iteration must fetch all the rows in the table using a "SELECT * FROM table_name" MySQL query, retrieve the table's column names and display them as table headings, loop through each row in the select query's result set and display them as a row in the table. Below is the complete code:

foreach($tableArr as $table)

$result = mysql_query("SELECT * FROM " . $table);

$rowArr = array();

echo "

" . $table . "

";

if(mysql_num_rows($result) > 0)

while($row = mysql_fetch_array($result))

$rowArr[] = $row;

echo "

";

foreach(array_keys($rowArr[0]) as $key)

echo "

";

echo "

";

foreach($rowArr as $row)

echo "

";

foreach($row as $field)

echo "

";

echo "

";

echo "

" . $key . "
" . $field . "

";

else

echo "

Table is empty

";
4

Close the connection to the MySQL server with the "mysql_close" function:

mysql_close();