More on displaying table data

November 30, 2006 / Filed under: Web Development, MySQL, HTML, Excel, Data

I've discussed before the crux of quickly displaying database data in a format that users can understand and absorb.

Ideally, we're talking about a front end for a database table.

After all, raw database data is nothing to look at.

That's why there are tools that help display this data, in a format that people can understand, such as an Excel spreadsheet. An Excel spreadsheet (with rows and columns) is a perfect "visual representation" of what a database would look like. If you give someone an Excel spreadsheet with column headers, and rows of data, they're very likely going to understand the representation of the data.

Excel is just one option for viewing database data. Other tools range from web-based configuration tools, such as phpMyAdmin, and other desktop applications such as Crystal Reports. Crystal, specifically, allows you to perform "grouping" functions, and create reports that "shows" your data in any number of customized templates. However, I'm not looking for something that extensive.

What I'd like is a quick way to display the data as an HTML table, without having to do all of the coding involved.

Here's what I picture:

<?php

    $viewTableData = new HtmlTableViewer;

    $viewTableData -> Query( "SELECT * FROM myTable" );

?>

We could pass the SQL query to the fictional class (HtmlTableViewer), which then queries the database, and outputs all of the data in HTML, with options for sorting, filtering, and searching.

Without the fictional class taking care of the details - the coding manpower taken to re-create such an HTML page from scratch is far beyond my means right now.

In this highly-automated world, I am used to just providing the most scant of details, and in turn, receiving a lot back. This is a prime example of where I need this type of automation.

Comments/Mentions

# Tim at 11/30/2006 3:29 pm cst

How about something like this. I thought about making a class to do it but I can't give you everything.

<?php

   $query = mysql_query("SELECT * FROM table");
   
    if ( mysql_num_rows($run_actual_search) > 0 )
    {
        $num_fields = mysql_num_fields($run_actual_search);
        
        // give field names
        echo "<table>r<tr>r";
       
        for ($i=0; $i < $num_fields; $i  )
        {
            print "t<th scope="col">" .
            mysql_field_name($run_actual_search, $i). "</th>r";
        }
    
        echo "</tr>r";
        
        while ( $r = mysql_fetch_row($query) )
        {
            echo "<tr>r";
            
            for ($f = 0; $f < $num_fields; $f  )
            {
                print "t<td>".$r[$f]."</td>r";
            }
            
            echo "</tr>r";
        }
        
        echo "</table>r";
   }

?>