Building dynamic table with php, html, mysql

[mudogg80]

Hello,

I've been driving myself nuts trying to figure this problem out so I'm going to ask for some help.

I have a cms with a php & mysql backend. I am trying to create a schedule of activities for a group of users, so they can see the times that they are supposed to be at a certain room/station. This will be in an html table with the data being retrieved from a mysql db.

This is the format of the table:

   Room1 Room2 Room3 Room4 Room5 
1:00 user1 user2 user3 user4 user5 
1:25 user5 user1 user2 user3 user4 
1:50 user4 user5 user1 user2 user3 
2:15 user3 user4 user5 user1 user2 

What is the best way to achieve results similar to the above table?
Is this enough information?

Thanks in advance

[eelixduppy]

>> Is this enough information?

Not quite. What's the structure of your database table you are going to be extracting this data from? What you want seems possible but I wouldn't know how to advise you without some additional information.

[mudogg80]

Sorry about that...

The data is being extracted from multiple mysql tables in a RDBMS format, so I am grabbing pieces of the data rather than 1 big SQL statement. The data I will have will before the table gets generated will be

(I've stored each section of data in an array.)

1. The number of rooms and their respective names (the table headers, maximum 8 or 9).
2. The row start times, ie 1:00, 1:25, 1:50...
3. and the users for this entire time block (1:00 - 4:00).

Each user is to be in each room only once.

Should I make 1 big SQL statement and store data into a multidimensional array?

Is this any better? If not, I apologize.

[darthmalis]

The exact structure of your data is very important. For example if you have columns in your users table table like Room1_Time and Room2_Time, the solution would be very different than if you have columns in you rooms table like Users_100 and Users_125. Based on what I am looking at; the easiest (not necessarily the best in every situation) way to do it would be a db table with columns like this Time, Room1, Room2 etc. Then you could store a users name in each room in each time. Then when you get your array you would simply loop through it like this:

foreach ($resultArray as $row) { 
 echo '<tr>'; 
 foreach ($row as $col) { 
 echo '<td>'; 
 echo $col; 
 echo '</td>'; 
 } 
 echo '</tr>'; 
} 

Most likely you database is not set up to work this way. If you could be a little more clear with the data structure, I am sure you would get better advice. I hope that this helps you move forward at least a little bit.

[mudogg80]

It does and your help is very appreciated!
I have been fighting tooth and nail with this system I am working on (previous new hire at company) and I am trying to resume a project that was already started with an already existing db schema. I am finding it very difficult to change db schema's because of the possibilities of errors in other areas of the project.

I pretty much did the same thing you instructed me to do. Got all the necessary data and stored into multidimensional array and then output to html table format using the foreach method.

If you happen to come back to this post, do you know the best way to export data to an excel file (.xls) rather than a csv file (.csv)?

Thanks again!

[darthmalis]

I have only used csv in the past. I have also converted .xls into csv for exporting it to a database. It seems like too much of a hassle to deal with all of the extra junk in a fully formatted spreadsheet and everything that does not get exported into the csv is probably not useful in my database anyway.