Hi All,

 

I have a database containing all the places you can fly fish around the UK.

 

How can i display how many records each county has next to its name...

 

http://www.ukfishersonline.com/fly-fishery-directory.html (at the moment i just list all the countys, i would like to also display how many records i have for each county)

 

for example:

 

Hampshire (12)

Kent (15)

Bedfordshire (0)

 

Many Thanks

What database and server-side language(s) are you using?

 

In MySQL you could use COUNT, like (not tested):

 

 

SELECT county, COUNT(*) FROM directory WHERE fly_fishing = '1' GROUP BY county;

 

It might help if you provide more specific information

Thanks for replying..

 

I am using a SQL database on a linux server..

 

I am coding in PHP

 

 

I was thinking of using "count" but i thought it was depreciated

i bloody hope its not depreciated

as said use the count method ... e.g.

 

 "SELECT row_name, COUNT(name) FROM products GROUP BY type"

Thanks, i will give this ago when i get home....

 

I guess i then have to "echo" next to each county

Ok,

 

I managed to get the correct SQL

 

$sql = SELECT `fishery_county`, COUNT(*) FROM fishery_tbl GROUP BY `fishery_county`; 

 

Which gives me exactly what i want

 

http://www.ukfishersonline.com/test.html

 

But how can i display it in two columns like so:

 

http://www.ukfishersonline.com/fly-fishery-directory.html

 

Thanks

Anyone?

 

Would i use modulus to half the rows? and make it into two columns?

Anyone?

 

Would i use modulus to half the rows? and make it into two columns?

 

Theres no harm in using modulus, I've done it plenty of times (it's a quick and dirty way to do it). The only problem I can see in that is how you order it (it's in ascending order from left to right). A better way might be to use an if else condition and when it's greater than 20 (half the counties) populate the other div (I havent checked your markup btw). Of course if you don't mind it being left to right all the way down that's no problem.

 

SELECT county, COUNT(*) FROM directory WHERE fly_fishing = '1' GROUP BY county ORDER BY county ASC;

Theres no harm in using modulus, I've done it plenty of times (it's a quick and dirty way to do it). The only problem I can see in that is how you order it (it's in ascending order from left to right). A better way might be to use an if else condition and when it's greater than 20 (half the counties) populate the other div (I havent checked your markup btw). Of course if you don't mind it being left to right all the way down that's no problem.

 

SELECT county, COUNT(*) FROM directory WHERE fly_fishing = '1' GROUP BY county ORDER BY county ASC;

 

At the moment on "http://www.ukfishersonline.com/fly-fishery-directory.html" i am just manually listing them in a two column table that i have customised with CSS.

 

I guess what your saying is i should have two divs. with one floating left and the other floating right, then when echoing the countys out. use an if statement to echo 10 in the 1st div and 10 in the 2nd div?

 

Thanks