phpmysql

'Counting' the number of records that match a certain criteria and displaying the numbers

I have a MySQL database containing a user's country and whether they are an individual or an organisation. The field names are 'country' and 'type'.

Using PHP, I'd like to 'count' the number of countries, the number of individuals and the number of organisations in the database and then display the numbers in the following example format:

<p>So far, <strong>500</strong> individuals and <strong>210</strong> organisations from <strong>40</strong> countries have registered their support.</p>
Solution

  • What you are looking for is a count based on a grouping. Try something like this:

    $sql = "SELECT type, count(*) as cnt FROM users GROUP BY type";
$result = mysql_query($sql);

$counts = array();

while ($row = mysql_fetch_assoc($result)) {
    $counts[$row['type']] = $row['cnt'];
}

    This will give you an array like

    Array (
    'individual' => 500,
    'organization' => 210
)

    For counting the countries, use the first statement as posted by Hammerite.

    EDIT: added a verbose example for counting the countries

    $sql = "SELECT COUNT(DISTINCT country) AS NumCountries FROM users";
$result = mysql_query($sql);

$number_of_countries = 0;
if ($row = mysql_fetch_assoc($result)) {
    $number_of_countries = $row['NumCountries'];
}

    This altogether you can then print out:

    printf('<p>So far, <strong>%d</strong> individuals and <strong>%d</strong> '.
       'organisations from <strong>%d</strong> countries have registered '.
       'their support.</p>', $counts['individual'], $counts['organization'],
        $number_of_countries);