Explode/Implode List for Mysql WHERE IN ()

Question

I am really stuck with this one.

I am trying to pass a list of codes to a MySql statement, but not in the right format for it to work.

My list comes from a form textarea called "uniquecode"

Example codes (all on a new line) from the text area would be:

fg3456 tg7844 de3902 .. etc

I attempt to explode and implode the list with:

$ar = $_POST['uniquecode']
$in = "'".implode("','", explode("\n", $ar))."'";

The result of $in then gets passed to a Mysql statment:

$sql = "SELECT code, size, price from stock WHERE code IN ($in)";

When I echo the $sql, I get the following:

SELECT housecode, name, cost, size, price FROM stock where code IN ('fg3456 ','tg7844 ', ','de3902')

As you can see only the LAST item, 'de3902' is formatted correctly. Notice the first item in the list has a space then a single quote then a comma: 'fg3456 ',

Because of this issue, when I attempt to loop through the result set from the query, only the last item is returned:

$result = $mysqli->query($sql); 
if ($result = mysqli_query($mysqli, $sql)) {
        while($row = $result->fetch_array()) {
            $code = $row['code'];
            $name = $row['name'];
            $size = $row['size'];
            $price = $row['price'];
        }   

 echo $code; //returns last item in the list: de3902

So my question is how to explode/implode the list into the correct format for the IN clause of the Mysql query so it reads correctly and returns all the codes???

SELECT code, name, size, price from stock WHERE code IN ('fg3456','tg7844','de3902')

Kind regards

The correct way (in my opinion...) would be to loop over your values and add a question mark for every value and then bind the values to these placeholders after you have prepared the statement. Right now you have an sql injection problem. — jeroen
– jeroen, Commented Dec 18, 2014 at 14:01
Explode in a separate var, do a trim and add them to the implode only if trimmed lenght > 0 — Marco Mura
– Marco Mura, Commented Dec 18, 2014 at 14:02
Please post the output from var_dump($_POST['uniquecode']) as copied from the browser page source, not as rendered on screen. You have something spurious in there (not an extra line break, I think) that is breaking your implode(), maybe correctable with arary_filter(). But steps need to be taken to protect this from SQL injection. — Michael Berkowski
– Michael Berkowski, Commented Dec 18, 2014 at 14:02
@Hexana The string shown only has 20 characters so perhaps you should use preg_split() instead to explode on multiple spaces or new-line characters as well. — jeroen
– jeroen, Commented Dec 18, 2014 at 14:09

jeroen · Accepted Answer · 2014-12-18 15:18:23Z

1

As mentioned in the comments, I would loop over your values and add a question mark for every value and then bind the values to these placeholders after you have prepared the statement. Right now you have an sql injection problem.

However, to make sure you get the string you want with the input you seem to be receiving, you could use preg_split() to explode on multiple spaces, tabs or new-line characters.

For example:

$st = trim(" \n fg3456   tg7844  \n  \n de3902  \n ");
$ar = preg_split('#\s+#', $st);
var_dump($ar);
$in = "'".implode("','", $ar)."'";
var_dump($in);

Result:

array(3) {
  [0]=>
  string(6) "fg3456"
  [1]=>
  string(6) "tg7844"
  [2]=>
  string(6) "de3902"
}
string(26) "'fg3456','tg7844','de3902'"

You can use the first part of this to prepare a statement:

$st = trim(" \n fg3456   tg7844  \n  \n de3902  \n ");
$ar = preg_split('#\s+#', $st);
$in = trim(str_repeat('?,', count($ar)), ',');
$sql = "SELECT code, size, price from stock WHERE code IN ($in)";
var_dump($sql);

Result:

string(57) "SELECT code, size, price from stock WHERE code IN (?,?,?)"

By the way, personally I would use PDO as that would make the binding a lot easier.

edited Dec 18, 2014 at 15:18

answered Dec 18, 2014 at 14:54

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

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

1 Comment

Hexana Over a year ago

BINGO !!! Smart Cookie. This line did the trick $ar = preg_split('#\s+#', $st); Thank you so much. I do however need to sort the injection problem though :)

Dan · Accepted Answer · 2014-12-18 15:54:29Z

1

Why are you exploding on \n it seems like your should explode on a space (' '). Otherwise, the implode looks fine.

Alternately you could do a preg_replace to replace any number of spaces with (quote comma quote). $in = "'" . preg_replace('/\s+/',"','", $ar) . "'";.

edited Dec 18, 2014 at 15:54

answered Dec 18, 2014 at 14:04

Dan

11.2k5 gold badges26 silver badges36 bronze badges

2 Comments

Hexana Over a year ago

Have tried: $ar = "'" . preg_replace('/\s+/,"','",' $ar) . "'" ; but gives me a parse error !!

Dan Over a year ago

It should be $in=..., I'll edit. Oh, and I missed a quote around the regex.

Collectives™ on Stack Overflow

Explode/Implode List for Mysql WHERE IN ()

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related