0

I'm busting my head trying to work this out.

"ContentBlock1":["2","22"]

I have been trying to get the 2 and the 22 into a comma sepertaed string so i can use it within a MySQL IN(2,22) query.

I currently have tried several ways but none seem to work for me.

$ContentBlock = my json data;

$cid = json_decode($ContentBlock,true);     

foreach ($cid as $key){ 

    $jsoncid = "$key ,";

}

And then:

SELECT * FROM content 
WHERE featured=1 AND state=1 AND catid IN($jsoncid) 
ORDER BY ordering ASC LIMIT 4");
Amal Murali
  • 75,622
  • 18
  • 128
  • 150
jonnypixel
  • 327
  • 5
  • 27
  • How exactly does your JSON string look like? Can you post the output of `var_dump($yourJSONString);`? – Amal Murali May 27 '14 at 03:21
  • Aha! ok cool. This is it - array(2) { [0]=> string(1) "2" [1]=> string(2) "22" } - thanks @Amal – jonnypixel May 27 '14 at 03:22
  • What did you try? ["2", "22"] is an array now right? Can't you just implode it with a comma separator? – Jorg May 27 '14 at 03:22
  • simple `implode()` will do just fine – user1978142 May 27 '14 at 03:23
  • @jonnypixel: Remove the `foreach` and just use `$jsoncid = implode(',', $theArrayYouJustVarDumpedAbove);`. – Amal Murali May 27 '14 at 03:24
  • @Amal so i still set vardump like somehting like this first? $theArrayYouJustVarDumpedAbove = var_dump($yourJSONString); – jonnypixel May 27 '14 at 03:28
  • @Amal it wont work for me? I have tried both with json true and not and also with vardump and just straight $cid and still not getting teh results – jonnypixel May 27 '14 at 03:32
  • @jonnypixel: You're confused. `var_dump()` is just for inspecting the contents of a variable and doesn't return a useful value. Can you please update your question with the **exact** code you're using? Make sure you include the JSON string **as it is**. – Amal Murali May 27 '14 at 03:38
  • `json_decode()` will be able to decode the JSON string into an array ONLY IF the JSON is valid. As you can verify with http://jsonlint.com, your current JSON is ***not*** valid. It's either that you made a C&P mistake while creating your question or the JSON string is *not* valid in the first place. Either way, make sure it *is* valid before attempting to decode it. (Use `var_dump()` to check the return values of `json_decode()`). – Amal Murali May 27 '14 at 03:41
  • Also make sure you have [enabled error reporting](http://stackoverflow.com/a/6575502/1438393). Otherwise, it would just return a whitepage without any useful information on what failed, where the error occurred etc. It's always a useful practice to enable error reporting on development environments. If you're doing this somewhere else (you shouldn't, without proper testing), you can turn off `display_errors` and then log them instead. – Amal Murali May 27 '14 at 03:43
  • @jonnypixel: If you find it difficult to grasp any of what I've said, consider reading through a basic PHP tutorial. To get started, you might try [CodeAcademy](http://www.codecademy.com/tracks/php). The [PH manual](http://php.net/) is a great resource as well – Amal Murali May 27 '14 at 03:44
  • @Amal i think if you add your initial answer in as an Answer i can tick it off as solved. Up to you. Thanks again - i have included my last response within my original question btw. – jonnypixel May 27 '14 at 04:03
  • @jonnypixel: I'm glad I could help. Just a note: please don't edit answers into the question. You can post an answer yourself and mark it as accepted (you need to wait some time for that), so future visitors can find it useful. – Amal Murali May 27 '14 at 04:07
  • @jonnypixel: Unrelated to the actual issue, but since you're learning anyway: **blindly injecting user input into your database query is a *very* Bad Idea**. You need to properly escape the values first — look into prepared statements. (Search on Google for tutorials. [This manual page](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) should get you started.) – Amal Murali May 27 '14 at 04:09

1 Answers1

0

you can do it like below

$c='{"ContentBlock1":["2","22"]}';

$cid = json_decode($c);  

// RecursiveIterator will search even sub arrays

$new = new RecursiveIteratorIterator(new RecursiveArrayIterator($cid));
foreach($new as $v) {
  $r[]= $v;
}

echo "SELECT * FROM content WHERE featured=1 AND state=1 AND catid IN('".implode("','",$jsoncid)."') ORDER BY ordering ASC LIMIT 4";

output

SELECT * FROM content WHERE featured=1 AND state=1 AND catid IN('2','22') ORDER BY ordering ASC LIMIT 4
ɹɐqʞɐ zoɹǝɟ
  • 4,342
  • 3
  • 22
  • 35
  • I'm not the downvoter, but what's the foreach doing in your answer? Just use `implode()`. There's no need for foreach here (and it wouldn't work because `$jsoncid` will only contain the value from the last iteration. You need to initialize it as an empty string (`$jsoncid = '';`) and then *append* it (**`$jsoncid .= $key;`**) instead of rewriting it. – Amal Murali May 27 '14 at 03:29
  • i didn't downvote, but I'm guessing it's because this only uses the last entry in the array, thanks to the `foreach`. – Jorg May 27 '14 at 03:29
  • And now try it with more than 1 entry... `{"ContentBlock1":["2","22"],"ContentBlock2":["1","11"]}` – Jorg May 27 '14 at 03:38
  • in this case the last values will be stored,but as what op is having as input mine will work – ɹɐqʞɐ zoɹǝɟ May 27 '14 at 03:47