Convert String containing commas to array

Question

I am trying to convert a String

$string = "'1', '2', '3'";`

to an array

$array = array($string);

By doing so it gives me an error when trying to fetch data on MySQL

SELECT * FROM name WHERE id NOT IN ( '" . implode( "', '" , $array) . "' ) LIMIT 10

However, if I manually set the array as $array = array('1', '2', '3') it doesn't give an error when fetching a data, is there a way to convert the string to the array so the fetching doesn't give out an error? Because what I am trying to do is some data will be going to be passed to this file, where it will be fetched as a String, but later want to convert it to an array. I also tried removing the quotation mark from the String and it still gives the same error str_replace('"', "", $string); using this inside of the array.

would it not just be `not in (str_replace("'", "", $string))` — Nathan_Sav, May 09 '22 at 16:02
Why not just `SELECT * FROM name WHERE id NOT IN ( ' . $string . ' ) LIMIT 10`? — Guido Faecke, May 09 '22 at 16:02
Or using prepared statements - https://stackoverflow.com/questions/907806/passing-an-array-to-a-query-using-a-where-clause — Nigel Ren, May 09 '22 at 16:06
@Nathan_Sav since I am using php this method conflicts with all the quotation marks and php gives out an error — Skoonda, May 09 '22 at 16:07
@GuidoFaecke this way for some reason it doesn't fetch any data — Skoonda, May 09 '22 at 16:07
If you do `$array = array($string);` you get an array with that string as its only member. Use e.g. [explode](https://www.php.net/explode) or [preg_split](https://www.php.net/preg_split). — Markus AO, May 09 '22 at 19:19

RiggsFolly · Accepted Answer · 2022-05-09T16:41:52.467

1

if you remove the quotes around the id's, assuming the id column is a integer column

$string = "'1', '2', '3'"; 
$string = str_replace("'", '', $string);

$sql = "SELECT * FROM name WHERE id NOT IN ( " . implode(",", explode(',',$string)) . ") LIMIT 10 ";
echo $sql;

RESULT

SELECT * FROM name WHERE id NOT IN ( 1, 2, 3) LIMIT 10

Of course a simpler way would be to simply do

$string = "'1', '2', '3'"; 
$string = str_replace("'", '', $string);
$sql = "SELECT * FROM name WHERE id NOT IN ( $string ) LIMIT 10 ";

edited May 09 '22 at 16:41

answered May 09 '22 at 16:07

RiggsFolly

93,638
21
103
149

I don't think so ;) -- `implode` without a second argument results an empty separator, so `echo implode([1,2,3]);` results in `123` (**NOT** `1,2,3`). But since `$string` does not contain the separator you are using in your explode (`','`, but string contains `', '`), the original version of `$string` is used in the query. – boppy May 09 '22 at 16:24
@boppy I am actually still learning sql and php, your point may be true and I will definitely keep on looking into it, but this answer did get the job done and I am able to fetch the data I am intending to. But thanks for your answer! I will also look into your answer and hopefully learn more about it. – Skoonda May 09 '22 at 16:28
@RiggsFolley: No, it produces the desired output by accident! Check: `explode("','",$string)` results in an array containing ONE Element. Joining it will produce the original string. Even exploding by `','` CANNOT work, because it will not catch the `'`s at the beginning and end of the input. – boppy May 09 '22 at 16:34
@boppy Yea you are right, amended – RiggsFolly May 09 '22 at 16:36
It works now, but explode+implode with the same separator will always result in the final string being exactly the input string... – boppy May 09 '22 at 16:38
Oh lord, I must need more caffine :) A simple amendment I hope will clear this up – RiggsFolly May 09 '22 at 16:42
1

Even `NOT IN ( '1', '2', '3' )` would work, which is what you get if the original `$string` is left untouched and just slapped in, ie. `NOT IN ( $string )` without any of the hoopla. MySQL simply converts the queried string-numbers to integers, assuming integer column, and then compares. – Markus AO May 09 '22 at 19:28

Convert String containing commas to array

1 Answers1