I am putting together a database with users, items, and tags. I have three tables to support this structure. The first is the "items" table, which contains all of the details about each individual submitted item. The second is the "tags" table, which has two columns: tag_id and tag_text. The third is the "useritemtag" table, which brings everything together with three columns: user_id, item_id, and tag_id.
When a user submits an item, a new row is put into table "items" with all of the relevant information. At the same time, any (non-duplicate) tags will be added to the "tags" table, and assigned a unique ID. Finally, a row will be added to "useritemtag" with the user_id, item_id, and tag_id recorded for each tag submitted.
That is the basic structure I am working with. I have the INSERT queries working perfectly.
Now, I am trying to pull this data into an HTML table that lists all of the items. I would like all of the tags for each item to show up in a column, linked to a search query to show all items with that tag. The way to do this would be:
1. Get the item_id for each item.
2. Get all the tag_id for each item_id under the "useritemtag" table.
3. Use the tag_ids retrieved to finally print the tag_text.
I have worked on this for days, trying multiple things, and I just can't figure out how to retrieve all of the tags for each item, even though I've come close many times. Here is the code I am currently working with to retrieve the tags for each item:
$query = mysql_query("SELECT * FROM items $where_query ORDER BY $sort_order");
$items_array = array();
while ($data = mysql_fetch_assoc($query)) {
array_push($items_array, $data['item_id']);
foreach ($items_array as $items) {
// Get all tag_id from table "useritemtag" for the relevant item_id, push into array
$query0 = mysql_query("SELECT * FROM useritemtag WHERE item_id = $items");
$tag_ids = array();
while ($result0 = mysql_fetch_row($query0)) {
$tagid = $result0['tag_id'];
array_push($tag_ids,$tagid);
}
$or = "";
while ( list ($key, $val) = each($tag_ids) ) {
$sql = "$sql ${or}'${val}'";
$or = " OR ";
}
// Get all tag_text from table "tags" for the relevant tag_ids, push into array
$tag_texts = array();
$query1 = mysql_query("SELECT * FROM tags WHERE tag_id = $sql");
while ($result1 = mysql_fetch_row($query1)) {
$tag_text = $result1['tag_text'];
array_push($tag_texts, $tag_text);
}
for ($i = 0; $i < count($tag_ids); $i++) {
$tags_ids_arr = explode(", ", $tag_ids);
}
for ($i = 0; $i < count($tag_texts); $i++) {
$tags_texts_arr = explode(", ", $tag_texts);
}
}
}Any help would be greatly appreciated. I'm not a professional programmer, it's all self-taught so apologies in advance if the code isn't so great. I hope my explanation was sufficiently clear.