comlicated db SELECT FROM DATABASE

[jwk811]

if you can tell what im doing. im trying to select forum posts that have new comments which are newer than the last time i have read that particular topic.

 

	$sql = "SELECT DISTINCT forum_posts.post_name AS post_name, forum_posts.post_id AS post_id, tbl_comments.user_id AS commenter_id
		FROM tbl_comments, forum_posts, topics_read
			WHERE tbl_comments.comment_type = 'forum' 
				AND tbl_comments.post_id = forum_posts.post_id
				AND topics_read.topic_id = forum_posts.post_id
				AND topics_read.user_id = '$member_user_id'
				AND topics_read.read_at > tbl_comments.date";

 

im getting some rows, dunno why im getting these. there are repeated post names and its definently not giving me the results i wanted.

 

what is wrong here?

[Bladescope]

Any chance you can provide a more detailled structure of the DB? (The tables involved only)

 

I have a theory, but I'm going to need some more info.

[jwk811]

forum_posts

-post_id

-post_name

-post_text

-user_id

 

*that table has all the forum topics

 

tbl_comments

-comment_id

-comment_type (forum, photo, video, etc.)

-date (time posted)

-user_id

-post_id (id of the forum post)

 

*these are the comments that people write for each forum post topic

 

topics_read

-user_id

-topic_id

-read_at

 

*everytime someone goes to a forum post. it says the user and the topic and what time they looked at it, when they go back it just changes the read_at time

 

topics_read.read_at AND tbl_comments.date are both DATETIME

[Bladescope]

SELECT DISTINCT 
forum_posts.post_name AS post_name, 
forum_posts.post_id AS post_id, 
tbl_comments.user_id AS commenter_id
FROM 
tbl_comments 
	NATURAL JOIN forum_posts
		ON (tbl_comments.post_id = forum_posts.post_id)
	NATURAL JOIN topics_read
		ON (topics_read.topic_id = forum_posts.post_id)  
WHERE
tbl_comments.comment_type = 'forum'
        AND topics_read.user_id = '{$member_user_id}'
        AND tbl_comments.date > topics_read.read_at

 

Try this first. I'll be amazed if it works but it probably won't.

 

Tell me what error gets back if it does fail. ( using mysql_query(...) OR DIE(mysql_error());  )

 

Edit: Changed some syntax.

[jwk811]

seems like a minor error but i wouldnt know how to fix it. i dont understand the natural joining stuff lol

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON (tbl_comments.post_id = forum_posts.post_id) NATURAL JOIN topics_read ' at line 8

 

btw, thanks for helping!

[Bladescope]

This is rattling my brain =p

 

You see, a small thing is that you're wanting to seelct forum topics with posts > last viewed time.

Thing is, you're also wanting to select a comment poster's id, note how it's single.

 

A problem with that is, if multiple commenters post on the same topic after you have viewed it, SQL will give back an error because it's trying to select a single value from multiple options.

 

I'm still working on it, don't worry

[jwk811]

This is rattling my brain =p

 

You see, a small thing is that you're wanting to seelct forum topics with posts > last viewed time.

Thing is, you're also wanting to select a comment poster's id, note how it's single.

 

A problem with that is, if multiple commenters post on the same topic after you have viewed it, SQL will give back an error because it's trying to select a single value from multiple options.

 

I'm still working on it, don't worry

well im executing the data using a while loop so it should show all the comments. maybe thats not what you meant..

[Bladescope]

SELECT forum_posts.post_id, forum_posts.post_name, COUNT(comment_id)
FROM topics_read LEFT OUTER JOIN tbl_comments 
ON (topics_read.topic_id = tbl_comments.post_id), forum_posts
WHERE topics_read.read_at < tbl_comments.date
AND topics_read.topic_id = forum_posts.post_id
AND topics_read.user_id = '{$member_user_id}' 
GROUP BY (forum_posts.post_id)

 

This query returns each topic's id and name with the number of newly posted comments (since the user last checked).

 

Finally figured it out .

 

 

Edit:

 

well im executing the data using a while loop so it should show all the comments. maybe thats not what you meant..

 

Well, you can make a foreach loop for each row returned.

That way, you can get the data for each seperate topic using the topic id (aka post id) as a filter

[jwk811]

WOO HOO it works. ur a genius TYTYTYTY

[Bladescope]

No problem :3.

 

I had to make a small emulation from the table structures you gave me on my local server to test it all out.

 

Good fun, really trivial and awesomely satisfying :3.