CODEWITHSUNDEEP
phphtmlmysql
dcclassics
dcclassics

Reputation: 896

PHP Logic - For each Inside While Loop duplicating items from MySQL query

I am having a bit of trouble wrapping my head around this scenario. I've got 3 tables, typical tag, post, tag_post mapped tables.

I have a page that displays the posts, basically just does the query SELECT * from POST, then inside the while statement for these results I query SELECT tag_id FROM tag_post WHERE post_id = ? with ? being the post_id from the parent while loop. I also have the HTML for each post inside this parent loop.

I obtain the tag_id values from this second query, and then lower in my HTML create an additional foreach ($tagIds as $tag) {} to fetch the tag_name values from the tag table.

My problem is, every time the loop runs, the tags from the more recent iteration are included. So my second post has all the tags from the first post, third has tags from all three etc. I know my problem is that I'm running the HTML twice, but as I said I can't fathom how to correct this.

Am I taking too many steps? Could it be done much more efficiently than how I'm doing it?

I can post code if needed.

Upvotes: 0

Views: 124

Answers (1)

mesutozer
mesutozer

Reputation: 2859

You can get all required data with a single query:

"SELECT p.* FROM post p
INNER JOIN tag_post tp ON (tp.post_id = p.id)
INNER JOIN tag t ON (t.id = tp.tag_id)
WHERE p.post_id = ?"

I guess you are currently accumulating data in some arrays and you do not empty the tags array. So in each iteration new tags always added to that list.

Upvotes: 2

Related Questions