Filter event listings by City

[ppunk]

I have a database table called 'listings' where visitors will be able to submit local event listings. The form they fill out includes the following:
first_name, last_name, email, address1, address2, city, state, zip, date, time, summary.
I want to setup a main listings page that organizes the listings by city.
Example:

Burlington (2)
Springfield (5)

The numbers next to the city names represent how many listings are posted for that city.
I want it to filter only cities that have a listing for the current week. So if there were only two cities that had events listed for that week, it would display the above. Let's say the previous week there was a listing from the city of Hartsfield, that entry will be in the database but will be expired and should not display on the site.
I actually setup the above example and got it working, but the only way I could do it was by creating a piece of code (if else statements) for each city to filter that exact city from the 'city' field in the database, and also to read the num_rows to display the number of listings in ( ).
That system I setup would never work beacuse I would have to create the code for each city in the database. So basically, I would have to do every city in my state in order for my system to work. If I don't and someone posts a listing for a city that is not entered in my setup, it won't get displayed on the site at all.

After spending hours on this, I finally came to my senses and decided to ask the experts. Does anyone have any ideas/suggestions for me?
Is there a way to setup the programming so it detects each different city in the database and display it like my example above?

I am stumped. Any help would be much appreciated.
Thanks!

[Barand]

[code]$today = time();
$dow = - date ('w', $today);
$sun = date ('Y-m-d', strtotime("+$dow days"));
$dow += 6;
$sat = date ('Y-m-d', strtotime("+$dow days"));

$sql = "SELECT city, COUNT(*)
           FROM listings
           WHERE date BETWEEN '$sun' AND '$sat'
           GROUP BY city'";
$res = mysql_query($sql) or die (mysql_error());

while (list($city, $events) = mysql_fetch_row($res)) {
       echo "$city ($events)<br>";
}
[/code]

[ppunk]

That worked great! I don't know what I would do without your help!
I have another thing I am looking into. I will probably start another post if I can't get any leads from doing a search here or on Google.
I'm looking into a way to archive old (expired) listings to another database table. I want to see if there is a way to automate it so it moves them from the listings table to the archive_listings table.
Well, I gotta do some research.
Thanks again for the excellent help Barand!

[Barand]

Assuming archive table has exectly the same format you need 2 sql queries

[code]$archivedate = date('Y-m-d, strtotime ("-3 months")); // or whatever age you decide on[/code]

[code]
INSERT INTO archive_listings SELECT * FROM listings WHERE date < '$archivedate'[/code]

[code]DELETE FROM listings WHERE date < '$archivedate'[/code]

Caveat : Make sure the firstquery is working properly before introducing the second query