Determining most common entries in a DB table

[sc00tz]

I have a form that submits information to a database.  The form has a pulldown menu from which the user selects 1 of 10 "Houses."  After the House is selected, the user is asked to check any number of 20 checkboxes.  That info all goes into a single MySQL table.

 

I would like to have a page for each House, e.g. House1.php, House2.php, etc., that lists the 5 most frequently selected checkboxes for that particular house.  I honestly have no idea how to do this and my experience in MySQL thus far has been strictly entering specific data into tables and pulling specific cells of that table out.

 

Ideally, if 20 people filled out the form for House 1, and checkboxes 4, 5, 6, 7, and 8 were checked more than any others, I'd like House1.php to simply list "4, 5, 6, 7, 8."

 

Can anyone help me out?  I'm totally clueless.  Thanks!

[sc00tz]

anything?  or is there an easier way to do something more simple but similar?

[roopurt18]

You only need one house.php file that has a URL parameter: /house.php?house=1 or /house.php?house=2.

Read up on GET (or $_GET) to see more examples.

 

As for your SQL statement, what does your table structure look like?

[sc00tz]

OK thanks, I will read up on URL parameters.

 

As for the table structure, it has a 'form_id' field (int(11)), a 'user_id' field (varchar), a 'timestamp' field (varchar), a 'house' field (varchar, this is from the pulldown menu), and a 'boxes' field (varchar, this is an exploded array from the checkbox form submission page).

 

I think that's it...

[roopurt18]

and a 'boxes' field (varchar, this is an exploded array from the checkbox form submission page).

 

If you want to handle this nicely and neatly in your database query, then I suggest you break this data out into another, normalized table.

 

Otherwise you will have to select from the database, loop over the records at least once to calculate totals, and then loop over them again to do whatever it is you want to do with them.  In other words, this second approach will require you to write and maintain more code.

[sc00tz]

I originally had it in a different format, but given a search function I implemented, it actually made it more effective to have it as an exploded array.  This was mostly because each of the checkboxes is actually a string, not just a 1-20 number, but I just don't feel like writing out every string here.  Anyway, that's not important...

 

How exactly do I calculate totals, as you mentioned?  This is where I'm stumped I guess.  Like I said, I don't have much experience with MySQL or PHP.