mysqli - Delete method not deleting

Question

Got a delete method which is not working ... can anybody figure out why it's returning 0? The item is present in the table.

public function deleteItem($item_id)
{
    $userItemIDS = array();

    $userItemIDS = $this->helperClass->userItemIDS();

    if( !in_array($item_id, $userItemIDS) )
    {
        return false;
    }

    $q = $this->db->mysqli->prepare("DELETE i, s FROM items i
        LEFT JOIN save_list s
            ON i.id = s.item_id
        WHERE i.id = ? AND s.item_id = ?
        AND
            NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
        AND
            NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)");
    if($q)
    {
        $q->bind_param("iiii", $item_id, $item_id, $item_id, $item_id);
        $a_r = $q->affected_rows;
        $q->execute();
        $q->close();

        return $a_r;
    }
}

Either the query is not finding the rows, or your `prepare` is failing. You're not doing any error checking so you wouldn't know. — , Jul 15 '15 at 23:35
I just noticed that I didn't have `$q->execute();` but it still doesn't work. — Ciprian, Jul 15 '15 at 23:41
before you do `$q->close();`, put this and let me know if anything shows up: `print_r($q->error);`. Also, you should read [this] (http://stackoverflow.com/a/2553892/2518525) (*and be error handling like that*) for error reporting. — Darren, Jul 15 '15 at 23:44
Error checking: check the return value of every call to MySQL for `false`. If it __is__ false, look at the appropriate error variable to find out what went wrong __and report it__. Right now you check the value of `$q`, and execute the query if it's not false. There's no reporting here so you have no way to know if the query executed or not. — , Jul 15 '15 at 23:59
@ciprian yes, all of those checks otherwise you won't know where it failed.. — Darren, Jul 16 '15 at 00:00
Also, your query is most likely (*90%*) failing because of your query: `DELETE i, s FROM....`, the syntax is `DELETE FROM table`, **not** `DELETE column_1,column_2 FROM table`....which means your query is wrong. — Darren, Jul 16 '15 at 00:02
I keep finding queries built like this: http://stackoverflow.com/a/4839931/385623 — Ciprian, Jul 16 '15 at 04:02

score 0 · Accepted Answer · answered Jul 16 '15 at 12:46

The comments are very helpful and I appreciate them a lot. However my problem was in the query itself. Taking out AND s.item_id = ? deletes the entry from the items table and fromsave_list if present.

 $q = $this->db->mysqli->prepare("DELETE i, s FROM items i
    LEFT JOIN save_list s
        ON i.id = s.item_id
    WHERE i.id = ?
    AND
        NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
    AND
        NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)");

mysqli - Delete method not deleting

1 Answers1