How to count a value with PHP

Hello guys,

I have datas in my database and for example there is a city column and I need to count how many people are from New York and show it on my dashboard table. I need to count the number of people form the city and show it in my dashboard table with $newyork for example. By the way I will do it for 80 cities so let me know if there is a way without repeting the steps for each city.

Can someone help me with it assuming my database is database and table is table?

Thanks

 

not really should how your db is set up but if you use ony one table then the query below should work.

SELECT *,COUNT(*) AS `tot` FROM `table` GROUP BY `city`

Code (markup):

 

I don't thing my db would matter, its just about one table and one column, by the way I need to specifically count New York city and using PHP code.

 

It does matter. because without seeing what you actually have done so far, you leave us guessing.

SELECT COUNT(*) AS `tot` FROM `table` WHERE `city`="New York City"

Code (markup):

 

OK! How will it be in PHP?

I used this but I only receive "1" as result...

mysql_connect("localhost", "user", "pass") or die ("Problem with Connection...");
mysql_select_db("database");
$query = mysql_query("SELECT COUNT(*) AS tot FROM users WHERE city='New York'");
$newyork = mysql_num_rows($query);

Do you have any idea?

 

ok 2 things.


1. the usage of the mysql extension is discouraged which means it shouldn't be used any more.

2. this is how you need to do it.

mysql_connect("localhost", "user", "pass") or die ("Problem with Connection...");
mysql_select_db("database");
$query = mysql_query("SELECT COUNT(*) AS tot FROM users WHERE city='New York'");
$result = mysql_fetch_assoc($query);
$newyork = $result['tot'];

PHP:

or if you are using the PDO library

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = 'SELECT COUNT(*) AS `tot` FROM `users` WHERE `city`="New York"';
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetch(PDO::FETCH_ASSOC);

PHP:

Yes it looks like a lot more code but trust me it is a lot safer to use it.

 

No problem. I like sharing my dote.

 

You can also try using the global mysql_num_rows function. This returns the number of rows where the query pulled the desired data. This way you never have to use COUNT at all.

$data = mysql_query("SELECT * FROM users WHERE city = 'New York'");
$num_rows3 = mysql_num_rows($data4);
echo $num_rows3;

 

You are right but there are 2 problems.

1. As I stated above using the mysql functions is DISCOURAGED. Just to make sure you understand what it means: It means you shouldn't use it! You should use an alternative.

2. Your SQL is very bad. Why do you select first everything and store it in memory and then count it if you can let the database do the counting and store simply a single value in memory. This might not be a problem if you have a small table but if you have thousands of lines or even more then your SQL will actually slow everything down.

Just because you get the same result doesn't mean that it's ok to use it.

 

Guys thank you for answers! I have solved it last night in this way;

Is selecting one column better? What do you think?

$query = mysql_query("SELECT COUNT(city) FROM users WHERE city='New York'");
$newyork = mysql_result($query, 0);

 

Why do you use the mysql functions?? It is DISCOURAGED to use them. Don't you care or are you just lazy?

 

I really have no idea, its something I found on web as a solution and it worked...

What's the problem to use them?

Thanks

 

He is making the case that this is the old fashioned way of doing this. Nowadays many developers are using the PDO object instead of the mysql globals. It may be more secure to do it this way. But, if you haven't started using the more object-oriented practices yet then your solution will probably fit your needs. If you are doing a lot of PHP development then you might want to pick a some of the newer PHP books such as the newest book from sitepoint about PHP development. It will show you how to use the PDO object.

 

Another reason to not use the mysql functions is that they may be discontinued in the future. Then, when your host upgrades to a new version of PHP, your site stops working. And fixing it will be a nightmare, because you'll post your code, which is flawless, tell us it used to work but stopped, and we won't have read the email you got telling you that your host is now running MySQL 9.3 or something.

 

I would not recommend COUNT(city), instead do COUNT(*), which is much faster.

Additionally, do not have the 0 in there unless you have multiple DB connections, in which you should have the Db resource connection instead.

<?php
$query = mysql_query("SELECT COUNT(*) FROM users WHERE city='New York'");
$newyork = mysql_result($query); 

PHP:

 

We used count method for counting the any value.