Creating Dynamic HTML Tables

dcparris

I am able to hard code an HTML table and populate it with data from MySQL.
What is the best strategy to create tables dynamically, regardless of the
number of fields the user needs for their result set?

It doesn't seem prudent to try and build static tables for end users when
their needs will be many and varied. If one considers that my DB has 117
fields spread (not very evenly) across 13 tables, I'll spend a fair bit of
time trying to hard code the tables for the possible result sets.

Should I define a table function or class? Is there already a code snippet
available that handles this task? I have been considering defining a function
that creates the table and then loops through the result set to populate it. I
would greatly appreciate any guidance I can get here.

sarahk

In a year's time you won't be able to believe you asked that question

In the meantime read:

http://www.databasejournal.com/features/php/article.php/1485971
http://www.phpbuilder.com/columns/jesus19990308.php3
http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html

good luck

dcparris

Thanks for the pointers.

Perhaps I should add that I'm new to programming in general, not just PHP. Some of this stuff just seems a little over my head. However, as a minister, I need a church administration database that runs on the Linux platform. Our new church plant has no money for Windows or Windows-based products.

I managed to get the MySQL backend going. I seem to get the PHP samples, but applying them to my own script doesn't come quite so easy. I hope you'll be patient if I don't seem to "get it".

sarahk

Aaah, we're endlessly patient here

As a minister I would have thought you'd have access to the young guns in the congegration and would be able to find a bright young thing who'd love to have some real world experience - either doing it or helping you to do it.

I'm not a church-goer but I know that if I was 16 and had to do some church - community service stuff and had to choose between:
a) help with Sunday School
b) help with the oldies
c) help the minister with his database programming

"C" would win hands down. But then maybe I'm beyond redemption 🙂

dcparris

Unfortunately, I AM the young gun - and most computer literate. I believe I can safely say that my small congregation has no collective knowledge of computers - believe it or not.

As for me, I've tried my hand at Quick Basic under MS DOS 6.22, C, and VBA for MS Acces. I had some progress with the VBA, but never got to really apply it.

I seem to half-way grasp PHP so far. Perhaps if I keep at it, I'll eventually grasp programming. BTW, I didn't much care for math, hence my understanding of logic is - well, it ain't there.

Everyone is redeemable, but only if you want to be redeemed. That's off topic, so I'll leave it there. I appreciate your patience. I just gotta ask questions.

Norman_Graham

But to cut to the chase, you wanted dynamic tables and as a committed aetheist I just cannot resist the opportunity to lend a helping hand to a minister:

<?php
//Get my results from the DB
include ("db_cnx.inc")
$result=mysql_query("SELECT name, topic FROM churchrecords WHERE topic='marriage'");
print "<table>"; //Put results into table
while ($row=mysql_fetch_array($result)) {
echo "<tr><td>" . $row["name"] . "</td></tr>";
}
print "</table>";
?>

Now, of course, your tables will probably be a lot more complex than that, but that is the absolute minimum approach and perhaps you get the idea. PHP loops through the results and just produces a new table row for each result. The question is, can you adapt this for the kind of table you actually want? Perhaps you'd like to post some details about the kind of display tables you'd like and we could think about how to adapt.

Best o luck

Norman

dcparris

Well,

It truly is a small world. I was born in Weisbaden, Germany.

At any rate, I would like to be able to produce a table automatically (magically) regardless of the number of columns.

I have a PERSON table and an ENTITY table, among others, with anywhere from 2 to 34 columns in a table. I (or any end-user) will likely want to ask the database for a variety of reports using any number of columns. It's easy enough to create predefined queries with the columns already arranged to be placed into HTML tables for display.

I don't mean to be lazy, but I really didn't much enjoy coding the HTML table for the PERSON table (the BIG 34) results. Besides, I may only want five or ten of those fields at any given time.

Here's a hard-coded result (HTML)table:

  mysql_select_db("chadb_test"); 
  $query = "select * from entity"; 
  $result = mysql_query($query); 

  $num_results = mysql_num_rows($result); 

  echo "<p align='center'>Number of entities found: ".$num_results."</p>"; 
?> 
<div> 
<table align='center' border='0' cellpadding='2'> 
       <tr> 
           <th>Entity ID</th> 
           <th>Entity Type</th> 
           <th>Entity Category</th> 
           <th>Entity Name</th> 
           <th>Address ID</th> 
       </tr>

<?php 
  while ($query_data = mysql_fetch_array($result)) 
  { 
     $ent_id = $query_data["ent_id"]; 
     $ent_type = $query_data["ent_type"]; 
     $ent_cat = $query_data["ent_cat"]; 
     $ent_name = $query_data["ent_name"]; 
     $adrs_id = $query_data["adrs_id"]; 
     echo "<tr>\n"; 
     echo "<td align='center'>$ent_id</td>\n"; 
     echo "<td align='center'>$ent_type</td>\n"; 
     echo "<td align='center'>$ent_cat</td>\n"; 
     echo "<td align='center'>$ent_name</td>\n"; 
     echo "<td align='center'>$adrs_id</td>\n"; 
  }

Suppose I want - one day down the road - to pull data from both the ENTITY and PERSON tables, using only certain columns? Entities could be family names or business names, other churches, etc.

Even if I have done my homework and pre-determined the kinds of reports I'll need, it seems I should NOT have to hard-code the columns for every situation.

Should I use a function, or a class with one function for determining how many columns have been requested by the end-user, and another for building the HTML table based on the output of the first function?

Thanks. I hope this makes clearer what I'm trying to do.

dcparris

I'm thinking what I need is something to the effect of:

Weedpacket

Here's something that I've ported over from something else I'm working on right now in another language.

<?php
/*
First off, here's an array of column names. It's a little bit more
complicated than just a list of column names, though; what might make
sense for the name of a column in the database isn't necessarily a nice-
looking name on the page when it renders. ("Entity Category" looks
much nicer than "ent_cat", for example.)

Furthermore, some columns come from different tables. In this case, they
all come from the 'entity' table, but we want some sort of way to
specify other tables should the need arise.

Columns will appear in the HTML table in the order they appear here.
*/
$display_columns = array(
 array('ent_id',   'Entity ID',       'entity'),
 array('ent_type', 'Entity Type',     'entity'),
 array('ent_cat',  'Entity Category', 'entity'),
 array('ent_name', 'Entity Name',     'entity'),
 array('adrs_id',  'Address ID',      'entity')
);

/*
Let's assume we've got our database connection, and we've already
selected our database. So what comes next I guess is that we build and
run our query.

First of all, we need to get the names of all the columns we want to
extract, and the tables we want to extract them from.
*/
$wanted_columns = array();
$wanted_tables = array();
foreach($display_columns as $display_column)
{ // Make sure we uniquely identify each column
  // (as e.g., "entity.ent_id")
  $wanted_columns[] = $display_column[2].'.'.$display_column[0];
  $wanted_tables[] = $display_column[2];
}
// We don't need to mention a table more than once:
$wanted_tables = array_unique($wanted_tables);

// Let's turn those into bits of a SQL query.
$wanted_columns = join(',', $wanted_columns);
$wanted_tables = join(',', $wanted_tables);

// Build and run the query:
$query = "select $wanted_columns from $wanted_tables";
$results = mysql_query($query);
$num_results=mysql_num_results($results);

echo "<p align='center'>Number of entities found: ".$num_results."</p>"; 

// Check we've got something to display
if($num_results>0)
{ // Put together the table, column headings first. (I stick an extra
  // column header cell at the beginning. You'll see what goes in that
  // column in a moment.
?> 
<div> 
<table align='center' border='0' cellpadding='2'><tr><th></th>
<?php
  foreach($display_columns as $display_column)
  { // Use the for-human-consumption name
    echo '<th>'.$display_column[1].'</th>';
  }
?>       </tr>
<?php
  /* And now to do the columns. It's not accidental that the order in
  which columns are listed in the table match the ordering in the result
  set.*/
  for($i=0; $i<$num_rows; $i++)
  { $row = mysql_fetch_row($results))
    echo '<tr><td>'.$i.'</td>'; // That's what the extra column's for.
    for($j=0; $j<count($row); $j++)
    { echo '<td>'.$row[$j].'</td>';
    }
    echo '</tr>';
  }
  echo '</table></div>';
} // if($num_results>0)
?>

Now, I'll be the first to admit that this isn't the best solution (that's one of the reasons why I'm currently working on it myself). The whole thing probably should be wrapped in query generator and HTML-table classes, there's plenty of scope for expanding the functionality (WHERE clauses especially need implementing), and the $display_columns could be organised a bit more intelligently, oh, and I'm not bothering to properly escape my output; but it's a start.

mcgruff

You have a hotline to The Master Programmer and you're asking us?!

If you'll forgive me for that I'd just like to say in general that php should be a good choice for you. It might not always seem so at first but it's an easy language: stick at it and you'll get there. There are lots of resources just a google away, and helpful forums like this one.

I'm not at all religious but I guess the open source community is kind of inspiring as an example of people helping each other out.

dcparris

To weedpacket:

You understand me well. I have been told by a VB Script/ASP type that I am not asking for the beginner's bag of tricks. Even so, given that one can pull the number of columns from a MySQL table via a PHP function, I figured that it would be a matter of throwing together a function or two (or 3 or 4), possibly into a class, to bring it all together.

I haven't reall had a chance to digest your response, but I might even grasp it.

To McGruff:

The Master Programmer was - and is - pretty good at making us think for ourselves. `The open source community, which I am certain is not exactly a hotbed of Christianity, is very inspiring, and could teach us Christians a thing or two about sharing.

There is also a close similarity between the kind of sharing that happens in these forums and the (seemingly lost) art of Christian Discipleship. It's very practical. Not only that, but I sense there is a sense of relationship among the many programmers and would-be/wannabe's.

Norman_Graham

Looks like you're doing well at sorting this one out, and you have Weedpacket behind you (he's your only man, as we used to say where I come from). I will quickly bow out in recognition of Weedpacket's vastly superior skill and experience and wish you the best of luck.

Norman

dcparris

Well Mr. Graham,

Don't rush off, it could get interesting. But if you must, I appreciate you jumping in to help out (or try).

dcparris

Weed Packet,

This section:

// Let's turn those into bits of a SQL query.

$wanted_columns = join(',', $wanted_columns);

$wanted_tables = join(',', $wanted_tables);

raises a question. What in the world is the comma (',' for? I know the second comma separates the two elements. I'm also curious about the join keyword here. Are you saying "join the requested tables/columns"?

Weedpacket

[man]join[/man] takes an array (as its second argument) and returns a string consisting of the arrays elements joined together; the first argument (in this case, a comma) is a string that is used as a kind of "glue".

$array = array("This", "is", "an", "array", "of", "strings.");
$string = join(' ', $array);
echo $string;

outputs "This is an array of strings.". The array elements were joined together into a single string with spaces between them.

So what those statments were doing was taking an array of the columns and tables we wanted to query, and turning them into comma-delimited strings suitable for inserting into the SQL query.

Some people would argue that using the same variable to store different types of data ($wanted_columns, for example, starts out as an array, but after that join statement it contains a string) is a bad idea; but I really don't see much wrong with it in this situation, since the array had only this one single use and was not going to be needed again.

mcgruff

You said you were new to php so I hope you don't mind me mentioning the manuals available at php.net and mysql.com. You can get downloadable versions of both - including a php manual with user comments (recommended).

I don't mean to suggest you should go away and RTFM but, if you don't have them already, they'll help a lot. Indispensable really.

Sorry I'm not giving you any more specific help - weedpacket is on the job 🙂

dcparris

I appreciate your input, and have been peeking at PHP's function defs. I have two books already that cover PHP and MySQL in depth. I pretty much get the MySQL part - and enjoyed defining and building my tables, as well as writing a few initial queries.

The challenge for me lies more in understanding how to implement the code for my application. I have never felt comfortable with mathematics and logic, which I believe is a must in programming.

I read the examples and understand. Then I try to figure out how to apply the examples to my own application and come up blank. I have managed to modify some of the sample code from my books to help me in designing the user interface (what I've done so far), which is a major step forward for me. But I raised my initial question here because I didn't see any information on dynamically creating HTML tables to fit any potential query - something I believe should have been raised in books for beginners, or articles, etc.

It appears to me that I wasn't asking for mere child's play with this question, either. Some experienced programmers (locally, and apparently on this forum) have expressed that this is a challenge. Weedpacket has offered more help than I anticipated, but I still have yet to apply his sample to my application. I'll hopefully have time to figure that part out this week. We'll see. Our newly planted church celebrates our first full year of ministry.

Weedpacket

That sounds like how I learned (I'm a mathematician, not an engineer; even though programming is both mathematics and engineering) - I, too, was once a wannabe 🙂

A good source of understanding is reading what others have written and pondering how it does what it does and why it does it that way - and tinkering with it. Over time you start to recognise common themes and methods, places where the approach used just seems wrong to you based on what you know (and sometimes on closer investigation you find that it could indeed be done better). Even though I prefer homebrewed solutions, I frequently peruse repositories like PEAR, or PHPClasses.

And I noticed that you have some interest in the sociology of Open Source. I've often found Eric Raymond to be especially astute on this and the wider hacker community: many's the time I've read his observations with a sense of recognition (not always flattering).

dcparris

I should add that MySQL's manual is more helpful the the book published by Sam's that was recommended on their web site in some respects.

Learning MySQL has actualy enhanced my understanding of programming - it certainly makes it easier to apply. It also boosted my understanding of arrays. If someone had shown me a spreadsheet years ago and said, "this is basically an array", I would never have thought programming to be beyond my grasp. Now, let me see if I can start working on this table issue.