Counting entries in database

[ld0121]

Hi all, I have a database with various fields... one of them is "region"

REGION
r1 = Scotland
r2 = North East
r3 = North West
etc etc

then on my html/php page i want to list the regions, then in brackets add how many entries there are in the database for that region.

Scotland (?)
North East (?)
etc etc

The question is how do I count how many entries for each region and then show the result.

Any help would be great

Thanks
Lee

[mark_79_smith]

Hi,

You have 2 options.

1. $sql = mysql_query("SELECT COUNT(*) FROM tablename WHERE region = 'Scotland'");
2. $sql = mysql_query("SELECT * FROM tablename WHERE region = 'Scotland'");
   $num = mysql_num_rows($sql);

Hope this helps

[toxic_brain]

SELECT region_field, COUNT(*) as region_count FROM table_region GROUP BY region_field

echo "$row->region_field  ($row->region_count)";

[ld0121]

Thanks for posting a reply... but im still stuck... im testing it with Region 6 (r6) as that is the only one poulated at the moment...

Tried the first option

  <?
									  mysql_connect("localhost","XXXXX","XXXXX");
									  mysql_select_db("XXXXX") or die("Unable to select database");
									  $sql = mysql_query ("select * from gzvenues where gzvenues.region = r6");
									  $num = mysql_num_rows("$sql");
									  echo "<font size=2 color=FF0099 face=Arial, Helvetica, sans-serif>$num</font><p>";
									  ?>

And im getting an error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/www/XXXX/ukmap.php on line 83

line 83 being $num = mysql_num_rows("$sql");

Then tried option 2

<?
									  mysql_connect("localhost","XXXX","XXXXX");
									  mysql_select_db("XXXX") or die("Unable to select database");
									  $sql = mysql_query ("select COUNT(*) from gzvenues where gzvenues.region = 'r6'");
									  echo "<font size=2 color=FF0099 face=Arial, Helvetica, sans-serif>$sql</font><p>";
									  ?>

And this bought up Resource id #6 what does that mean??

Im sorry to say I really dont understand the 3rd option from Toxic Brian, sorry Brian its just to much for my small brain.

So if anyone can help some more, I would be forver grateful.

Thanks
Lee

[awesome_aims]

For your query you wouldn't do:

SELECT * FROM gzvenues WHERE gzvenues.region =.....

you would do

SELECT * FROM gzvenues WHERE region = ...

Because you have already specified gzvenues for it to select from.
IDK, it may work in some cases, but that's not how I learned it.

[toxic_brain]

mysql_query() returns only a handle to the opened recordset.
change first option to this.

$sql ="select * from gzvenues where gzvenues.region = 'r6'    "; // u missed to put sigle quotes here
$result = mysql_query($sql) ;
$num = mysql_num_rows($result);

or second option

$sql ="select count(*)  as C from gzvenues where gzvenues.region = 'r6'    ";
$result = mysql_query($sql) ;
$row = mysql_fetch_object($result);
$num=$row->C;

[ld0121]

Hi thanks again, im sorted and its all working.

Thanks
Lee