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?

+3


source to share


1 answer


$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 '

.

+2


source







All Articles