I'm trying to pull specific rows from a mysql table, but am running into problems using WHERE to select the records. This presents two questions, one relating to the syntax I need to use, and the second more basically about strategy for pulling data from a table.

I want to use a SELECT statement that only pulls records matching some user input I stored in a variable. (I feel pretty certain I'm using an inefficient method here, but that's another story). I built a variable $x in this form: 'a' or 'b' or 'c'. Trying to incorporate that in a query I tried this:

$sql = "SELECT field1, field2, field3
FROM mytable
WHERE field4 = $x";

That returns a DB error that halts the script from the start: CMS Warning ... While executing query "SELECT field1, field2, field3 FROM mytable WHERE field4 = " the following error occured: You have an error in your SQL syntax;

So then I did some searching and found an example where a variable is used to insert values into a table, where the variable looks like this: ' " . $x . " '

Using that, I tried this query:

$sql = "SELECT field1, field2, field3
FROM mytable
WHERE field4 = ' " . $x . " ' ";

The script does not error out immediately, but will run until it tries to execute the query: CMS Warning ... While executing query "SELECT field1, field2, field3 FROM mytable WHERE field4 = ' 'a' or 'b' or 'c' ' " the following error occured: You have an error in your SQL syntax;

In theory the query would execute if there were not an extra set of single quotes around the values from my variable. But if I remove the single quotes from this expression ( ' " . $x . " ') then I get the first error shown above.

So the first question would be how to use a php variable in the WHERE segment of a SQL query. But maybe that's not the best way to do what I'm trying to do.

So, the second question is... should I be filtering for the three specific values after pulling ALL the rows in the table?

As a bonus question :rolleyes: ... I'm building my variable $x from an array. Can I just filter the query or query results based on the array contents directly, rather than creating a variable with "or" statements between the values?

Apologies if this is all really obvious... I'm learning by trial and error but the inconsistent results I'm getting are really confusing. :queasy:

    When looking for multiple "OR" matches, you need to use either of these two methods:

    $sql = "SELECT field1, field2, field3
FROM mytable
WHERE field4 = '$x' OR field4 = '$y' OR field4 = '$z'";
//
// ...or...
//
$sql = "SELECT field1, field2, field3
FROM mytable
WHERE field4 = IN('$x', '$y', '$z')";

      Thanks again for your help, NogDog! I found the problem with my variable... the script was evaluating the SQL expression while the variable was empty. Hence the error showing two single quotes at the end of my where statement.

      You anticipated my syntax problems, though, and I have the query working with the first option you provided in your reply. The second option, while I seemed to get my variable properly populated with values separated by commas for the IN() clause, would not execute, and produced a SQL error showing what looked like a perfectly good query. 😕 Not sure why, but it's irrelevant since the field='x' or field='y' or field='z' structure is working.

      Thank you! 🙂

        Write a Reply...