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;
}

Answer 1

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