Question:

How to run delete sql query with multiple values in Joomla?

Jaxson: 5 days ago

I would like to run a delete sql query with multiple values in it in my joomla site. But I am unable to do so. So I need your help on this.

In controller.php page:

function deleteFiles()
{
    JRequest::checkToken() or jexit('Invalid Token');
    $mainframe = &JFactory::getApplication();
    if ($this->_model->deleteFiles()) {
        $mainframe->enqueueMessage(JText::_('Files deleted successfully'),'message');
    } else {
        $mainframe->enqueueMessage(JText::_('Files not deleted'), 'error');
    }
    ARequest::redirectList($this->_controllerName);
}

In model.php page:

function deleteFiles($field, $value)
{
    $query = 'DELETE FROM ' . $this->rquote($this->_table->getTableName()) . ' WHERE ' . $this->rquote($field) . ' = ' . $this->_db->Quote($value);
    $this->_db->setQuery($query);
    return $this->_db->query();
}

In file.php page

function deleteFiles()
{
    $this->_db->setQuery('DELETE r 
            FROM #__booking_reservation AS r 
            WHERE r.state = ' . FILES_ACCEPT);
    $this->_db->query();
    return true;
}

And finally in the default.php page:

JToolBarHelper::custom('deleteFiles', 'delete.png', 'delete_2.png', 'Delete Files', true);

Now in file.php page, I want to add FILES_BOOKED, FILES_CANCEL, FILES_DELETE along with the present FILES_ACCEPT. But I don't know how to do it.

I have used something like the following:

WHERE r.state=' .FILES_ACCEPT. ' OR r.state=' .FILES_BOOKED. ' OR r.state=' .FILES_CANCEL. ' OR r.state=' .FILES_DELETE);

But then, all the record is got deleted under those value when I select one record from FILES_BOOKED and then click Delete files button, then it deleted all other files from FILES_ACCEPT or FILES_CANCEL etc.

So what should I need to do to delete one value when I select that particular value from listing record.

I have also tried following method:

function deleteFiles()
{
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);

    $conditions = array(
        $db->quoteName('r.state') . ' = ' . $db->quote('FILES_ACCEPT'), 
        $db->quoteName('r.state') . ' = ' . $db->quote('FILES_CANCEL')
    );

    $query->delete($db->quoteName('#__booking_reservation_items'));
    $query->where($conditions);

    $db->setQuery($query);
    $result = $db->execute();
}

But then it's giving me a 1054 error with following message:

Unknown column 'r.state' in 'where clause'
SQL=DELETE FROM `iyatx_booking_reservation_items` WHERE `r`.`state` = 'FILES_ACCEPT' AND `r`.`state` = 'FILES_CANCEL'

So can you tell me what to do now?

Answer:
Emma: 5 days ago

Just some side notes:

  • JRequest is deprecated. Please use JSession::checkToken() or die('Invalid Token';)
  • You can remove the & from &JFactory::getApplication()

As for your actual query, I'd suggest looking at the Joomla Documentation (https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase#Deleting_a_Record) and making full use of the Joomla API, which allows you to add conditions, for example:

$conditions = array(
    $db->quoteName('r.state') . ' = ' . $db->quote('something'), 
    $db->quoteName('r.state') . ' = ' . $db->quote('something else')
);