sorting problem

jjstuart

I am having a problem figuring out how to sort my results from mysql. I have a table with these values

id, zip_code, month, day, year

I have an array with 10 zip codes and I need to find every record that contains those zip codes. Then based on those results I would like to be able to sort the results based on the date. The code I am using is.

for($t=0; $zipResult = $zip[$t]; $t++) {
$cal_sql = "SELECT * FROM calendar WHERE month >= $month
and day >= $day and year >= $year and zip_code = $zipResult";
$calendar_result = mysql_query($cal_sql);
while($cal_rec = mysql_fetch_array($calendar_result)) {
create_tables($cal_rec['month'],$cal_rec['day'],$cal_rec['year']);
}
}

$month = current month
$day = current day
$zip = zip code array
create_tables() = function that prints the results

I know I am obviously going about it wrong. I just dont know how else to do it.
If you need more info please let me know.

Thanks

laserlight

Change your SQL statement to ORDER BY year, month, day

jjstuart

Thanks that helped. What I ended up doing is running this query.

$sql_first = "SELECT * FROM calendar WHERE month >= $month and year >= $year ORDER BY year, month, day";

Then running this

for($t=0; $zipResult = $zip[$t]; $t++) {
$cal_sql = "SELECT * FROM calendar WHERE id = $event_id and zip_code = $zipResult";
$calendar_result = mysql_query($cal_sql);
while($cal_rec = mysql_fetch_array($calendar_result)) {
create_bar_tables($cal_rec['id'],$cal_rec['title']);
}
}

What I didnt plan for is the fact that I would only like so many results to appear on a page. Doing it the way I am I do not see any way to use LIMIT in the query. Since the first statement contains results that I may not use. Is there any way I could I guess restructure this so I can do that with sql? I am basically selecting all results in the first query that have a certain date then narrowing it down in the second query based on zip code.

Thanks again.

laserlight

I shall assume that $zip is your array of zip codes.

$zipcodes = implode(',', $zip);
$query = "SELECT id, title FROM calendar
	WHERE id = $event_id
		AND zip_code IN ($zipcodes)
		AND month >= $month AND year >= $year
	ORDER BY year, month, day
	LIMIT 10";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
	create_bar_tables($cal_rec['id'], $cal_rec['title']);
}

Of course, if your zip codes are strings, then you should use:

$zipcodes = "'" . implode("','", $zip) . "'";

Sxooter

Is there reason you're not using s timestamp field instead of separate y m d fields?

jjstuart

Thank you very much laser light. That was what I needed. Sxooter, no there was no reason except I just didnt know better. I ended up using a timestamp instead though, thanks.