SQL help please

[Cabbit]

I have a query here but i would also like to make it so that it will take any of the reply for LIKE'%$search%' but i cant get it without failing i know its to do with the grouping "GROUP BY post.`id`" but i cant work out how to sort it out.

$sql = "SELECT
post.`id`, 
post.`post-id`, 
post.`username`, 
post.`date`, 
post.`subject`, 
post.`post`, 
post.`lastpost`,
post.`views`,
post.`lastposttime`,
reply.`reply-id`,
reply.`username` AS `reply_username`,
reply.`date` AS `reply_date`,
reply.`subject` AS `reply_subject`,
reply.`post` AS `reply_post`,
COUNT(DISTINCT reply.`id`) AS `replycount`,
topic.`id` AS `topic_id`,
topic.`title` AS `topic_title`,
topic.`category_id` AS `topic_cat_id`
FROM 
`forum-posts` 
AS
`post` 
LEFT JOIN `forum-reply` 
AS 
`reply` 
ON 
post.`id` = reply.`reply-id`
LEFT JOIN `forum-topics` 
AS 
`topic` 
ON 
post.`post-id` = topic.`id`
WHERE 
post.`post` LIKE'%$search%'
OR
post.`username` LIKE'%$search%'
OR
post.`date` LIKE'%$search%'
OR
post.`subject` LIKE'%$search%'
GROUP BY post.`id`
";

 

[angelwatt]

Don't you need a semicolon at the end of the MySQL statement? Just an idea. I don't mess with MySQL much.

...
GROUP BY post.`id`
[B];[/B]";

 

[Cabbit]

Don't you need a semicolon at the end of the MySQL statement? Just an idea. I don't mess with MySQL much.

...
GROUP BY post.`id`
[B];[/B]";

does not affect SQL query.

problem occurs when i follow on my search

OR
post.`subject` LIKE'%$search%'
OR
`reply_date` LIKE'%$search%'

this is one of my thoughts but it dont work

WHERE
post.`post` LIKE'%$search%'
OR
post.`username` LIKE'%$search%'
OR
post.`date` LIKE'%$search%'
OR
post.`subject` LIKE'%$search%'
GROUP BY post.`id`
AND WHERE
`reply_date` LIKE'%$search%'
ORDER BY `replyid`

 

[Winterfell]

One typically groups by anything in a SQL query that is not an aggregate value, i.e. you probably need to include every field in your GROUP BY other than the `replycount`.


~Colin

 

[JFreak]

Yes, it seems you have misunderstood the group by -clause. It goes like this:

select col1, col2, count(col3)
from somewhere
group by col1, col2;

In other words, all columns that do not have some function have to be introduced in the group by clause. If you have extra columns in the list (that are missing from the group by -clause), the query will not work.

 

[garybUK]

Also if your doing group by's instead of WHERE try HAVING

 

[JFreak]

Also if your doing group by's instead of WHERE try HAVING

There's a semantic difference between the two. Consider this:

SELECT col1,col2,count(col3) FROM table
WHERE col1 > 100
GROUP BY col1,col2 HAVING count(col3) < 100

As you can clearly see, the WHERE-clause is working on the actual rows of the table leaving out all rows where col1 is hundred or less. Grouping is done AFTER that phase, and as the very last step we're leaving out those GROUPS that have count(col3) hundred or more.

So...

WHERE acts first and works with table rows,
HAVING acts last and works with grouping rows.