php+mysql: extracting all tags for each article

I'm trying to display the last 20 articles, and their respective tags (each article has about 3-4 tags). I have an article table and a tag table and for each article there are 3-4 rows of tags with article id.

What I know how to do is :

$result = $mysqli->query("SELECT * FROM items  LIMIT 0,24 ");
while ($row = $result->fetch_object()) {
       $result2 = $mysqli->query("SELECT tag FROM tags WHERE item_id = ".$row->id);
               while ($row2 = $result2->fetch_object()) {
                      echo $row2 . '<br>';
                }
 }

When I try to do a join i get each article appear in 3-4 rows.

This seems really not clean. is there a more elegant way of extracting the tags for each article? I'm open to hear about different ways of storing the data as well, I just started the project...

Answers


you could try SELECT DISTINCT itemID FROM items LIMIT 0,24

this will only return one result per itemID


Good practice would be joining the two tables, but because in theory, an article can have many tags, this would create some overhead.

So in this case, best you can do is; Storing the results of your first query, in an associative array, where keys are the item IDs:

    $items = array();
    $result = $mysqli->query("SELECT * FROM items  LIMIT 0,24 ");
    while ($row = $result->fetch_object()) {
      $items[$row->item_id] = $row;
      $items[$row->item_id]->tags = array();
    }

After this iteration, you execute the second query, but with a selector on all item IDs:

    $result2 = $mysqli->query("SELECT item_id, tag FROM tags WHERE item_id IN (".implode(',', array_keys($items)).")");

    while ($row2 = $result2->fetch_object()) {
      $items[$row2->item_id]->tags[] = $row2->tag;
    }

This way, you fill the complete multidimensional array in just 2 queries.


Need Your Help

JQuery Toggle Image not working

jquery jquery-click-event jquery-attributes

I am simply trying to toggle the image source with a click using JQuery. My script is below, I am not sure why it's not toggling. When the page first loads, if you click the gray image, it toggle...