How to count rows in while loop using pdo fetch

Question

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

So, for the first line, I want an echo of "1", etc.

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

In accordance with the request. This is my connection.

I do not know if I should check "$ e-> getMessage ();" per request, since I use this connection for all my requests.

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

It worked

 $query = mysqli_query($con, 'SELECT * FROM music'); $count = 0; while($row = mysqli_fetch_array($query)){ $count++; echo $count; } 

New does not work.

 $query = $dbh->query('SELECT * FROM music'); $count = 0; while($row = $query->fetch()){ $count++; echo $count; } 

Answer 1

Works well, use try catch, see if your PDO connection works.

 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 just experienced this and it works great. Either your PDO connection is incorrect, or your request does not return any results. I suggest you var_dump($dbh) and see if it returns a PDO object or checks if your request is correct. Is your table called music ? It is case sensitive.

You also need to change the mysqli connection form 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 there are any events: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

Answer 2

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

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

Answer 3

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

Answer 4

PDOs have a slightly 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 this helps.

Answer 5

Try using PDO::FETCH_NUM to get the line 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 allows you to use the query to count the rows and saves time spent on the while loop.

Answer 6

You seem to be confusing PDO and MYSQLI _

Are you saying that this code worked after using the PDO connection ?? But it issues a query through mysqli_query() . Weird!

 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(); } $query = mysqli_query($con, 'SELECT * FROM music'); $count = 0; while($row = mysqli_fetch_array($query)){ $count++; echo $count; } 

I don’t think it worked, or you changed something and you don’t tell us.

It should have been something like this

 $con= mysqli_connect('localhost', 'usr', 'pwd', 'my_db'); if (!$link) { die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error()); } $query = mysqli_query($con, 'SELECT * FROM music'); $count = 0; while($row = mysqli_fetch_array($query)){ $count++; echo $count; } 

So, your second part of the code can be written like this: -

 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(); } $sql = 'SELECT * FROM music'; foreach ($dbh->query($sql) as $row) { $count++; echo $count . "\n"; }