MySQL query result split

Question

This is somewhat of a multipart question, but..

I am looking to query a MySQL table to get fields from a event category table.

Each category has a specific calendar assigned to it, in the "calendar" field in the category table.

I am planning to have a HTML list box for each of the different types of calendars (only 4, and they wont change).

Is there a way to query the category table once, and split the results into different arrays?

Ex.

---

Sports (only categories assigned to the sports calendar appear here):

(in list box):

Basketball

Baseball

Golf

etc.

then,

---

General:

(only categories assigned to the general calendar appear here)

etc.

etc.

etc.

---

I thought to do this in one query, instead of querying the whole table for each calendar type, but will there be that much difference in speed?

I am using PHP, by the way.

Thanks for the help.

Answer 1

You can query the table once and use mysql_data_seek to reset the rowset pointer back to the beginning after having read through it - i.e. iterate over the rowset for category 1, reset the pointer, iterate over for category 2, etc. You need only query once, and iterating over the results is very fast vs. querying.

Alternatively, have four strings each containing the HTML for the content of one of the listboxes, and iterate over the rowset once, appending to the relevant string based on the category of the current record.