MySql SELECT... LIKE... $variable syntax problem?

Hello! I have a problem trying to search on one of my db tables which uses …
phpMyAdmin (Version information: 3.3.9) and Apache/2.2.17 (Win32) mod_ssl/2.2.17 OpenSSL/0.9.8o PHP/5.3.4 mod_perl/2.0.4 Perl/v5.10.1
MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
PHP extension: mysql

I am using the following php code to do the search…

		OpenDB();
		
		$element = $_REQUEST["element"];
		$criteria = mysql_real_escape_string($_REQUEST["criteria"]);
		switch ($element)
		{
		case "datetime":
			$sql = "SELECT * FROM `vehicles` WHERE datetime LIKE '%".$criteria."%'";
			$row = mysql_query($sql) or die(mysql_error());
			break;
		case "fullname";												
			$sql = "SELECT * FROM `vehicles` WHERE fullname LIKE '%".$criteria."%'";
			$row = mysql_query($sql) or die(mysql_error());
			break;
		case "make":
			$sql = "SELECT * FROM `vehicles` WHERE make LIKE '%".$criteria."%'";
			$row = mysql_query($sql) or die(mysql_error());
			break;
		case "year":
			$sql = "SELECT * FROM `vehicles` WHERE year LIKE '%".$criteria."%'";
			$row = mysql_query($sql) or die(mysql_error());
			break;
		case "suburb":
			$sql = "SELECT * FROM `vehicles` WHERE suburb LIKE '%".$criteria."%'";
			$row = mysql_query($sql) or die(mysql_error());
		}

and receive the following error message

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 ‘’ at line 1

I am truly perplexed! Can anyone see the wood for the trees?:rolleyes:

Welcome to the SP forums :slight_smile:

Do a print_r($_REQUEST) and an echo of $sql to see if things are as you think they are.

This I did with the following result

SELECT * FROM `vehicles` WHERE fullname LIKE '%Christeen%'

which I also entered via phpMyAdmin on my localhost xampp, with a positive result!? But I still get

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 ‘’ at line 1
when running the script!??

That query looks fine to me. Are you sure there is no other point where you are running a query that might give that error? You could change the ‘or die’ message (include some kind of identification) so that you’re sure the error is coming from that particular query.

As advised I put this code into the sql statement -

		case "fullname";												
			$sql = "SELECT * FROM `vehicles` WHERE `fullname` LIKE '%".$criteria."%'";
			$row = mysql_query($sql) or die("A MySQL error has occurred.<br />My Query: " . $sql . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());
			break;

Curiously, exactly the same response…

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 ‘’ at line 1

No sign of my altered message???

So I took out the open db code to force another error and got this response…

SELECT * FROM vehicles WHERE fullname LIKE ‘%Christeen%’
A MySQL error has occurred.
My Query: SELECT * FROM vehicles WHERE fullname LIKE ‘%Christeen%’
Error: (1046) No database selected

Like I thought, the error is caused somewhere else in your script. A query is executed AFTER the one you thought caused the error.

You are absolutely correct Sir! I put an echo at the end of the switch/case script and it displayed!
Thanks so much for your prompt and helpful advice. I shall report back on my eventual findings at this thread, for the sake of continuity.

Yes, once again thank you for your help:)

I found the faux-pas that was causing the error…

After the search comes a display (in a while loop) of the main page - I was effectively destroying the sql select data used by the main page query…:blush: