How do I create HTML tables from MySQL?
I am creating a part of a system where a user can define "views" from a mysql database.
I want some way of generating simple HTML tables / reports from data, but not just outputting sql queries - simple joins are probably not enough.
Any ideas?
Just to be clear : I know perfectly well how to create an HTML table with PHP / MySQL - it's the easy part. I want my USERS to be able to create their own tables without being involved in this process!
That's right, I'll give a few details - thanks for all the answers:
Currently, users can create tables using a wizard and then view / edit / delete records in those tables. This makes it difficult to present an example because the tables can change. The data that I believe the client should enter is home rentals - a table of houses, tenants, rooms, service issues, etc.
What I want to do is generate some basic formatted output of a certain kind, like "show the current month's rental profitability for all homes" or "show expired tenants" or "show rooms without tenants". Once configured, they will only be displayed, not edited, so the process must be tolerant for customization.
It makes sense? If that's not possible, I'll just hard-code the views - it makes the system less flexible!
a source to share
You must define a way to create these HTML tables. Do they set multiple rows and columns and then load the table manually?
Once we had to make a templating system where all the images, css properties, positioning of various html blocks, etc. The whole system had 5 or 6 mysql tables just to store template properties, but if it is just a table, you can make the process easier.
Imagine one mysql table for table format and properties (row count, column count, column width, colors, etc.) and another mysql table for data entry. This second table should have columns to store the date, row and column positions.
Everything else makes a form available to non-technical users to set up and submit the table and display the table with some PHP loops and queries.
a source to share
Is this what you are looking for? It will dynamically generate html table from mysql result no matter how big or how big the result.
//this function dynamically outputs a basic HTML table from any mysql result
function createTable($result) {
$table = '<table><tr>';
for ($x=0;$x<mysql_num_fields($result);$x++) $table .= '<th>'.mysql_field_name($result,$x).'</th>';
$table .= '</tr>';
while ($rows = mysql_fetch_assoc($result)) {
$table .= '<tr>';
foreach ($rows as $row) $table .= '<td>'.$row.'</td>';
$table .= '</tr>';
}
$table .= '<table>';
//mysql_data_seek($result,0); //if we need to reset the mysql result pointer to 0
return $table;
}
This is how you will print the table:
echo createTable($result);
a source to share
You can use phpMyAdmin to show MySQL in nice tables, or if you want to code it yourself, you can use PHP and HTML to get the job done:
<table>
<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Could not connect: " . mysql_error());
mysql_select_db("mydb");
$result = mysql_query("SELECT id, name FROM mytable");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<tr>
<td><?=$row['id']; ?></td>
<td><?=$row['name']; ?></td>
</tr>
<?
}
mysql_free_result($result);
?>
</table>
Edit: After reading your permissions, it might not be a good idea to force users to "create their own tables", but instead give them a few options and sort orders to create a better user experience. Otherwise, it's just a form with some input elements and some if statements.
a source to share
You will need to expose users for part of the process, as they have to describe the relationship between tables and tables in some way. You can take a page from the likes of Crystal Reports, Visual Studio and phpMyAdmin and create a visual table designer. The table is presented as a rectangle with a title that displays the table name and list of fields. Tables are on a canvas and can be placed anywhere on that canvas. Foreign keys are represented as lines connecting fields.
You can use the same interface as the creator of the query: the user specifies which tables to query by dragging and dropping them from the list of tables to the canvas, which fields they are interested in (perhaps a checkbox next to each column), and which fields to join by connecting the fields with lines. To join the table for yourself, you can allow the table to be added to the query more than once, or you can allow the fields in the table to be connected to other fields within the same table.
If you don't have it, grab phpMyAdmin , install it, open your database and go to the Designer tab to see how it works. You can also see some screenshots
a source to share
<?php
$query="SELECT * FROM table_name";
$result=mysql_query($query);
$numfields = mysql_num_fields($result);
echo "<table border=1><tr>";
for ($i=0; $i < $numfields; $i++) // Header
{ echo '<th>'.mysql_field_name($result, $i).'</th>'; }
echo "</tr>";
while
($res=mysql_fetch_row($result)) //you may use mysql_fatch_array($result) which can hendel both $res[name] and $res[1]
{
echo"
<tr>
<td> $res[0]</td>
<td> $res[1]</td>
<td> $res[2]</td>
.
.
.
</tr>";
}
echo"</table>";
?>
100% The job has just changed. $ query according to your table
a source to share