MYSQL & where clause problem

[Anon]

I am just learning php so please bear with me (2nd day!!)

I am trying to use a where clause to search a database. Basically i want to search thru surnames, i came up with, 'Select * from test4 where surname like '%ker%'';
Now this seems to work fine and returns a record if i run it from phpmyadmin, but when i try and run it as part of a php script i keep getting 'query empty'

[Anon]

when you send the query string to php for processing, leave the semicolon out completely.

[[deleted]]

Use error checking and PHP/MySQL will tell you what's wrong:

http://www.xs4all.nl/~vinny/index.html#4

[Anon]

Thanks, i used or die (mysql_error() ) and that just reported 'query was empty' now i know this shouldn't be the case because if i run the query in phpmyadmin i get a result of 1 record !

[[deleted]]

If it says "query was empty" that means that you did not supply a query at all!

[Anon]

This is the section of code that i am using, if i take out the where clause of the select it returns values fine.
Essentially what i am trying to do is to search for a surname or part of surname, ie. someone can enter ker which will match kerwick, skerwin etc(eg %ker% or %k% in the code here)

BTW thanks for the help.

<?php

include( 'dbinfo.php' );
$conn = mysql_connect( $dbhost, $dbuser, $dbpass );
mysql_select_db($dbname);
$sql = 'SELECT * FROM test4 where surname like '%k%'';
$test4Result = mysql_query( $sql ) or die( mysql_error() );
while ( $test4Row = mysql_fetch_row( $test4Result ) ) {
echo '<tr><td>' . $test4Row[1] . '</td><td>' . $test4Row[2] . '</td><td>' . $test4Row[3] .

'</td><td>' . $test4Row[4] . '</td><td>' . $test4Row[5] . '</td></tr>';
}
mysql_close ($conn);

?>

This is my table schema

phpMyAdmin MySQL-Dump

version 2.2.0rc4

http://phpwizard.net/phpMyAdmin/

http://phpmyadmin.sourceforge.net/ (download page)

#

Host: xxx.xxx.xxx.xxx:/var/lib/mysql/mysql.sock:3306

Generation Time: August 29, 2001, 12:49 pm

Server version: 3.23.36

PHP Version: 4.0.4pl1

Database : test

--------------------------------------------------------

#

Table structure for table test4

#

CREATE TABLE test4 (
id int(11) NOT NULL auto_increment,
firstname varchar(40) NOT NULL default '',
surname varchar(40) NOT NULL default '',
role varchar(40) NOT NULL default '',
department varchar(40) NOT NULL default '',
extension varchar(20) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

[[deleted]]

$sql = 'SELECT * FROM test4 where surname like '%k%'';

I very much doubt that this is in your real code, because this is not proper PHP, there are too many single-quotes within single-quotes.

[Anon]

I can assure you that is my real code. I am only on my 2nd day of php so still very much learning 😉
how should this be written?

[kparker]

Apparently you've just uncovered a parser bug, that's been there a very long time. There's no way this statement should be accepted as valid:

&nbsp;&nbsp;$sql = 'SELECT * FROM test4 where surname like '%k%'';

but unfortunately what you do get is the same as if you had written:

&nbsp;&nbsp;$sql = '';

Instead, you need to use double-quotes around the string:

&nbsp;&nbsp;$sql = "SELECT * FROM test4 where surname like '%k%' ";