Display number of DB results beside a link?

[Solarpitch]

For an example, if you look at www.menupages.ie they give you a preview of how many results are found for each link.

 

ie:

 

Ballsbridge Restaurants  (52)

Blackrock Restaurants  (17)

Clondalkin Restaurants  (18)

Clontarf Restaurants  (17)

 

How is this achieved. I take it that it must be a select count on the DB for each row but that seems pointless, is there an easy way to achieve this?

[effigy]

SELECT name, COUNT(*) FROM table GROUP BY name;

[Solarpitch]

Hi,

 

I am not too sure what you mean by this.  In my database say I have the following... each of which are entries of a  column called "category"

 

Drivers (68)

Woods (14)

Irons (6)

Wedges (54)

 

So I need to calculate how many times Drivers is set as the "category", how many times Woods is set as the "category" and so on...

 

Example of the table...

 

ID -------- Category -------------- Price ------------ Date

 

1              Drivers                        60                      13.09.07

2              Woods                        140                      13.09.07

3              Drivers                        60                      13.09.07

4              Drivers                        55                      13.09.07

5              Irons                          200                      13.09.07

6              Drivers                        60                      13.09.07

 

So as above would be

 

Drivers (4)

Woods (1)

Irons (1)

Wedges (0)

 

 

Hope this makes sense! 

 

[rarebit]

As effigy say's, see aggregate function in mysql manual:

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

[Solarpitch]

Ok, I've had a read over that and came up with this...

 

$query = "Select count(*) From ads Where category in ('Drivers','Woods') Group by category";
$result = mysqli_query($mysql_connect, $query) or die (mysqli_error($mysql_connect));

$query_data = mysqli_fetch_row($result);
$link1 = $query_data[0];
$link2 = $query_data[1];

echo $link1; -----> Output = 18 which is correct
echo $link2; -----> Output = 0 but should be 6

 

Maybe this is correct but I am not calling the value from the array properly?

[effigy]

You need to fetch the next row.