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

[JiminSA]

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?

[guido2004]

Welcome to the SP forums

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

[JiminSA]

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!??

[guido2004]

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.

[JiminSA]

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???

[JiminSA]

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

[guido2004]

Curiously, exactly the same response...

No sign of my altered message???

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

[JiminSA]

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.

[JiminSA]

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...