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!
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.
mysql_* functions, many mysqli functions share the same naming convention, which might make a refactor easier on you. Good luck!$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.
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`";
$num_rows_example3 = mysql_num_rows($query);, from the OP... read the code!
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.