How to use MySQL IN with Joomla Query - Syntax Issue
I have a variable named $del_ids
that uses a function array_keys()
:
$del_ids = implode("','", array_keys($_POST['gallery']));
$del_ids
outputs an array that looks like this:
Array
(
[2] => on
[4] => on
)
And I need to delete any rows that have an id that matches the IN array. If I use procedural PHP it works great:
$query = "DELETE FROM studio_sessions WHERE id IN('$del_ids')";
$result = mysqli_query($connection, $query);
When I convert this to Joomla query, I cannot get the IN syntax correctly. For example:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$conditions = array(
$db->quoteName('id' . ' IN ' . $del_ids)
);
$query->delete($db->quoteName('#__studio_sessions'));
$query->where($conditions);
$db->setQuery($query);
$result = $db->execute();
I am getting this error:
1054 Unknown column 'id IN 2','4' in 'where clause' SQL=DELETE FROM `vsem6_studio_sessions` WHERE `id IN 2','4`
It looks like I'm missing single quotes, maybe around id and 2, but I can't seem to get the syntax right - can anyone spot the problem?
source to share
$db->quoteName('id' . ' IN ' . $del_ids)
Wrong. You treat the whole long strng like a name, so it thinks that 'id IN (' 2 ',' 4 ')' is the column name.
Try
$db->quoteName('id') . ' IN (' . $del_ids .')'
Also your initial hack should be
$del_ids = implode(',', array_keys($_POST['gallery']));
Otherwise, you will get ','
as a delimiter instead of '
.
source to share