twan twan - 3 months ago 25
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

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:

SELECT wc.*
FROM web_content wc
WHERE wc.catid IN (SELECT cat.id
                   FROM web_categories cat
                   WHERE cat.parent_id = 13 AND cat.published = 1
                  ) AND
      wc.state = 1;