PHP Count in MySQL Results

PHP scripts retrieve data from databases and present it within webpages.

When developers create websites using the PHP language and a MySQL database, they can carry out various types of data query. Sometimes a developer will need to determine the number of database table rows resulting from a query, so that they can structure these results within HTML markup. This task can be achieved using both SQL and PHP functions. Both options require only a few lines of code.

SQL Queries

Developers working with MySQL databases can execute SQL queries on them. Within these queries, a developer can specify a particular set of data, referencing table and column names. The results of a query may include multiple records. Depending on the application, this may mean that the developer has no idea how many records a query will return, which can pose a problem when presenting data within a Web interface in PHP. This is where count queries can prove useful.

Querying From PHP

Websites built using server side scripts in PHP can use standard functions to execute queries on MySQL databases in SQL. The PHP for a website normally handles retrieving data from the database, then iterating through the results, presenting some or all of the data items within HTML markup structures that the user will ultimately see on viewing the page. If a PHP script is building query results into HTML, it may therefore need to know how many records it is dealing with. In some cases, query results are split across multiple HTML pages.

MySQL Count

The count function in SQL allows developers to determine the number of records a particular query will return. The following sample code demonstrates a count query:
SELECT COUNT(*) FROM customer;

This returns the total number of records in the specified table. To return the number of distinct values in a particular table column, the developer could use the following version:
SELECT COUNT(DISTINCT firstname) FROM customer;

To execute a count query from a PHP script, developers use the following syntax:
$count_query = "SELECT COUNT(DISTINCT firstname) FROM customer";
$result = mysql_query($count_query);

This gives the script a reference to the number of rows in the data returned from the query.

PHP Number of Rows

Within a PHP script, developers can use language functions to determine the number of records in a query result set, rather than executing a count query specifically to retrieve that information. The following sample code demonstrates:
$query = "SELECT * FROM customer";
$result = mysql_query($query);
$num_records = mysql_num_rows($result);

This allows the developer to determine the number of records without having to carry out a separate query. Otherwise there may be a need to execute one query to get the data and another to find out how many records are in it. The "mysql_num_rows" function returns an integer that the script can refer to when building webpage HTML.

References

• W3Schools: PHP MySQL Introduction
• W3Schools: PHP MySQL Select
• W3Schools: SQL COUNT() Function
• PHP Manual: mysql_query
• PHP Manual: mysql_num_rows

Resources

• MySQL Documentation: Counting Rows
• Tizag: MySQL Tutorial - Query
• Tizag: MySQL Tutorial - Count