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.

you wish to select all tag id and tag text for every item?
well , you can do that in one query

SELECT u.item_id, t.tag_id, t.tag_text FROM useritemtag u, tags t WHERE u.tag_id = t.tag_id ORDER BY u.item_id ASC;

Well, I need to select it for every item but it needs to be separated into rows, so not selecting all at once. I need to select the tag_id and tag_text for each item separately.

For example, this is what my final HTML table will look like:

Item1 | Item1Detail1 | Item1Detail2 | Tag1, Tag2, Tag3
Item2 | Item2Detail1 | Item2Detail2 | Tag4, Tag5, Tag6, Tag1
Item3 | Item3Detail1 | Item3Detail2 | Tag6, Tag3

Etc...

Well, I need to select it for every item but it needs to be separated into rows, so not selecting all at once. I need to select the tag_id and tag_text for each item separately.

For example, this is what my final HTML table will look like:

Item1 | Item1Detail1 | Item1Detail2 | Tag1, Tag2, Tag3
Item2 | Item2Detail1 | Item2Detail2 | Tag4, Tag5, Tag6, Tag1
Item3 | Item3Detail1 | Item3Detail2 | Tag6, Tag3

Etc...

you mean one item at a time?

still can be done in one query :)

SELECT u.item_id, t.* FROM useritemtag u, tags t WHERE u.item_id = 'some id' AND u.tag_id = t.tag_id ORDER BY u.tag_id DESC;

this will select all tags and tag ids for that "some id".

use a loop to get all those tags into one array

tags[] = array('tag_id'=>'tag_text');

Thanks for your reply. :) I will try this out today and see how it goes.

Sorry if this is really clueless, but what do the "u" and "t" values stand for in that SELECT statement?

And I'm guessing that I will implode the tags[] array at the end to display it properly?

Thanks again for the help!

what do the "u" and "t" values stand for in that SELECT statement?

they're just aliases for the tables so that the database would know which column belongs to which table.

And I'm guessing that I will implode the tags[] array at the end to display it properly?

yes

Thanks again for your help. I think I'm having a very difficult time understanding how implode works, even though I've probably read 50 websites about it. What would be the proper way of imploding this data? Here is the code I'm using now:

$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) {

$query0 = mysql_query("SELECT u.item_id, t.* FROM useritemtag u, tags t WHERE u.item_id = '$items' AND u.tag_id = t.tag_id");
$get_tags = array('tag_id'=>'tag_text');

$output_tags = implode(", ", $get_tags);
}

$tags_html = "<a href=\"=". $output_tags['tag_id'] ."\">". $output_tags['tag_text'] ."</a>";

The result is that every row of the table just says "t" for tags.

Thanks again for helping out.

this part is done wrong

foreach ($items_array as $items) {

$query0 = mysql_query("SELECT u.item_id, t.* FROM useritemtag u, tags t WHERE u.item_id = '$items' AND u.tag_id = t.tag_id");
$get_tags = array('tag_id'=>'tag_text');

$output_tags = implode(", ", $get_tags);
}

here's how it should be

foreach ($items_array as $items) {

$query0 = mysql_query("SELECT u.item_id, t.* FROM useritemtag u, tags t WHERE u.item_id = '$items' AND u.tag_id = t.tag_id");

while($row = mysql_fetch_row($query0))
{
 $get_tags[] = array($row['tag_id']=>$row['tag_text']);
}

$output_tags = implode(", ", $get_tags);
}

So I tried your code, but I'm still having some issues... I did some more research and this is the code I'm working with now. Now, when I check the HTML page, the output is "Array" for every row where there should be tags.

I tried doing print_r() to figure out where the problem was.
$item works fine, but when I tried to printr() on $row and $row... nothing.

I can't figure out what could be wrong. I checked all of the table and column names on my MySQL database and everything matches. I even changed the name of two columns in different tables because they had the same name (tag_id) and I thought maybe that was causing the problem. But still, nothing.

$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 => $item) {

$query0 = mysql_query("SELECT u.uit_item_id, t.* FROM useritemtag u, tags t WHERE u.uit_item_id = $item AND u.uit_tag_id = t.tag_id");

while($row = mysql_fetch_row($query0)) {
$get_tags = array(array(tags_ids => $row['tag_id']), array(tags_texts => $row['tag_text']));
$count = count($get_tags);
}

for ($i = 0; $i < $count; $i++) {
$tags_html = "<a href=\"index.php?t=". $get_tags[$i]['tags_ids'] ."\">". $get_tags[$i]['tags_texts'] ."</a>";
}

I really appreciate the help. Thanks in advance.

So I tried your code, but I'm still having some issues... I did some more research and this is the code I'm working with now. Now, when I check the HTML page, the output is "Array" for every row where there should be tags.

I tried doing print_r() to figure out where the problem was.
$item works fine, but when I tried to printr() on $row and $row... nothing.

I can't figure out what could be wrong. I checked all of the table and column names on my MySQL database and everything matches. I even changed the name of two columns in different tables because they had the same name (tag_id) and I thought maybe that was causing the problem. But still, nothing.

$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 => $item) {

$query0 = mysql_query("SELECT u.uit_item_id, t.* FROM useritemtag u, tags t WHERE u.uit_item_id = $item AND u.uit_tag_id = t.tag_id");

while($row = mysql_fetch_row($query0)) {
$get_tags = array(array(tags_ids => $row['tag_id']), array(tags_texts => $row['tag_text']));
$count = count($get_tags);
}

for ($i = 0; $i < $count; $i++) {
$tags_html = "<a href=\"index.php?t=". $get_tags[$i]['tags_ids'] ."\">". $get_tags[$i]['tags_texts'] ."</a>";
}

I really appreciate the help. Thanks in advance.

forgive me, in my last post i was forgetting all about the implode function,

this code

while($row = mysql_fetch_row($query0)) {
$get_tags = array(array(tags_ids => $row['tag_id']), array(tags_texts => $row['tag_text']));
$count = count($get_tags);
}

should actually be

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];
}

this will create an array
$get_tags = array('tag_id1'=>'tag_text1', 'tag_id2'=>'tag_text2',....etc)

then you can use foreach to output the hyperlinked tag text (which now i understand is what you want)

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
}

$tags_html = substr($tags_html, 0 ,-2);

Thanks again for your input. I am working with the code you posted, and I'm working on putting it into a recursive function. This is a little more code to illustrate where it will be going on the page:

function displayTags($x) {
$query0 = mysql_query("SELECT uit.item_id, tags.* FROM uit, tags WHERE uit.tag_id = tags.tag_id AND uit.item_id = $x");

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
$tags_html = substr($tags_html, 0 ,-2);
}
}
}

$data_full = "";
$query = mysql_query("SELECT * FROM items $where_query ORDER BY $sort_order");

while ($data = mysql_fetch_assoc($query)) {
$item = $data['item_id'];

displayTags($item);

$data_full .= "<tr>\n
<td><a href=\"\">". $data['garment'] ."</a></td>\n
<td><a href=\"\">". $data['color'] ."</a></td>\n
<td><a href=\"\">". $data['brand'] ."</a></td>\n
<td><a href=\"\">". $data['size'] ."</a></td>\n
<td>". $data['price'] ."</td>\n
<td>". $tags_html ."</td>\n
<td>". $data['notes'] ."</td>\n
</tr>\n";
}

Right now the "tags" column returns blank, but I will be working on it more tonight. Thanks for your help so far. :)

the foreach loop should be outside the while loop

this code is wrong

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
$tags_html = substr($tags_html, 0 ,-2);
}
}

it should be like this

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];
}

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
}

$tags_html = substr($tags_html, 0 ,-2);

the foreach loop should be outside the while loop

this code is wrong

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
$tags_html = substr($tags_html, 0 ,-2);
}
}

it should be like this

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];
}

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
}

$tags_html = substr($tags_html, 0 ,-2);

the foreach loop should be outside the while loop

this code is wrong

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
$tags_html = substr($tags_html, 0 ,-2);
}
}

it should be like this

while($row = mysql_fetch_row($query0)) {
$get_tags[$row['tag_id']] = $row['tag_text'];
}

foreach ($get_tags as $tag_id => $tag_text) {
$tags_html .= "<a href=\"index.php?t=". $tag_id ."\">". $tag_text ."</a>".", ";
}

$tags_html = substr($tags_html, 0 ,-2);
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.