use group by with query

[Jim Freeman]

this code returns only one record per lot num unless a SEARCH query is run PLEASE HELP

$query = "SELECT
                   Payee,
                  Street,
                  City,
                  State,
                  Zip,
				  homephone,
	              lotnum
	
                FROM
                       CCADDRESS GROUP BY lotnum";

 // ************* search *****************/
 if( isset($_GET['search']) && !empty($_GET['search'])) 
 {
    // WHERE found ?
    if(!stristr($query,"WHERE "))
	 $query .= " WHERE (";
    else
	 $query .= " AND (";
        
    // add field
     $query .= " Payee LIKE '%".addslashes($_GET['search'])."%' OR";
     $query .= " Street LIKE '%".addslashes($_GET['search'])."%' OR";
     $query .= " City LIKE '%".addslashes($_GET['search'])."%' OR";
     $query .= " State LIKE '%".addslashes($_GET['search'])."%' OR";
     $query .= " Zip LIKE '%".addslashes($_GET['search'])."%' OR";
	 $query .= " homephone LIKE '%".addslashes($_GET['search'])."%' OR";
	 $query .= " lotnum LIKE '%".addslashes($_GET['search'])."%' OR";    
	
// delete last OR
$query = substr($query,0,strlen($query)-3);
$query .= ")";

[Rabbit]

That SQL shouldn't even execute. It's syntactically incorrect. The fact that it even returns anything other than an error confuses me.

[shanmugamit]

The code is little confusion "group by" will come after "where" condition.

change if condition to

if(stristr($que ry, 'WHERE ') === FALSE)

[Jim Freeman]

That was just a partial of the code

I have attached a text file with the whole code
I get this error if I put any value in the search for box

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 'WHERE ( Payee LIKE '%30%' OR Street LIKE '%30%' OR City LIKE '%30%' OR State LIK' at line 12

works fine on view all

Hope this makes it a little clearer

[shanmugamit]

//line no 38: change to 
 CCADDRESS ";

//add new code into line no 65:
$query.=" GROUP BY lotnum";

[Rabbit]

1) A GROUP BY clause must come after a WHERE clause.
2) A GROUP BY clause must include all non-aggregated fields.
3) A GROUP BY clause is only needed for aggregations.