I have multiple ID's stored in a variable. This array can become long (+400 ID's). How do I fetch the data from these ID's in MySQL (PDO)?
I could do it via this:
"select * from info WHERE `id` IN ('1,2,3,4,5')"
But that's unsafe (SQL injection) and maybe not performant. Any suggestions to handle this problem?
$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
SELECT *
FROM galleries
WHERE id IN ($in);
SQL;
$statement = $pdo->prepare($select);
$statement->execute($ids);
take out single quote from the content '1,2,3,4,5' this checks the complete value under ID.
so use it without single quotes as the ID column is numeric, 1,2,3,4,5 this will separate each value.
I assume that id field is INTEGER so please remove the apostrophes and new query will be like,
select * from info WHERE `id` IN (1,2,3,4,5)
the other hand at PHP side for instance,
$idList = explode(',', $_GET['id_list']);
$idList = implode(',', array_unique(array_map('intval', $idList)));
$query = "select * from info WHERE `id` IN ($idList)";
and please check max allowed selector in IN
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_packet
Use
"select * from info WHERE `id` IN (1,2,3,4,5)"
Including single quotes forces whole value to be considered as single string.
If you have value in array then just write it as
$query = "select * from info WHERE `id` IN (implode(',', array_map('intval', json_decode('[' . $ids . ']', true))))" ;
implode().