How to check whether SELECT EXISTS returns a value or not?

Question

I am trying to quickly determine if a user_ID is the owner of a 'goal'. I believe my SQL query is good, but I'm trying to find a nice way of checking the result!

In this case, no matter what I put for $obj_id or $user_id, my function returns true. I assume it's because mysql_num_rows is counting even a false result as a row? So what PHP code should I use to check to see if the result exists or not?

Note that I want something short and elegant! I know I could do it the long way (check count(*), return mysql_assoc then check the count value...) but that is long winded and ugly.

Any ideas? Thanks!

$query = "SELECT EXISTS (SELECT * FROM goals WHERE goal_ID='$obj_id' AND user_ID='$user_id')";
if (@mysql_num_rows(mysql_query($query))!=1) {
    return false;
} else {
    return true;
}

Using `count(*)` should be preferred because it returns just one row instead of potentially millions. — Arjan, Oct 09 '13 at 15:55

gbn · Accepted Answer · 2011-11-20T09:32:05.260

43

Don't bother with EXISTS. The in-line exists will always give one row containing "true" or "false".

You're looking for either "zero rows" or "at least one row" so change the query to something like this and then check how many rows are returned

SELECT 1 FROM goals WHERE goal_ID='$obj_id' AND user_ID='$user_id' LIMIT 1

edited Nov 20 '11 at 09:32

answered Dec 19 '10 at 20:45

gbn

422,506
82
585
676

Should I use "SELECT 1"? What is this 1 here? – AA Shakil May 02 '18 at 15:02
So how should I extract that "true or false" from EXISTS? – Jack Dec 17 '18 at 11:55

score 10 · Answer 2 · answered Dec 19 '10 at 20:54

10

I like gbn's answer the best, but I wanted to point out that this:

if (@mysql_num_rows(mysql_query($query))!=1) {
     return false;
} else {
     return true;
}

can be simplified to:

return @mysql_num_rows(mysql_query($query)) == 1;

answered Dec 19 '10 at 20:54

Brad Mace

27,194
17
102
148

2

I have some other stuff in my "if / else" sections in the real page, so I can't simplify quite that much, but I didn't realize you could just add the '==' like that. Thanks, that's useful! – supermitch Dec 19 '10 at 20:59

score 2 · Answer 3 · answered Jul 08 '13 at 14:54

2

This way is probably faster.

$query = "SELECT EXISTS (SELECT * FROM goals WHERE goal_ID='$obj_id' AND user_ID='$user_id')";

if(mysql_num_rows(mysqli_query($query)) < 1) {
   // Nothing found!
}

answered Jul 08 '13 at 14:54

Bas

25
1
1
9

This is indeed, faster. http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table – Edward Dec 05 '16 at 18:43

score 2 · Answer 4 · edited Aug 24 '14 at 21:46

2

Counting how many rows match the criteria should be easier:

$sql = SELECT COUNT(*) FROM goals WHERE goal_ID='$obj_id' AND user_ID='$user_id'
$query = mysql_query($sql);
$result = mysql_fetch_row($query);

return $result[0] >= 1;

edited Aug 24 '14 at 21:46

Jacek Laskowski

72,696
27
242
420

answered Dec 19 '10 at 20:45

Esteban

874
8
13

score 1 · Answer 5 · answered Dec 04 '19 at 17:57

SELECT EXISTS always returns a row! The following code uses the fastest MySql query(according to this answer: https://stackoverflow.com/a/10688065/3710053) and gives in PHP the correct result:

$link = mysqli_connect($DB_SERV, $DB_USER, $DB_PASS, $DB_NAME);
$query = "SELECT EXISTS (SELECT * FROM goals 
                      WHERE goal_ID='$obj_id' 
                        AND user_ID='$user_id' 
                      LIMIT 1) 
      as `row_exists`";

if(mysqli_fetch_assoc(mysqli_query($link,$query))['row_exists'] ===0) {
   // Nothing found!
}

score 1 · Answer 6 · edited Mar 05 '21 at 10:47

1

Really working config for MySQL:

$sql="SELECT EXISTS (Select * FROM $dbname.$dbrel WHERE Index_r=$idx AND ... LIMIT 
1)";    
$r1=mysqli_fetch_row(mysqli_query($conn, $sql));
if (current($r1) == 0) {when no recoeds} else {when exist records }

edited Mar 05 '21 at 10:47

ekochergin

4,109
2
12
19

answered Mar 05 '21 at 08:40

Sergey Shramuk

11
2

score 0 · Answer 7 · answered Sep 03 '14 at 10:48

0

mysql_result(mysql_query("SELECT EXISTS (SELECT * FROM goals WHERE goal_ID='$obj_id' AND user_ID='$user_id')"),0);

answered Sep 03 '14 at 10:48

levent

9
1

score 0 · Answer 8 · answered Jan 14 '11 at 15:49

0

what about this:

$query = "SELECT EXISTS (SELECT * FROM goals WHERE goal_ID='$obj_id' AND user_ID='$user_id')";

return  mysql_query($query) ? false : true;

answered Jan 14 '11 at 15:49

tareco

1,121
7
4

How to check whether SELECT EXISTS returns a value or not?

8 Answers8

Linked