check existance of record in table row

Question

I am trying to test whether record for particular condition exist or not.

$con = new mysqli('127.0.0.1', 'root', '', 'mysql');            

        if ($con->query("select count(*) from userpost  where userid = 1000004") != null) 
        {
            echo "exist";
        }
        else        
        { 
            echo "not exist";
        }

is this correct way?

or let me know if anything better

`IF EXISTS`; http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists — Alex K., Dec 20 '13 at 12:22
@AlexK.: I already saw it. but it could not work for me. it directly checks `SELECT IF( EXISTS( SELECT * FROM gdata_calendars WHERE `group` = ? AND id = ?), 1, 0)` without showing connection parameter. — user3121782, Dec 20 '13 at 12:26

score 2 · Answer 1 · answered Dec 20 '13 at 12:25

2

try this

if ($con->query("select user_id from userpost  where userid = 1000004 LIMIT 1 ") != null) 
        {
            echo "exist";
        }
        else        
        { 
            echo "not exist";
        }

answered Dec 20 '13 at 12:25

Satish Sharma

9,547
6
29
51

thanks, but always shows `exist` even if userid does not exist – user3121782 Dec 20 '13 at 12:29

score 1 · Answer 2 · answered Dec 20 '13 at 12:20

1

I think this is the best way assuming that userid is the primary key. You can't get much better performance than a primary key in the where clause (assuming no joins, unions, etc exist).

answered Dec 20 '13 at 12:20

Kevin Bowersox

93,289
19
159
189

userid is not primary key. And above code echo `exist` even of no such id present – user3121782 Dec 20 '13 at 12:23
Lets tag this with php to draw some extra attention – Kevin Bowersox Dec 20 '13 at 12:26

KeNJiKunG · Answer 3 · 2013-12-20T13:31:26.023

$con->query will never equals null. as said in php manual. link

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

Use fetch_row to get row from query result. link

$con = new mysqli('127.0.0.1', 'root', '', 'mysql');            

$result = $con->query("select exists( select * from userpost where userid = 1000004 )");

if ( $result !== FALSE )
{
    $row = $result->fetch_row();
    if( $row[0] != 0 )
    {
        echo 'exists';
    }
    else
    {
        echo 'not exist';
    }
}
else
{
    echo 'error';
}

$result->close();

check existance of record in table row

3 Answers3