By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

use group by with query

P: 4
this code returns only one record per lot num unless a SEARCH query is run PLEASE HELP
Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT
  2.                    Payee,
  3.                   Street,
  4.                   City,
  5.                   State,
  6.                   Zip,
  7.                   homephone,
  8.                   lotnum
  9.  
  10.                 FROM
  11.                        CCADDRESS GROUP BY lotnum";
  12.  
  13.  // ************* search *****************/
  14.  if( isset($_GET['search']) && !empty($_GET['search'])) 
  15.  {
  16.     // WHERE found ?
  17.     if(!stristr($query,"WHERE "))
  18.      $query .= " WHERE (";
  19.     else
  20.      $query .= " AND (";
  21.  
  22.     // add field
  23.      $query .= " Payee LIKE '%".addslashes($_GET['search'])."%' OR";
  24.      $query .= " Street LIKE '%".addslashes($_GET['search'])."%' OR";
  25.      $query .= " City LIKE '%".addslashes($_GET['search'])."%' OR";
  26.      $query .= " State LIKE '%".addslashes($_GET['search'])."%' OR";
  27.      $query .= " Zip LIKE '%".addslashes($_GET['search'])."%' OR";
  28.      $query .= " homephone LIKE '%".addslashes($_GET['search'])."%' OR";
  29.      $query .= " lotnum LIKE '%".addslashes($_GET['search'])."%' OR";    
  30.  
  31. // delete last OR
  32. $query = substr($query,0,strlen($query)-3);
  33. $query .= ")";
  34.  
Mar 6 '12 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,347
That SQL shouldn't even execute. It's syntactically incorrect. The fact that it even returns anything other than an error confuses me.
Mar 6 '12 #2

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

change if condition to

if(stristr($query, 'WHERE ') === FALSE)
Mar 8 '12 #3

P: 4
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
Attached Files
File Type: txt sample.txt (7.3 KB, 227 views)
Mar 8 '12 #4

P: 45
Expand|Select|Wrap|Line Numbers
  1. //line no 38: change to 
  2.  CCADDRESS ";
  3.  
  4. //add new code into line no 65:
  5. $query.=" GROUP BY lotnum";
  6.  
Mar 8 '12 #5

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 8 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.