If I execute the following query in Workbench, I get back the expected number of rows — 5.
SELECT id, first_name, last_name, minutes, price, date, trans_id, user_id FROM user_orders WHERE user_id = 1234567 AND refunded = 1;
However if I enter the same user_id in the search field of my web app it returns ALL orders, whether or not it has been refunded, but still lists the total number of refunds as 5.
$searchRefunds = $this->db->query("SELECT id, first_name, last_name, minutes, price, date, trans_id, user_id FROM user_orders WHERE refunded = 1 AND id = '$searchTerm' OR trans_id = '$searchTerm' OR user_id = '$searchTerm' ORDER BY id desc;");
$total_records=$this->db->query("SELECT count(user_id) as count FROM user_orders WHERE refunded = 1 AND user_id = '$searchTerm';")->result();
$total_records = isset($total_records[0])?$total_records[0]->count:0;
Any help is appreciated.
Advertisement
Answer
Because you need to add all the or clauses inside parenthesis like this.
$searchRefunds = $this->db->query("SELECT id, first_name, last_name, minutes, price, date, trans_id, user_id FROM user_orders WHERE refunded = 1 AND (id = '$searchTerm' OR trans_id = '$searchTerm' OR user_id = '$searchTerm' ) ORDER BY id desc;");
$total_records=$this->db->query("SELECT count(user_id) as count FROM user_orders WHERE refunded = 1 AND user_id = '$searchTerm';")->result();
In your own implementation, you either need a row to be refunded with a specific id, or have a specific user id or a specific transaction id.
So the query returned all the user related rows, regardless of whether or not they were refunded.