-1

I am trying to select data from a database that has been selected by the user on the previous page.

I am using $_POST to get the data and have the below query

$result=mysql_query("SELECT * FROM $table WHERE
  $field   LIKE'%$fieldvalue%'   AND
  $filter1 LIKE'%$filter1value%' AND  
  $filter2 LIKE'%$filter2value%' AND
  $filter3 LIKE'%$filter3value%' AND
  $filter4 LIKE'%$filter4value%' ")or die('ERROR 238 - ' . mysql_error());

This is bringing back the error

ERROR 238 - 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 ' LIKE '%%'' at line 2

Can anyone help by seeing what I have done wrong?

UPDATE

The variables could be blank - how do I check this first? Thanks

2 Answers2

0

First things first we need to alter your code to enable some debugging of the code:

I have added curley braces as I find them easier to read. (more on those herE: Curly braces in string in PHP). I have also added backticks to the SQL which are used to mark field names.

$sql="SELECT * FROM $table WHERE
`{$field}`   LIKE'%{$fieldvalue}%'   AND
`{$filter1}` LIKE'%{$filter1value}%' AND  
`{$filter2}` LIKE'%{$filter2value}%' AND
`{$filter3}` LIKE'%{$filter3value}%' AND
`{$filter4}` LIKE'%{$filter4value}% ;";

echo $sql; // this allows you to see the SQL you are making

$result=mysql_query($sql)or die('ERROR 238 - ' . mysql_error());

Now obviously when you want other people to use your code then you will need to comment out the echo line. However if you are getting SQL errors it helps to see the SQL. As I cannot see your SQL I am going to make a series of educated guesses and provide answers for each one.

Guess 1: Not setting all strings.

My gut reaction is that $fieldvalue is not set or is an empty string. If you don't see any content for one or more of the place holders and you see a pair of backticks or '%%' then the problem is that somewhere else in your code the string is not getting a value.

So for this solution check that you are building the string from your user input.

Guess 2: Form not supplying all the values

As you are dealing with user supplied values I hope that you have something nice and solid like this:

if(isset($_POST['fieldvalue']){
    $fieldvalue = mysql_real_escape_string(trim($_POST['fieldvalue']));
    if($fieldvalue == ''){
        $fieldvalue='SomeDefaulValue';
    }
}

For each and every value.

Personally that seems like too much work and I would use an array but that's me.

Assuming that you are not proving defaults (quite a common mistake) and the form is, for some reason, not giving you all the values you need this would also result in the same output as not setting the string.

Guess3: An easily overlooked string name difference

It is always worth checking that you did not set the value to $feildvalue and then call for $fieldvalue (notice the slight spelling difference.

It happens to us all (and another good argument is made for an array and some loops (less to type and get wrong).

Community
  • 1
  • 1
Matthew Brown aka Lord Matt
  • 2,298
  • 1
  • 27
  • 40
-1
<?php

$q = "SELECT * FROM $table WHERE 1=1";
if($field != "" or $fieldvalue != "" ){
    $q .= " and $field   LIKE'%$fieldvalue%'";
}

if($filter1 != "" or $filter1value  != "" ){
    $q .= " and $filter1 LIKE'%$filter1value%'";
}

if($filter2 != "" or $filter2value  != "" ){
    $q .= " and $filter2 LIKE'%$filter2value%'";
}

if($filter3 != "" or $filter3value  != "" ){
    $q .= " and $filter3 LIKE'%$filter3value%'";
}


if($filter4 != "" or $filter4value  != ""){
    $q .= " and $filter4 LIKE'%$filter4value%'";
}


$result=mysql_query($q)or die('ERROR 238 - ' . mysql_error());

?>
sanir ranta
  • 291
  • 2
  • 7