0

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?

  • I find your use of all caps strings to be ambiguous. They look like and are in some instances used like php CONSTANTS. Can you clarify if FILES_ACCEPT is literally a string value in your database table? Or does FILES_ACCEPT represent another string in the context of your project? – mickmackusa Feb 1 at 20:51
  • In model.php: Is $this->rquote() a valid call? I'm not familiar with that one. – mickmackusa Feb 1 at 20:59
1

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 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')
);
  • can you elaborate it a bit more? – indy Mar 5 '16 at 18:12
  • I have applied your method and it's giving me 1054 error. Maybe my execution is wrong. So need your kind help on this. – indy Mar 5 '16 at 18:37
  • @indy - Try changing r.state to state in your query – Lodder Mar 5 '16 at 19:16
  • Giving this message now: Unknown column 'state' in 'where clause' SQL=DELETE FROM iyatx_booking_reservation_items WHERE state = 'FILES_ACCEPT' AND state = 'FILES_CANCEL' – indy Mar 5 '16 at 20:02
1

But then it's giving me 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'

Here is why you are getting this error: r is the "table alias" that you are referencing while targeting the state column, but you never declare r as the table alias of iyatx_booking_reservation_items.

The truth is, because you are only referencing one table in your DELETE query, you don't need a table alias nor do you need to reference the table to target your column.

It can be as simple as removing the r. from the column names. *And state can only be one value at a time (per row).

DELETE FROM iyatx_booking_reservation_items WHERE state = 'FILES_ACCEPT' OR state = 'FILES_CANCEL'

or to condense your WHERE clause you can use...

DELETE FROM iyatx_booking_reservation_items WHERE state IN ('FILES_ACCEPT','FILES_CANCEL')

To crystallize, you can use this snippet to generate my recommended/2nd query:

function deleteFiles() {
    $db = JFactory::getDBO();
    try {
        $query = $db->getQuery(true)
                    ->delete("#__booking_reservation_items")
                    ->where("state IN ('FILES_ACCEPT','FILES_CANCEL')");
        $db->setQuery($query);
        $db->execute();
        if (!$affrows = $db->getAffectedRows()) {
            return "Possible Logic Error - No Qualifying Rows To Delete";
        }
        return "Success $affrows Deleted Rows";
    } catch (Exception $e) {
        return "Syntax Error"; // . "Query: " . $query->dump() . " & Error: " . $e->getMessage();
    }

Of course, you can return whatever you like from your function. I am offering some informative strings, but you might like true|false -- the choice is yours.

If MySQL is still banging on about state being an "unknown column", then clearly state does not exist in the iyatx_booking_reservation_items table; in which case you should check your spelling and/or table structure.

  • @indy is this answer "acceptable"? All questions should progress to some sort of resolution because abandoned pages are less valuable to researchers. – mickmackusa Feb 1 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.