how do I match records against an array?

gsmackay

I have an array containing 10 elements which are categories. I have a query which returns records for all the members and which catagory(s) they belong to. For example one member might have four records (one for each catagory he belongs to) and another member might have all 10 records. For speed, I only want to traverse the query one time from beginning to end. It is sorted by member then catagory. So, I am thinking I need to loop through the array once for each member and if there is not a record that matches that catagory print a blank space and if there is a record that matches print an X. I am just not sure how to code it to loop through the array and stay in sync with the query.

Report layout

                                  Catagories
member       A  B   C   D   E   F   G   H    I     J    

-------------------------------------------------------
joe               x        x                  x         x
mary            x   x   x        x                    x    x

-------------------------------------------------------

tomhath

Your explanation isn't very clear. What does the row coming from the query look like? I don't see a need for any array in php, just some way to tell what position the row's category should print (are they numbered?). Probably something like this:

fetch first record
while not at end of records
  start printing a new member line on the output page
  for category from 1 to 10
     if not past last record and 
            this record is for the member line being printed
        if this record is for the category we need to print
          print the X
          fetch the next record
        else print a blank
    else print a blank
end while

gsmackay

Sorry I was not very clear. I could not figure out how to get the report layout to display right in my post. I could not get the columns to line up vertically but your answer seems like you figured it out.

My example listed 10 categories but depending on the options the user selects there could be any number of categories. So the number of columns in my html table is built dynamically. I issue a query to the category table and stuff the results in an array. Then a query is issued that pulls out all the members that belong to any of the selected categories. I figured, repeatedly looping through the array and stepping through the main query once, would be faster than a bunch of selects or locates in the main query.

For further explanation: There are three tables. The table of categories, a table of members and a table containing the id field from the category and members tables.

members table:
id
name
addr

categories table:
id
name

catmembers table:
member_id
category_id

I will try to work out something from your example. Thanks again!