PHP Count in MySQL Results

PHP Count in MySQL Results

Instructions:

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.

Tagged as: count, MySQL, results