php mysql where do i

[waterboy]

Hello,
I new to PHP and i dont know where i can put the "GROUP BY `uid`" so that my seach will work
Here is part of my code
[code]$query = "SELECT `input` , count( * ) AS input ,
                  `bot` as bot,
  `id` as id,
  `response` as response,
  `uid` as uid,
  `enteredtime` as enteredtime
          FROM `conversationlog`
  GROUP BY `uid`";

// ************* search *****************/
if( isset($_GET['search']) && !empty($_GET['search']))
{
    // WHERE found ?
    if(!stristr($query,"WHERE "))
$query .= " WHERE (";
    else
$query .= " AND (";
       
    // add field
    $query .= " bot LIKE '%".addslashes($_GET['search'])."%' OR";
    $query .= " id LIKE '%".addslashes($_GET['search'])."%' OR";
    $query .= " input LIKE '%".addslashes($_GET['search'])."%' OR";
    $query .= " response LIKE '%".addslashes($_GET['search'])."%' OR";
    $query .= " uid LIKE '%".addslashes($_GET['search'])."%' OR";
    $query .= " enteredtime LIKE '%".addslashes($_GET['search'])."%' OR";

// delete last OR
$query = substr($query,0,strlen($query)-3);
$query .= ")";//GROUP BY `uid`[/code]

When i used the search i get
[code]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 ( bot LIKE '%21d05352bb6884d0ce49e80f5f34f740%' OR id LIKE '%21d05352bb688'[/code]

i think its the Goup by that is the probleme..

CAn someone point me in the right direction? i have attache the full code

[attachment deleted by admin]

[trq]

Get rid of all those backticks ( ` ).

[Jessica]

What datatype is bot?

[matto]

Your 'group by' needs to be after your WHERE....

:)

[waterboy]

Here is the defnition of the table
[code]CREATE TABLE `conversationlog` (
  `bot` tinyint(3) unsigned NOT NULL default '0',
  `id` int(11) NOT NULL auto_increment,
  `input` text,
  `response` text,
  `uid` varchar(255) default NULL,
  `enteredtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `botid` (`bot`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

--
-- Dumping data for table `conversationlog`
--

INSERT INTO `conversationlog` VALUES (1, 1, 'what is your name', 'My name is Anna.', '21d05352bb6884d0ce49e80f5f34f740', '2006-12-23 21:14:13');
[/code]


I tryed putting it at the last line
[code]$query .= "GROUP BY `uid`)";//[/code]

But i get this error

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[matto]

can I suggest you put the following

[code]
echo "<tt>$query</tt>";
exit;
[/code]

just before this:

[code]
$result = mysql_query($query) or die(mysql_error());
[/code]

and post back what it outputs.

:)

[waterboy]

Here are the results
[code]SELECT `input` , count( * ) AS input , `bot` as bot, `id` as id, `response` as response, `uid` as uid, `enteredtime` as enteredtime FROM `conversationlog` GROUP BY `uid`[/code]

[trq]

I'll say it again. get rid of all those backticks!

[HuggieBear]

If MySQL abides by the same rules as oracle then you [color=red][b]MUST[/b][/color] have any columns that appear in the select statement, that aren't group functions, in the GROUP BY clause...

[code]SELECT employee, department, AVG(salery)
FROM employees
GROUP BY employee[/code]

The above will give an error, the query must look like this...

[code]SELECT employee, department, AVG(salery)
FROM employees
GROUP BY employee, department[/code]

I hope this helps

Regards
Huggie

[waterboy]

Igot get rid of all those backticks. no change.
Tryed adding all the field in the groupe by but got the same error

[waterboy]

With a bit of tweeking and some good advice from you, it works!

THanks everyone!!!