selecting using distinct

[jwk811]

im trying to select the posts that a specific user has commented. its returning multiple posts and im using DISTINCT so i dunno if thats the only problem or not. please help

 

	$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
			WHERE tbl_comments.comment_type = 'forum' 
				AND tbl_comments.post_id = forum_posts.post_id 
				AND forum_posts.user_id = '$user_id'

					ORDER BY tbl_comments.date DESC LIMIT 10";
$result = dbQuery($sql);

table structures

 

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)

[mrMarcus]

distinct will return multiple posts as that's not its purpose.  it will NOT return identical fields, ie. if there are several 'forum_posts.post_name' with the exact same value.

 

i'm not 100% what you're trying to accomplish.

 

and what is dbQuery()?  did you create an alias function for mysql_query()?

[jwk811]

distinct will return multiple posts as that's not its purpose.  it will NOT return identical fields, ie. if there are several 'forum_posts.post_name' with the exact same value.

 

i'm not 100% what you're trying to accomplish.

 

and what is dbQuery()?  did you create an alias function for mysql_query()?

im trying to select the POSTS that a specific user commented. in the list of these posts it is repeating posts that the user posted in multiple times. i only want that post to show once. and yes dbQuery is for mysql query

 

and ideas?

[mrMarcus]

first things first.  creating aliases are redundant.  use built-in PHP functions instead, ie. mysql_query() instead of dbQuery().

 

try GROUP BY instead of DISTINCT:

 

$sql = "
SELECT `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`
WHERE `tbl_comments`.`comment_type` = 'forum' 
AND `tbl_comments`.`post_id` = `forum_posts`.`post_id`
AND `forum_posts`.`user_id` = '$user_id'
GROUP BY `post_name`
ORDER BY `tbl_comments`.`date`
DESC LIMIT 10
";
$result = dbQuery($sql);

 

consider using the MySQL Help forum next time.  those guys are good.

 

EDIT: already moved.