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

Question

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 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')
);

Answer 2

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.

Answer 3

controller file

public function deletcheck() {
    $delchsk=$_POST['delchsk'];
    foreach ($delchsk as $key => $value) {
        $db = JFactory::getDbo();
        $db1 = JFactory::getDbo();
        $query1 = "Delete from `table1` where name = '$value'";
        $query2 = "Delete from `table2` where symbol = '$value'";
        $db->setQuery($query1);
        $db1->setQuery($query2);
        $db->query(); 
        $db1->query(); 
    }
}

ajax code

$(function () {
    $("#btnDel").click(function(e) {

        //$('#adminForm').on('reset', function (e) {
        e.preventDefault();
        var checkboxes = $("input[name='delchk[]']:checked");
        var val = [];
        checkboxes.each(function(i){
            val[i] = $(this).val();
        });

        $.ajax({
            type: 'post',
            url: "index.php?option=com_eq_misc&task=Dashboard.deletcheck",
            dataType: "html",
            data: {
                delchsk: val
            },
            success: function (response) {
                alert('Deleted');
                location.reload();
                // $("#formdata").html(response).focus();   
            }
        });
    });
});

view file

<button type="button" id="btnDel"  class="btn btn-primary"> <i class="fa fa-trash color-down"></i>   Delete  </button>