jump to navigation

Counting records from a Mysql query with PHP May 23, 2011

Posted by Tournas Dimitrios in PHP.
trackback

My previous  article outlined the 3 methods available to connect a PHP driven website with a Mysql Database . Counting the results from a Mysql query can be useful in cases like : paginating the result if the returned record list is very long , in authentication procedures ( if returned record is not null –> user is in the member list )  etc … Just to recap , the 3 methods available to connect a PHP driven website to Mysql are :

  1. The original Mysql extension
  2. The improved Mysqli extension
  3. The PDO  extension

This article will present a practical example for each connection method . While Mysql extension and it’s improved version , Mysqli  , have similar ways to count the returned records , PDO takes a totally different approach . Let’s go directly to practical examples :

Counting the result from a original Mysql extension query :

<?php
include('dbconn_mysql.ini.php');
// connect to MySQL
$conn = dbConnect('visitor');
// prepare the SQL query
$sql = 'SELECT * FROM countries';
// submit the query and capture the result
$result = mysql_query($sql) or die(mysql_error());
// find out how many records were retrieved
$numRows = mysql_num_rows($result);
?>

Counting the result from a improved Mysqli extension query :

<?php
include('conn_mysqli.inc.php');
// connect to MySQL
$conn = dbConnect('visitor');
// prepare the SQL query
$sql = 'SELECT * FROM countries';
// submit the query and capture the result
$result = $conn->query($sql) or die(mysqli_error($conn));
// find out how many records were retrieved
$numRows = $result->num_rows;
?>

Counting the result from a PDO extension query :
Because the PDO extension doesn’t have a equivalent function ” num_rows or mysql_num_rows() ” like the previous 2 extensions , we have to apply the counting capability directly on the SQL query .

<?php
include('dbconn_pdo.ini.php');
// connect to MySQL
$conn = dbConnect('query');
// prepare the SQL query
$sql = 'SELECT COUNT(*) FROM countries';
// submit the query and capture the result
$result = $conn->query($sql);
$error = $conn->errorInfo();
if (isset($error[2])) die($error[2]);
// find out how many records were retrieved
$numRows = $result->fetchColumn();
?>

.

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s