SQL help please

Discussion in 'Web Design and Development' started by Cabbit, Jun 29, 2008.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    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.

    HTML:
    $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`
    ";
    
     
  2. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #2
    Don't you need a semicolon at the end of the MySQL statement? Just an idea. I don't mess with MySQL much.
    Code:
    ...
    GROUP BY post.`id`
    [B];[/B]";
     
  3. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #3
    does not affect SQL query.

    problem occurs when i follow on my search

    HTML:
    OR
    post.`subject` LIKE'%$search%'
    OR
    `reply_date` LIKE'%$search%'
    
    this is one of my thoughts but it dont work
    Code:
    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`
    
     
  4. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #4
    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
     
  5. JFreak macrumors 68040

    JFreak

    Joined:
    Jul 11, 2003
    Location:
    Tampere, Finland
    #5
    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.
     
  6. garybUK Guest

    garybUK

    Joined:
    Jun 3, 2002
    #6
    Also if your doing group by's instead of WHERE try HAVING
     
  7. JFreak macrumors 68040

    JFreak

    Joined:
    Jul 11, 2003
    Location:
    Tampere, Finland
    #7
    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.
     

Share This Page