0

I have a mySQL database table with columns like:

Item

  • item_id
  • name
  • size (e.g., in bytes)
  • description
  • date

And another table, that has a 1:* relationship to the first table, which contains "keywords" that describe items represented by fields in the first table:

Item_Keyword

  • item_id
  • keyword

I use PHP to "select *" from the Item table, and then iterate over the returned rows (using a while loop with fetch_assoc()) to build a HTML table containing all of the data.

On each iteration of the while loop, I execute another query for all of the keyword(s) from the Item_Keyword table which match the id of the current tuple from the Item table, and then add these to a column in the table, concatenated together:

$res = getItems();
if ($res)
{
  while ($row = $res->fetch_assoc())
  {
    ...
    $itemID = $row['item_id];
    $res2 = getKeywords($itemID);
    if ($res2)
    {
      $keywords = "";
      while ($row2 = $res2->fetch_assoc())
      {
        if (strlen($keywords) > 0)
          $keywords .= ", ";
        $keywords .= $row2['keyword'];
      }
      $text .= "<td> " . $keywords . " </td>";
     }
   }
}

So I end up with a table that looks something like:

ID  Name    Size  Description  Date    Keywords
0   Widget  1024  Widget one   010101  widget,one,item

I'm trying to make the HTML table columns sortable. I can envisage how that would work with an ORDER BY in my query on the Item table. The trouble is, since the Keywords are stored in a separate table and retrieved with an independent query, I can only ORDER BY one set of query results or the other, but not both - more pithily, I can't see any way of sorting by values in the Keyword column.

Is there some kind of esoteric query that I could construct that would allow me to SELECT the keyword(s) for an item, and include these in the "FROM" parameters for the query on the Item table? Something like:

SELECT item_id, name, size, description, date (SELECT keyword FROM Item_Keyword WHERE item_id = 0)
FROM Item
WHERE item_id = 0
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
AMarch
  • 153
  • 2
  • 14
  • use NATURAL JOIN in your select query and pull all the data at once – Pwner Oct 04 '13 at 21:39
  • This may be a less desirable approach depending on your program, but you can sort tables with jQuery after they're made. http://stackoverflow.com/questions/3160277/jquery-table-sort – DACrosby Oct 04 '13 at 21:43

1 Answers1

1

First off, you should probably be grabbing all of your data with a single query like this:

SELECT
  i.item_id AS `item_id`,
  i.name AS `name`,
  i.size AS `size`,
  i.description AS `description`,
  i.date AS `date`,
  GROUP_CONCAT(ik.keyword) AS `keywords`
FROM item AS i
LEFT JOIN item_keyword AS ik
  ON item.item_id = item_keyword.item_id
GROUP BY i.item_id
ORDER BY i.item_id ASC, ik.keyword ASC /* OR whatever sort you desire */

Note here that I use GROUP_CONCAT() and a GROUP BY clause in order to collapse all keyword entries into a single row associated with each item_id.

This gets you everything you need in a single database call eliminating all those unnecessary extra queries. You can do this without a group by as well, you would just have more rows (i.e. an item with 3 keywords would result in 3 rows). With this approach, you could read your data into a multi-dimensional array before rendering the table.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • That's absolutely brilliant, thank you so much for sharing your knowledge. I've never progressed beyond "absolute beginner" with query writing, your example demonstrates the power of more advanced learning. Much appreciated. – AMarch Oct 05 '13 at 16:55
  • @AMarch No problem. Just realize that, if in the future you see yourself querying the database in a loop like that, it is probably indicative of an anti-pattern that could best be handled by revising your approach to retrieving the data. – Mike Brant Oct 07 '13 at 16:32