0

How can I do this in a better way? 

$query = mysql_query("SELECT * FROM table WHERE region='example1'");
$num_rows_example1 = mysql_num_rows($query);    

$query = mysql_query("SELECT * FROM table WHERE region='example2'");
$num_rows_example2 = mysql_num_rows($query);    

$query = mysql_query("SELECT * FROM table WHERE region='example3'");
$num_rows_example3 = mysql_num_rows($query);

Maybe with an Array and Foreach?

Thanks!

Rafael Fonseca
  • 259
  • 1
  • 3
  • 7
  • 1
    It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). – Matt Aug 09 '12 at 18:30
  • What do you mean "a better way"? Do you mean in only one query? – Tchoupi Aug 09 '12 at 18:31

3 Answers3

3

Your query could be

SELECT region, count(*) as num_regions FROM table GROUP BY region

And now you only have one query to perform, which returns the number of rows for each region!

Now you can perform some PHP magic using your array of 

$regions = array('example1', 'example2', 'example3');

foreach ($regions as $region) {
    // do something with the MySQL result
}

PLEASE NOTE You should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article.

Community
  • 1
  • 1
Matt
  • 6,745
  • 4
  • 24
  • 49
  • Thanks Matt but I don't have all the regions on my table. I want to count if there is 2 rows with "region1" or 0 rows with "region2"... – Rafael Fonseca Aug 09 '12 at 18:39
  • This will allow you to do that. You just need to see if those regions exist in the mysql result. If they don't, you know that the count is zero. – Matt Aug 09 '12 at 18:40
  • @Rafael If you're converting from `mysql_*` functions, many `mysqli` functions share the same naming convention, which might make a refactor easier on you. Good luck! – Matt Aug 09 '12 at 18:48
1
$query = mysql_query("SELECT region, COUNT(region) AS count FROM table GROUP BY region");

while($row = mysql_fetch_assoc($query)) {
  ${$row[region]} = $row[count];
}

When you echo $example1; it will produce the count of that region.

Kermit
  • 32,563
  • 10
  • 80
  • 117
0

First and foremost:

Please, don't use mysql_* functions to write new code. They are no longer maintained and the community has begun deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide which, this article will help you. If you pick PDO, here is good tutorial.

Now that that's out of the way:

$query = "SELECT `region`, count(*) AS `count` FROM `table` GROUP BY `region`";
orourkek
  • 2,071
  • 14
  • 22