AMarch AMarch - 1 month ago 11
MySQL Question

mySQL, PHP, HTML table with sortable columns - but issue with some column values

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

Answer

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.

Comments