Parsing data

[donal23]

Hi. I have a table of documents in MySQL and related table doc_categories. I have a getDocuments function that basically says:

function getDocument($pdo) {
  (select fields from documents)
  foreach fields as field {
    returnarray[field] = field
    returnarray[anotherfield] = anotherfield
    returnarray[cats] = getCategories($pdo, $id);
  }
  return returnarray
}

function getCategories($pdo, $id) {
  (select fields from doc_categories where id = $id)
  foreach fields as field {
    returnarray[field] = field
  }
  return returnarray
}

So now when I get docs, I have all the doc root info plus an array for each doc with categories. Problem is, I want to break down a doc list like:

Category A
(docs in A)

Category B
(docs in B)

Now, I could loop the documents array I have and in each loop check if in_array(currentcat, currentdoc[cats]) but that seems pretty inefficient. Is there a way to do this that would be better? I suppose instead of having cats in the docs array I could instead get cats, loop on cats and in each cat do the select for docs. Would that be the better way to do it? I read once that you should cut down on the round trips you make to the database. Wasn’t sure what would be the right way to go.

Thanks!

[chorn]

you may want to reverse your logic: select all categories inner join with docs order by category. so you run exactly one query and can break up the results with array_reduce later or even directly with PDO::FETCH_GROUP.