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

Question

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

use NATURAL JOIN in your select query and pull all the data at once — Pwner
– Pwner, Commented Oct 4, 2013 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. stackoverflow.com/questions/3160277/jquery-table-sort — DACrosby
– DACrosby, Commented Oct 4, 2013 at 21:43

Mike Brant · Accepted Answer · 2013-10-04 21:49:10Z

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.

edited Oct 4, 2013 at 21:49

answered Oct 4, 2013 at 21:43

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AMarch Over a year ago

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.

Mike Brant Over a year ago

@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.

Collectives™ on Stack Overflow

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

Item

Item_Keyword

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Item

Item_Keyword

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related