CODEWITHSUNDEEP
mysql
BernardA
BernardA

Reputation: 1523

mysql IN Clause not accepting string variable

I have this odd problem with an IN clause

The variable $fuel comes from an Ajax call to this php page.

$fuel = $_REQUEST['fuel'];

var_dump($fuel) = 'gas' ( for instance)

Now, when $fuel is not selected in the php page that originates the Ajax call, I need to insert ALL possibles values for $fuel, with the following code:

 if($fuel=='none'){
    $fuel=array('gas','diesel','hybrid','electric');
    $fuel=implode(',',$fuel);
}

This is the query. It works fine when the value comes directly from the Ajax call, that is, when $fuel = 'gas', for instance.

But it gives me an empty set when $fuel is generated by my IF clause and is $fuel = 'gas,diesel,hybrid,electric'.

SELECT * FROM vprice_range WHERE (power >= $power)
            AND (price BETWEEN $low AND $high)
            AND (fuel IN ('$fuel'))
            AND (mileagemix < $mileage)
            AND (emission_co2 < $co2)
            AND (trunk >= $trunk)

This may be irrelevant, but note that the FIELD 'fuel' is a ENUM field ('gas','diesel','hybrid','electric')

As far as I know $fuel is formatted correctly as a string.

Please advise

Upvotes: 2

Views: 10426

Answers (3)

John Conde
John Conde

Reputation: 219834

@Gordon Linoff is correct. An alternative solution is to provide the missing single quotes:

$fuel=implode("','",$fuel);

Upvotes: 0

65Fbef05
65Fbef05

Reputation: 4522

Your issue is that the values for your IN clause need to be quoted like WHERE whatever IN ('one','two','three') and so forth. You can fix this with $fuel = "'" . implode("','", $fuel) . "'";. One note though, if you're selecting by all possible values, why not just leave AND fuel IN out of your query altogether for that case?

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269973

It is accepting the string variable. The problem is that the entire string is interpreted as one value, something like 'gas,diesel,. . .'. And that is not what you mean.

Instead, use find_in_set():

. . .
and find_in_set(fuel, $fuel) > 0)

Upvotes: 1

Related Questions