Formating HTML TABLE from mysqlresults

[billix1]

I have two tables in mysql called version and event. I would like to have the output of the query creating a table for each version _Id with the events in the version sorted and listed by event_type. So for instance, the below would be the ideal scenario, where a table is created for each version, and the events for that version are sorted by the event type. with event_type as the header. ie application, or configuration.

                         VERSION 2 (v_name)

                          Application (e_type= application)

Title(e_name), Submit Date(e_date), Sequence(E_sequence)

                       Configuration (e_type= configuration)

Title(e_name), Submit Date(e_date), Sequence(E_sequence)

                        VERSION 1.5 (v_name)

                          Application (e_type= application)

Title(e_name), Submit Date(e_date), Sequence(E_sequence)

                       Configuration (e_type= configuration)

Title(e_name), Submit Date(e_date), Sequence(E_sequence)

My questions are..Would i need to use Select Distinct on the event table for event_type, Do i need to use two mysql queries to achieve the above? Finally would the above require use of the foreach loop, and how could i implement that? would be greatly appreciated if anyone could provide me with some insight. Thanks!

)

[Roger_Ramjet]

No to the Select Distinct, because:

SELECT version.v_id, version.v_name, event.e_type, event.e_name, event.e_date, event.e_sequence
FROM version INNER JOIN event ON version.v_id = event.v_id
ORDER BY version.v_id, event.e_type

will return the required data. Only if you have duplicate records will you need to use distinct; and you should have multi-field indexes that prevent this.

To process the results you will need to store the current version id, ie the version id of the table you are outputting. The first row triggers the table. Each subsequent row fills a table entry until the version id changes, at which time you close up the table and then start a new one.

... test for no results ....

while($row = mysql_fetch_array($result)) {
if ( $row['v_id'] != $current_id) {

      if ($current_id >0) {
           .... generate html to close last table .....
           }

       ... generate html to create table for new version  using ...
      $row['v_name']

      $current_id = $row['v_id']

  } 

   .... generate html to list version table contents using .....
   $row['e_type'] etc

} // end of while rows to process

.... generate html to close last table and end page .....

Hope you understand what I'm getting at. The only part to worry about is starting the first table and ending the last one. All the rest follows naturally as you iterate through the results. Other loop operators such as FOR EACH or DO ..... UNTIL may be easier to follow, but the result should be the same.