mysql IN Clause not accepting string variable

Question

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

you should echo the text of your $sql query. This is a standard debugging practice(to inspect things, instead of assuming the value is what you think). — goat
– goat, Commented Jun 16, 2013 at 18:21
^^ Exactly what @chris said. Echo your query. type it into mysql console. look at the error. no need to play guessing games. — d-_-b
– d-_-b, Commented Jun 16, 2013 at 18:23
Thanks guys. The query works fine when I try it in phpmyadmin, replacing the variables with the corresponding values. So, the problem is not the query itself because if you read my question carefully I say that it works fine when $fuel comes from the Ajax call. So, I am not guessing here. — BernardA
– BernardA, Commented Jun 16, 2013 at 18:27

65Fbef05 · Accepted Answer · 2013-06-16 18:25:28Z

6

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?

answered Jun 16, 2013 at 18:25

65Fbef05

4,5221 gold badge24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BernardA Over a year ago

Thanks 65Fbef05. Just to your point about leaving the portion out of the query. The reason is that this is for filtering vehicles according to specs. If the user chooses the criteria than it is filtered otherwise ALL options for that specific items are let in.

65Fbef05 Over a year ago

I understand what you're saying, but by leaving that portion of your query out, you'll be querying by all possible options for that column.

BernardA Over a year ago

Correct, but then I would need to make one query for each possibility of criteria chosen by the user, with several IF statements. This way I just have one query, no matter what the user chooses to filter. Note, that I did not include all the other items that comes from the Ajax call, as they are not relevant to the point.

BernardA Over a year ago

Just to illustrate my point. In the case of $power, for instance, either the user chooses a power (say 100hp) or I set it to zero so that in the query $power >=0 and ALL items will be included in the query without filtering for power.

Gordon Linoff · Accepted Answer · 2013-06-16 18:22:01Z

1

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)

answered Jun 16, 2013 at 18:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

BernardA Over a year ago

Thanks Gordon. I have this working with the solution from 65Fbef05 and from John Conde, but did not manage with find_in_set as follows: AND (find_in_set(fuel,$fuel))....Am I missing something?

Gordon Linoff Over a year ago

@BernardA . . . enum works with find_in_set(). Perhaps the names are subtly different in $fuel and fuel?

John Conde · Accepted Answer · 2013-06-16 18:25:58Z

0

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

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

answered Jun 16, 2013 at 18:25

John Conde

220k100 gold badges464 silver badges504 bronze badges

Collectives™ on Stack Overflow

mysql IN Clause not accepting string variable

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related