Jump to content

Filter event listings by City


ppunk

Recommended Posts

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!
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.