How to count rows in a while loop using pdo fetch

Question

I was using mysqli_fetch_array and the counting was right until I changed to fetch(), which now only returns the total number of rows instead of returning each number for each row.

So for row one, I want to echo "1", and so on.

NEW NOTE :Everything else inside the while statement is returning correct values, except the counter which returns the total number of rows whereas I want a row number in the order that it was selected from the sql statement.

As requested. This is my connection.

I don't know if i'm suppose to be checking " $e->getMessage();" on every query since I'm using this connection for all my queries.

try {
    $dbh = new PDO('mysql:host=localhost;dbname=my_db;charset=utf8', 'usr', 'pwd',
                    array(PDO::ATTR_EMULATE_PREPARES => false, 
                          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
                  );
} catch (PDOException $e){
    echo $e->getMessage();
}

This worked

$query = mysqli_query($con, 'SELECT * FROM music');

$count = 0;
while($row = mysqli_fetch_array($query)){
    $count++; 
    echo $count;
}

The new doesn't work.

$query = $dbh->query('SELECT * FROM music');

$count = 0;
while($row = $query->fetch()){
    $count++; 
    echo $count;
}

have you looked into this: php.net/manual/en/pdostatement.rowcount.php ?? — iam-decoder
– iam-decoder, Commented Aug 13, 2014 at 22:54
PDOStatement::rowCount — Returns the number of rows affected by the last SQL statement — user3109875
– user3109875, Commented Aug 14, 2014 at 12:56
PDO have a little different behavior. This is a replacement for your mysqli (stackoverflow.com/questions/25199122/…). — Ivijan Stefan Stipić
– Ivijan Stefan Stipić, Commented Aug 18, 2014 at 18:32

iswinky · Accepted Answer · 2014-08-19 10:43:15Z

2

Works fine, use a try catch do see if your PDO connection is working.

try {
    $dbh = new PDO('mysql:host=localhost;dbname=db', 'root', 'root',
                       array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}  catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

$sql = 'SELECT * FROM music';
$sth = $dbh->query($sql);

$count = 0;
while($row = $sth->fetch()){
    $count++; 
    echo $count;
}

I've just tested this and it works fine. Either your PDO connection is incorrect or your query returns no results. I suggest you var_dump($dbh) and see if it returns a PDO object or check that your query is correct. Is your table called music? It is case sensitive.

You also need to change your connection form mysqli to PDO

$mysqli = new mysqli("localhost", "user", "password", "database");

to

$dbh = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');

You can also throw PDO exceptions to see if any are occuring: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

edited Aug 19, 2014 at 10:43

answered Aug 8, 2014 at 8:37

iswinky

1,9793 gold badges17 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

user3109875 Over a year ago

Can u please explain what's making what i've done not to work and why this will work?

iswinky Over a year ago

You haven't called execute() on the the $query object

user3109875 Over a year ago

Why are you using prepare, this statement doesn't have to be prepared? I'm not using any external values in my question

iswinky Over a year ago

My bad. Your code looks correct, have you checked your PDO object?

iswinky Over a year ago

Try using a try catch block and change your mysqli connection to PDO if you haven't already

|

mdsaleem1804 · Accepted Answer · 2014-08-18 09:02:00Z

0

$conn->exec("SET CHARACTER SET utf8");

http://coursesweb.net/php-mysql/pdo-select-query-fetch

answered Aug 18, 2014 at 9:02

mdsaleem1804

11014 bronze badges

Comments

Mike Miller · Accepted Answer · 2014-08-18 16:05:46Z

0

What about:

if ($pdo){  
    $query=$pdo->prepare("SELECT count(*) as cnt FROM music");
    if($query->execute()){
        $count = $query->fetch()[0];//or fetch()['cnt'] 
        echo $count;
    }
}

answered Aug 18, 2014 at 16:05

Mike Miller

3,1273 gold badges27 silver badges32 bronze badges

Comments

Ivijan Stefan Stipić · Accepted Answer · 2014-08-18 16:27:55Z

0

PDO have a little different behavior. This is a replacement for your mysqli.

try {
    $dbh = new PDO('mysql:host=localhost;dbname=database', 'user', 'password',array(
        PDO::ATTR_EMULATE_PREPARES => false, 
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        // optional
        PDO::MYSQL_ATTR_INIT_COMMAND    => 'SET NAMES utf8',
    ));

    $count = 0;
    foreach ($dbh->query("SELECT * FROM `music`") as $row) {
        $count++; 
        echo $count;
    }
}  catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    exit();
}

I hope that helped.

answered Aug 18, 2014 at 16:27

Ivijan Stefan Stipić

6,7386 gold badges51 silver badges85 bronze badges

Comments

Japheth Ongeri - inkalimeva · Accepted Answer · 2014-08-12 11:02:57Z

-1

Try using PDO::FETCH_NUM to get the row count directly :-

$countquery = $dbh->query('SELECT COUNT(1) FROM music');
$rowCount = 0;
$rowCount = $countquery ->fetch(PDO::FETCH_NUM);
echo $rowCount;
//And then do another query for the real data if need be

* This makes use of a query to get the row count, and saves you the time taken by the while loop.

edited Aug 12, 2014 at 11:02

answered Aug 12, 2014 at 9:22

Japheth Ongeri - inkalimeva

4,9996 gold badges32 silver badges47 bronze badges

2 Comments

Tuncay Göncüoğlu Over a year ago

...and instead sends out same query to server twice, effectively doubling servers work load. I wouldn't prefer this way unless I know for certain that the data I'm querying is very small and creating the result set will be trivial for server.

Japheth Ongeri - inkalimeva Over a year ago

@TuncayGöncüoğlu SELECT COUNT(1) ... is a very light query, it is usually the recomended option in mysql of getting the size of the result set before doing a heavier query, especially when using innodb. Besides, iterating over all rows scales (very) badly as the number of rows increases.

Collectives™ on Stack Overflow

How to count rows in a while loop using pdo fetch

5 Answers 5

13 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

13 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related