Total records found

ldtiw

Hi there,

Ive created a dance school directory and im wanting to display the total number of schools in each region so

London (10)

but im using dreamweaver and cant figure out how to create the sql to use the total records found for each region.

i have two tables for the countrys and regions:

Countrys:

id | Name | Desc

Regions:

id | Name | Parent | Desc

and in the school table where school info is held it contains both country and region.

and the area that calls the repeate region:


          <?php do { ?>
          <li><a href="schools.php?country=<?php echo $HTTP_GET_VARS['country']; ?>&region=<?php echo $row_getgegions['name']; ?>"><?php echo $row_getgegions['name']; ?></a></li>
          <?php } while ($row_getgegions = mysql_fetch_assoc($getgegions)); ?>

here is is working: http://www.letsdancetheirishway.com/schools.php

And also, any idea to break the long lists into two colums?? ie America, all of them are down in one line. Looks a little messy.

Thanks in advance.

Danny

barand

I think this is the sql you are looking for

$sql = "SELECT country, region, COUNT(*) as numschools FROM school GROUP BY country, region";

$result = mysql_query($sql);

while (list ($c, $r, $n) = mysql_fetch_row($result)) {
         echo " $c $r : $n schools<br />";
}

hth

Najjar

Try to use Crosstabbing

http://dev.mysql.com/tech-resources/articles/wizard/index.html

ldtiw

Hi there,

Cant figure it out, so included the pages code from
http://www.letsdancetheirishway.com/schools.php

I need it to display total amount of schools in a country, and then once the country is clicked, to display total schools found for each region.

so page one with list of countries:

England (10)
Ireland (30)

Then Regions Page:

London (5)
Midlands (5)

Like ive said im using dreamweaver just to throw it out fast. But still cant figure this one out.

Thanks,
Danny Lewin

barand

For countries

$sql = "SELECT country, COUNT(*) as numschools FROM school GROUP BY country"; 

$result = mysql_query($sql); 

while (list ($c, $n) = mysql_fetch_row($result)) { 
         echo " $c  ($n) <br />"; 
}

For regions

$sql = "SELECT region, COUNT(*) as numschools FROM school WHERE country = '$country' GROUP BY region"; 

$result = mysql_query($sql); 

while (list ($r, $n) = mysql_fetch_row($result)) { 
         echo " $r  ($n) <br />"; 
}

ldtiw

Hi there!

Thanks for the help with this.

It nearly worked, but it was only repeating the countrys/regions that actually had schools in them. I would need it to also print all those without, as thats is where the country/region is selected from when someone wants to add a school.

like with the USA country. There are about 24 states in there, it was only returning about 5.

Any ideas?

Thanks,

Danny

tsinka

Hi,

am I right that Parent in the regions table is a reference to the country table ?

If yes then using a LEFT JOIN might do the trick.

A school belongs to exactly one region, right ? You don't need to store the country in the schools table. It is sufficient to store the region id since you can get the country out of the regions table.

But this makes only sense if Parent is a reference to the countries table.

I can give you an example of such a query as soon as I know how the tables depend on each other.

Thomas

barand

$sql = "SELECT c.country, COUNT(s.school_ID) as numschools FROM country c LEFT JOIN school s ON c.country = s.country GROUP BY country";  

$result = mysql_query($sql);  

while (list ($c, $n) = mysql_fetch_row($result)) {  

         echo " $c  ($n) <br />";  

}

and similarly for the regions

tsinka

Hi barand,

reading your post I might need to correct myself. In this case it might be better to store the country in the schools table to make the queries simple.

Anyway, I think your query wouldn't return the countries/regions that have no schools, this one should do the trick:

Example for the countries...

$sql = "SELECT c.country, COUNT(s.id) as numschools FROM country c LEFT JOIN school s ON c.id = s.country_id 
WHERE s.country_id>0 OR s.country_id IS NULL
GROUP BY country";

Correct me if I am wrong 🙂

Thomas

barand

My query would count the schools ids for each country.

COUNT(field) only counts fields with a value so where there was no match the id field would be null and therefore a zero count.

Where there was a match the school count would be returned so you get

USA (30)
England (25)
Wales (0)

Edit PS on the subject of whether to hold country in the school table it depends on the data (an unknown). If each region has a unique id and that is in the schools table than I agree with your earlier post.

If however, the region name is held in the school table then there is a possibility of 2 countries having same region name, therefore country would also need to be held to keep the combination unique.

tsinka

Hi,

I apologize 🙁

I had a similar query some weeks ago where I had to add IS NULL in addition to field='something' in a where clause in order to get any results additionally. Next time I'll do some tests before posting 🙂

Thomas

barand

The most common use of a LEFT JOIN is to find unmatched records

eg customers who haven't placed an order in the last six months

so in that case you would want to check for NULL values

tsinka

I try to remember ... I think it was a LEFT JOIN without a GROUP BY that had to find all records that matched against a certain value and those that didn't have a match at all (with one query).

Thomas

ldtiw

Barand,

Thank you SOO much. I had a little trouble trying to understand what you were trying to tell me for a split second, but its all working fine now, so THANK YOU VERY MUCH!!

Will try and read up on LEFT JOIN ect... at what times would using it be appropriate??

Again thanks loads,

Regards,
Danny Lewin

barand

Will try and read up on LEFT JOIN ect... at what times would using it be appropriate??

INNER JOIN returns records where the field specified for the join has a matching value in both tables

LEFT JOIN returns all selected records from the left table whether a match exists or not. If no match, fields selected from right table contain NULL.