Php PDO wrong row count

Question

Can someone explain me when i echo $rows_affected variable, why i only get 1 when I should be getting 2 based on my db records? More than half an hour trying to figure out this and still cant solve it.. Still noob on PDO.

Any other advices are welcome.

$sql1 = "SELECT title FROM page WHERE id=?";
        $stm = $db->prepare($sql1);
        $stm->execute(array($id));
        $row1 = $stm->fetch(PDO::FETCH_ASSOC);

        $sql2 = "SELECT id,title,pg_title FROM page WHERE title=? ORDER BY id ASC";
        $stm = $db->prepare($sql2);
        $stm->execute(array($row1['title']));

        $rows_affected = $stm->fetchColumn();

        if (count($rows_affected) > 0) {
            for($i=0; $i <= $rows_affected; $i++) {

                $row2 = $stm->fetch(PDO::FETCH_ASSOC);

                if($row2['id']==$id){
                    echo '<a href="javascript: void(0)"><b>'.$row2['pg_title'].'</b></a>&nbsp;&nbsp;';
                }else{
                    echo '<a href="page.php?id='.$row2['id'].'';?><?php if(isset($_GET['edit']) && $_GET['edit']==1){ echo '&edit=1';}?><?php echo'">'.$row2['pg_title'].'</a>&nbsp;&nbsp;';

                }
            }
        }

That `fetchColumn()` will get you the value of the `id` column from the first result of the second query, it has nothing to do with the number of records found. Did you mean to do `rowCount()`? Note that the `rowCount()` approach will only work with MySQL. Also note that `$rows_affected` will be a scalar value either way and therefore `count()` makes no sense in conjunction with it. — DaveRandom, Apr 17 '13 at 22:42
Thanks,that worked at first! Stupid mistake.. When you say that rowCount() will only work with mysql you mean it only works if im coding in mysql? P.s: My english isnt very good sorry — miguelfsf, Apr 17 '13 at 22:55
@miguelfsf Yes, [`rowCount()`](http://php.net/manual/en/pdostatement.rowcount.php) is not guaranteed to work as expected with `SELECT` statements for all database drivers. As far as I am aware, MySQL is the only natively supported driver for which it *is* guaranteed to work. See the linked manual page for more (although not much more) information. Also http://stackoverflow.com/a/15888467/889949 — DaveRandom, Apr 17 '13 at 23:11

score 0 · Answer 1 · answered Apr 17 '13 at 22:50

fetchColumn():

Returns a single column from the next row of a result set or FALSE if there are no more rows.

I think you want to use fetchAll(). Use PDO::FETCH_COLUMN as the $fetch_style if you only want the first column. E.g.

$rows_affected = $stm->fetchAll(PDO::FETCH_COLUMN, 0);

score 0 · Answer 2 · answered Apr 17 '13 at 22:50

0

if you're using MySql you can use rowCount method.

Or better iterate directly:

$stm->execute(array($row1['title']));
$stm->setFetchMode(\PDO::FETCH_ASSOC);
foreach ($stm as $row2) {
    if($row2['id']==$id){
        // do your stuff
    }
}

answered Apr 17 '13 at 22:50

bitWorking

12,485
1
32
38

score 0 · Answer 3 · answered Apr 18 '13 at 20:57

Some databases may return the number of rows returned by a SELECT statement. However, this behaviour is not guaranteed for all databases and should not be relied on .Manual You can use COUNT(*) and fetchColumn() as in following query to emulate rowCount().

    $sql2 = "SELECT COUNT(*) FROM page WHERE title=?";
    $stm = $db->prepare($sql2);
    $stm->execute(array($row1['title']));

// Check the number of rows that match the SELECT statement 
if($sql2->fetchColumn() == 0) {
    echo "No records found";//For Testing
 }else{
     $sql3 = "SELECT id,title,pg_title FROM page WHERE title=? ORDER BY id ASC";
     //Etc
}

Php PDO wrong row count

3 Answers3