Testing a SQL Query for True or False
mysql, php, sql
Solution
Use `EXISTS`:
SELECT EXISTS (SELECT 1 FROM users WHERE onduty = 1 AND loc_id = '{$site}') AS test;
Your original query would return "no row" if no row is found that matches the criteria. This one returns `TRUE` (`1`) or `FALSE` (`0`) every time.
In cases where there can be multiple rows matching the criteria and you are only interested whether at least one rows exists, performance of `EXISTS` is superior to a plain query. It can stop as soon as the first row is found and only returns `0` or `1`.
db<>fiddle here Old sqlfiddle
Problem
``` $sql = "SELECT # FROM users WHERE onduty = 1 AND loc_id = '{$site}';"; $result = mysql_query($sql); ``` I simply want to test if this is true or false. If it returns 0 rows, I want next line to be something like: ``` if (!$result) { //do this; } ``` However, in my test, I am getting false when I know it should be true. Is this sound logic here? (note, yes I know I should be using `mysqli_query`, that is not what I am asking here)** ANSWER: This is what I used: ``` $login_state = false; if(mysql_num_rows(mysql_query("SELECT 1 FROM users WHERE onduty = 1 AND loc_id = '{$site}';"))) { $login_state = true; } ```