我得到了一个id数组,我想在一个IN语句(sql)中使用。然而,这只能在正确书写时完成,例如:IN ('12', '13', '14')
如何将id数组更改为该格式?这意味着在每个数字周围添加引号,并且在每个数字后面用引号包围逗号,数组中的最后一个数字除外。
我代码:$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']."";
}
}
我以后想这样使用它:
$project = "SELECT * FROM `web_content` WHERE `catid` IN ('".$parentoverzicht."') AND state = 1";
将此作为单个查询执行!SQL引擎对处理表有各种各样的优化,在代码中进行循环通常会更昂贵。
最明显的查询是:
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;
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: ''
你试过内爆()吗?
使用", "
作为胶水。您必须自己编辑字符串,以便在开头和结尾添加"
。
更多信息:http://php.net/manual/en/function.implode.php
您也可以使用单连接查询,如下所示。
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
如果列在DB中的类型是整数,您实际上不需要引用值,但如果不是,您可以使用array_map来引用数组中的每个项目,然后用逗号将它们连接起来:
<?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;
结果: SELECT * from mytable where id in ('1','2','3','4','5')
你可以这样做:
$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;
这将给你:
SELECT * FROM `web_content` WHERE `catid` IN ('1','2','3','4') AND state = 1