How to create HTML tables from MySQL?

I'm building a part of a system where the user can define "views" from a mysql database.

I want some way of generating simple HTML tables/reports from the data, but not just plain output of sql queries- simple joins probably isn't enough.

Any ideas?

Just to be clear: I know perfectly well how to create a HTML table with PHP/MySQL - that is the simple part. What I want to do is allow my USERS to create their own tables without being exposed to the process!

Right, i'll give some more details- thanks for all the responses:

Currently users can create tables using a wizard, then view/edit/delete records in these tables. This makes it difficult to give an example as ta开发者_Python百科bles could change. The data I expect the client to input is for renting houses- a table of houses, tenants, rooms, maintenance issues etc.

What I aim to enable the user to do is to create some basic formatted output of a particular view, for example "show rent turnover in the current month for all houses" or "show overdue tenants" or "show rooms with no tenants". Once set up, these will only be viewed, not edited, so the process just has to be bearable to set up.

Does that make sense? If it's not possible I'll just hard-code the views- just makes the system less flexible!

Is this what you're looking for? It will dynamically generate an html table from a mysql result no matter what 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'd print the table:

echo createTable($result);

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 do the work:

<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 edits, it may not be the best idea to make users "create their own tables", but instead to give them a number of options and sort orders to create a better user experience. Otherwise it's just a form with some input elements and some if statements.

You'll have to expose users to part of the process, since they have to describe the tables and table relationships somehow. You could take a page from the likes of Crystal Reports, Visual Studio and phpMyAdmin and create a visual table designer. A table is represented as a rectangle with a title bar showing the table's name and a list of fields. The 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 a query creator: the user specifies which tables to query by dragging them from a list of tables to the canvas, which fields they're interested in (perhaps include a checkbox next to each column) and which fields to join on by connecting fields with lines. To join a table to itself, you could allow a table to be added to a query more than once, or allow fields within a table to be connected to other fields within the same table.

If you don't have it, grab phpMyAdmin, install it, open a database and go to the "Designer" tab to see how it works. You can also take a look at some screenshots

You have to define the way users will create those HTML tables. Do they setup a number of rows and columns, then feed the table manually ?

Once we had to do a templating system where all images, css properties, positionning of various html blocks, etc. The whole system was 5 or 6 mysql tables just to store the templates properties, but if it's just a table you can ease the process.

Imagine one mysql table for table format and properties (row count, col count, col width, colors, etc.) and another mysql table for data to put inside. This second table should have columns for storing date, row and col positions.

The rest is all making a form accessible to non-tech users to configure and feed table, and render table with some PHP loops and queries.

Approximately 100% of websites does the same task: create HTML tables from SQL database.
So, you can get yourself some PHP/Mysql book to get an idea. Though all tables being hardcoded by a programmer.

As for the user-defined views it can be a bit more complicate. But possible too

<?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% Working just changed. $query variable according to your table