[PHP, MySQL] nested do/while loop stopping after first iteration

[redrum87]

I'm trying to create a page which will display a list of blog entries from a table (blog) in an SQL database, along with each blog entry's associated tags from another table (tags) in the same SQL database. Each tag has a foreign key that associates it with the proper blog entry. On the page that displays the list of entries, there is a column for these tags, in which all the tags for a particular entry are placed. I realize this might sound confusing, so I have included a screenshot:

http://zoxide.com/blog_table.gif

The table itself is being generated by a do/while loop, while the tags are being generated by a nested do/while loop inside an if statement that compares the current article's primary key (article_id) with all the foreign keys (object_id) in the "tags" table.

So what's the problem? Well, all three of these bogus articles have tags stored in the MySQL database, but they only print for the first article in the loop. Any help would be appreciated. Here's the code:


<?php $counter = 0; //initialize the hilite counter outside the loop ?>
<?php do { ?>
<tr <?php if ($counter++ % 2) {echo 'class="hilite"';} ?>>
<td><?php echo $row_getArticles['created']; ?></td>
<td><?php echo $row_getArticles['title']; ?></td>
<td><?php echo $row_getArticles['username']; ?></td>
<td><?php echo $row_getArticles['image']; ?></td>
<td><?php echo $row_getArticles['caption']; ?></td>
<td><?php
do {
if ($row_getArticles['article_id'] == $row_getTags['object_id']){
echo " *" , $row_getTags['tag'];
}//end if
} while ($row_getTags = mysql_fetch_assoc($getTags));
?></td>
<td><a href="blog_update.php?article_id=<?php echo $row_getArticles['article_id']; ?>">EDIT</a></td>
<td><a href="blog_delete.php?article_id=<?php echo $row_getArticles['article_id']; ?>">DELETE</a></td>
</tr>
<?php } while ($row_getArticles = mysql_fetch_assoc($getArticles)); ?>

[bwh2]

this is a very bad db setup. you are trying to accomplish in one table what should be done in several tables. your setup will lead to very slow query speeds, slow processing speeds, more debugging, and is highly prone to user error. to be blunt, the php you're using isn't even worth looking at until you fix your db design.

if you are interested, i will show you the relational db design that you should be using.

[redrum87]

That screenshot isn't a representation of a database table. That's just a user interface. The data is actually being loaded in from three tables. Here is an ER diagram of the schema, using the information engineering method.

http://zoxide.com/zoxidedb.gif

In case you use something different, || denotes one, 0< is zero, one, or many, * denotes primary keys, and ** denotes foreign keys.

There aren't multiple values for the tags.tag entity. Using the example from my last post, they are stored as such:

article_id is 19, so the object_id is also 19

tag_id: some int. object_id: 19 tag: going
tag_id: int++ object_id: 19 tag: gone
tag_id: int++ object_id: 19 tag: here
tag_id: int++ object_id: 19 tag: we

Sorry for the misunderstanding. Unless of course you did understand and this is still a bad DB design... If thus is the case, I'd like your opinion as to why. If this schema is ok, then I still am stuck on my original question.

[bwh2]

That screenshot isn't a representation of a database table. That's just a user interface. The data is actually being loaded in from three tables.ahhh, now you're making more sense. but your problem now is that you're bound to have duplicate tag values within the tags table. so you just have to make the article_tags table to link the blogs and tags tables:
article_tags
----------------
article_id (FK)
tag_id (FK)once you do that, you have linked the tags to the articles. this allows you to remove the object_id from the tags table. this would make your db relational and in first normal form. mmmm.

[redrum87]

hey, sorry for the late response. florida storms took out my internet connection...

anyway, having a composite entity makes sense. however, something else occured to me. what if i want to tag other things beside articles, such as images, videos, other pages, etc? i'm guessing i would just create other composite entities such as image_tags, video_tags, etc. However, would that slow down searching? Is there a better way?

[bwh2]

what if i want to tag other things beside articles, such as images, videos, other pages, etc?ah yes. good question. let me think about it and get back to you.

[redrum87]

Hm, I think the method I described above would probably be ok. Matching a user inputed string to tags in one column would be fast, but would then finding related data by going through composite tables to the data tables be quick too? I don't have much experience working with databases that have more than two or so tables, so I don't know the answer.

I really can't think of another solution...