Page 1 of 1

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

Posted: Sun Jul 05, 2009 10:24 am
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.

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

Posted: Sun Jul 05, 2009 2:23 pm
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.

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

Posted: Sun Jul 05, 2009 3:11 pm
by a94060
yes,that sounds appropriate. Thanks a lot for the help. I understand what i was pretty much looking for =]