Trouble with using a php array in SQL WHERE clause

Question

I have a sql statement in my php page that looks like this:

 $places=array('CHI','DET','LA','NYC','DALLAS');

$SQL="SELECT NAME,
             ID,
             PHONE,
             EMAIL,
             LOCATION
      FROM SHOPPERS
      WHERE LOCATION IN '{$places}'
      AND ID BETWEEN '25687' AND '28050'
      ";

Then I'm echoing out the query as a table, but I'm not getting any results because there's a problem with the $places array. Any help would be appreciated. Thank you

Fluffeh · Accepted Answer · 2014-11-09 01:08:20Z

2

You need to format it to be friendly with what SQL expects:

$places=array('CHI','DET','LA','NYC','DALLAS');

$SQL="SELECT NAME,
             ID,
             PHONE,
             EMAIL,
             LOCATION
      FROM SHOPPERS
      WHERE LOCATION IN ('".implode("', '", $places)."')
      AND ID BETWEEN '25687' AND '28050'
      ";

SQL where column in clauses need brackets around them, so you need to add them in - and then you still need to implode the array into a string. The constructed SQL should look like this:

$SQL="SELECT NAME,
             ID,
             PHONE,
             EMAIL,
             LOCATION
      FROM SHOPPERS
      WHERE LOCATION IN ('CHI','DET','LA','NYC','DALLAS')
      AND ID BETWEEN '25687' AND '28050'
      ";

answered Nov 9, 2014 at 1:08

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

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

1 Comment

user3266259 Over a year ago

Thank you! I tried implode earlier, but I guess I didn't have the right apostrophes.

Collectives™ on Stack Overflow

Trouble with using a php array in SQL WHERE clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related