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

In model.php page:

function deleteFiles($field, $value)
    $query = 'DELETE FROM ' . $this->rquote($this->_table->getTableName()) . ' WHERE ' . $this->rquote($field) . ' = ' . $this->_db->Quote($value);
    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);
    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')


    $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? Feb 1, 2019 at 20:51
  • In model.php: Is $this->rquote() a valid call? I'm not familiar with that one. Feb 1, 2019 at 20:59

3 Answers 3


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, 2016 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, 2016 at 18:37
  • @indy - Try changing r.state to state in your query
    – Lodder
    Mar 5, 2016 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, 2016 at 20:02

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)
                    ->where("state IN ('FILES_ACCEPT','FILES_CANCEL')");
        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.


controller file

public function deletcheck() {
    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'";

ajax code

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

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

            type: 'post',
            url: "index.php?option=com_eq_misc&task=Dashboard.deletcheck",
            dataType: "html",
            data: {
                delchsk: val
            success: function (response) {
                // $("#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>
  • I cannot recommend this answer because it is vulnerable to sql injection attacks. Also, only one database connection needs to be connected -- the connection should not be instantiated inside the loop. Jun 1, 2022 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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