why {if row exist then fetch them} does not work?

Question

I want to check {if row exist} first and then fetch the results. here is my code:

$id = 102;

// connecting to database here
$sth = $dbh->prepare('SELECT * FROM users WHERE id = ?');
       $sth->execute(array($id));
       $num_rows = $sth->fetchColumn();

  // if exist
  if($num_rows){

       // then fetch
       while($end = $sth->fetch())
       {
           echo $end['id'];
       }

  }

But the output is blank, Why ?

It should be noted that the row with id = 102 is exist in the database.

Possible duplicate of: http://stackoverflow.com/questions/4286586/best-way-to-check-if-mysql-results-returned-in-php — stephen, Aug 02 '15 at 10:48
@surfer190 I use of `PDO`, your link has used of `mysql` api. — Shafizadeh, Aug 02 '15 at 10:50

score 1 · Answer 1 · answered Aug 02 '15 at 11:07

1

As I understood, you have only one row with this ID. You can use fetch():

$id = 102;

// connecting to database here
$sth = $dbh->prepare('SELECT * FROM users WHERE id = ?');
$sth->execute(array($id));
$row = $sth->fetch();

// if record exist
if ($row) {
     var_dump($row);
     die();
}

answered Aug 02 '15 at 11:07

Danila Ganchar

7,271
11
35
59

your mean is should I check existing with `fetch()` instead of `fetchColumn()` ? – Shafizadeh Aug 02 '15 at 11:10
@Sajad thanks. If you have many rows with the same value you can use fetchAll(): $rows = $sth->fetchAll(); if (count($rows) > 0) { //here your code... } – Danila Ganchar Aug 02 '15 at 11:19

score 0 · Accepted Answer · answered Aug 02 '15 at 10:58

PDO also have similar method rowCount but that would return effected rows in some cases.

Quoting from PHP Manual

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

As suggested, you can query, count and proceed

$id = 102;

// connecting to database here
$sth = $dbh->prepare('SELECT COUNT(*) FROM users WHERE id = ?');
       $sth->execute(array($id));
       $num_rows = $sth->fetchColumn();

// if exist
if($num_rows > 0){

    $sth = $dbh->prepare('SELECT * FROM users WHERE id = ?');
  $sth->execute(array($id));

    // then fetch
    while($end = $sth->fetch())
    {
         echo $end['id'];
    }
}

@Sajad 2 queries for one select? ;))) And with COUNT()? I think it is not good way. — Danila Ganchar, Aug 02 '15 at 11:11

why {if row exist then fetch them} does not work?

2 Answers2