CODEWITHSUNDEEP
phpsql
twan
twan

Reputation: 2659

Separate 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";

Upvotes: 3

Views: 275

Answers (6)

Manjeet Barnala
Manjeet Barnala

Reputation: 2995

You can do something like this:

$ids = ['1','2','3','4']; //array of id's
$newArr = array(); //empty array..
foreach($ids as $ids)
{
    $newArr[] = "'".$ids."'";  //push id into new array after adding single qoutes
}
$project = "SELECT * FROM `web_content` WHERE `catid` IN (".implode(',',$newArr).") AND state = 1"; /// implode new array with commaa.
echo $project;

This will give you :

SELECT * FROM `web_content` WHERE `catid` IN ('1','2','3','4') AND state = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 1

kalatabe
kalatabe

Reputation: 2989

If the column's type in the DB is integer you do not actually need to quote the values, but in case it isn't, you can use array_map to quote every item in the array, then implode to join them with commas:

<?php

$ids = [1, 2, 3, 4, 5];

$sql = 'SELECT * FROM mytable WHERE id IN (?)';

$in_clause = array_map(function ($key) {
    return "'$key'";
}, $ids);

$sql = str_replace('?', implode(',', $in_clause), $sql);

echo $sql;

Result:

SELECT * from mytable where id in ('1','2','3','4','5')

Upvotes: 1

Niklesh Raut
Niklesh Raut

Reputation: 34914

Alternatively you can use single join query, like this. 

SELECT con.* FROM `web_content` as con LEFT JOIN `web_categories` as cat 
ON con.catid=cat.id WHERE cat.parent_id=13 AND published = 1

Upvotes: 1

Iain
Iain

Reputation: 387

Have you tried to implode()?

Use ", " as glue. You will have to edit the string yourself to add a " at the beginning and end.

More info: http://php.net/manual/en/function.implode.php

Upvotes: 1

Sujan Shrestha
Sujan Shrestha

Reputation: 1040

Use implode().. 

<?php
    $a1 = array("1","2","3");
    $a2 = array("a");
    $a3 = array();

    echo "a1 is: '".implode("','",$a1)."'<br>";
    echo "a2 is: '".implode("','",$a2)."'<br>";
    echo "a3 is: '".implode("','",$a3)."'<br>";
?>
output->>>>>>>
a1 is: '1','2','3'
a2 is: 'a'
a3 is: ''

Upvotes: 1

Related Questions