Why do these two SQL queries return a different number of rows?

Question

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.

If you're going to test a query in workbench, make sure it's _identical_ to the one you have in your code, which this isn't. — M. Eriksson, Jan 07 '22 at 22:47
Side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Jan 08 '22 at 00:14

score 1 · Accepted Answer · answered Jan 07 '22 at 22:11

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.

Why do these two SQL queries return a different number of rows?

1 Answers1