Best way to create a "spreadsheet" using two tables.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
a94060
Forum Regular
Posts: 543
Joined: Fri Feb 10, 2006 4:53 pm

Best way to create a "spreadsheet" using two tables.

Post by a94060 »

Hello everyone,

I have one sql table called users. This stores the username, and a user id(random 6 digit number). . One more table is called events storing the event name and an event id. I have another table called attendence which stores the attendance of members. It stores the event id, and user id.

Now, my goal is to create a table. I want to put the users listed vertically, the events listed horizontally, and a check mark if the user as attended an event, and no check mark if the user has not attended the event.
I was thinking the best way to do this would be to "Generate" the table line by line. First i would generate the event headings, then start generating the members. For each member,id start a loop which would walk the whole events table, and make a query where the user id and event id match. If such an entry is found,that means the user has attended an event, and i should put a check mark. If i do not find an entry, then no check mark. This would however require me to put a event id which is not random.

Any other suggestions on how to go about? Recap:Make a table showing the attendence of members with members going vertically,and events going horizontally.
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Re: Best way to create a "spreadsheet" using two tables.

Post by Skara »

Ok, let me see if I understand you right...
This can probably be coded more concisely, but I think this is what you're going for..

Code: Select all

//get data:
SELECT * FROM `events`;
SELECT * FROM `users`;
foreach ($user) {
    $user['attendance'] = array(
        SELECT * FROM `attendance` WHERE `user_id`='$user['id']';
    );
}
 
//output data:
foreach ($event) {
    echo <td>event name</td>;
}
foreach ($user) {
    echo $user['name'];
    foreach ($event) {
        if (in_array($user['attendance'], $event_id)) {
            echo <td>checkbox</td>;
        }
    }
}
Something like that... Like I said, it can probably be coded better, but I think that's a pretty clear way of explaining what I think you want.
User avatar
a94060
Forum Regular
Posts: 543
Joined: Fri Feb 10, 2006 4:53 pm

Re: Best way to create a "spreadsheet" using two tables.

Post by a94060 »

yes,that sounds appropriate. Thanks a lot for the help. I understand what i was pretty much looking for =]
Post Reply