twan twan - 11 months ago 68
SQL Question

Seperate ids by commas and quotes

I got an array of ids that I want to use inside an IN statement (sql). However this can only be done when it is written correctly, for example: IN ('12', '13', '14')

How can I change an array of ids into that format? This means adding quotes around every number, and after every number surrounded by quotes a comma, except for the last one in the array.

My code:

$parent = "SELECT * FROM `web_categories` WHERE `parent_id` = 13 AND published = 1";
$parentcon = $conn->query($parent);
$parentcr = array();
while ($parentcr[] = $parentcon->fetch_array());

foreach($parentcr as $parentid){
if($parentid['id'] != ''){
$parentoverzicht .= "".$parentid['id']."";

I later want to use it like this:

$project = "SELECT * FROM `web_content` WHERE `catid` IN ('".$parentoverzicht."') AND state = 1";

Answer Source

Do this as a single query! SQL engines have all sorts of optimizations for working with tables, and doing the looping in your code is usually way more expensive.

The obvious query for your purposes would be:

FROM web_content wc
                   FROM web_categories cat
                   WHERE cat.parent_id = 13 AND cat.published = 1
                  ) AND
      wc.state = 1;