Low cost ecommerce web development India flash website design

Counting Rows with MySQL


The alternative approach is to use MySQL’s COUNT function within the query.
This requires that you perform two queries—one to count the results and one to
actually get the results—which will cost you a little in terms of performance.
Here’s how you could use the MySQL COUNT function:
File: 13.php (excerpt)
// A query to select all articles
$sql = "SELECT COUNT(*) AS numrows FROM articles";
// Query to count the rows returned
$queryResource = mysql_query($sql, $dbConn);
Chapter 3: PHP and MySQL

$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);
echo $row['numrows'] . " rows selected<br />";
// A query to select all articles
$sql = "SELECT * FROM articles ORDER BY title";
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
// Fetch rows from MySQL one at a time
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {
echo 'Title: ' . $row['title'] . '<br />';
echo 'Author: ' . $row['author'] . '<br />';
echo 'Body: ' . $row['body'] . '<br />';
}
Notice we used an alias to place the result of the COUNT function?
SELECT COUNT(*) AS numrows FROM articles
We do this so that the number of rows can be identified later using
$row['numrows']. The alternative would have been to omit the alias:
SELECT COUNT(*) FROM articles
This would require that we access the information as $row['COUNT(*)'], which
can make the code confusing to read.
When we use the COUNT function, it becomes important to construct queries on
the fly as we saw in “How do I create flexible SQL statements?”. You need to
make sure your COUNT query contains the same WHERE or LIMIT clauses you used
in the “real” query. For example, if the query we’re actually using to fetch data
is:
SELECT * FROM articles WHERE author='HarryF'
In PHP, we’ll probably want something like this:
File: 14.php (excerpt)
// Define reusable "chunks" of SQL
$table = " FROM articles";
$where = " WHERE author='HarryF'";
$order = " ORDER BY title";
// Query to count the rows returned

Counting Rows with MySQL
$sql = "SELECT COUNT(*) as numrows" . $table . $where;
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);
echo $row['numrows'] . " rows selected<br />";
// A query to fetch the rows
$sql = "SELECT * " . $table . $where . $order;
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
// Fetch rows from MySQL one at a time
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {
echo 'Title: ' . $row['title'] . '<br />';
echo 'Author: ' . $row['author'] . '<br />';
echo 'Body: ' . $row['body'] . '<br />';
}

website designer freelance ASP PHP ecommerce web developer
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110