Page 1 of 1

Selecting Distinct months from UNIX_TIMESTAMP - URGENT!!!

Posted: Tue Jul 29, 2003 1:21 pm
by kristian
Hi - I have a problem that hopefully can be easily resolved.

I have an Events table in MYSQL with 'start_date' and 'end_date' fields. Data in these fields are in UNIX_TIMESTAMP format.

I wanted to create a dropdown list showing DISTINCT months and/or years from all records using the 'start_date' data.

Drop down might look like this:
Jan - 2003 --- Might be 5 records in here
Mar - 2003 --- Might be 2 records in here
Oct - 2003 --- Might be 23 records in here
Feb - 2004 --- Might be 1 record in here

So, any ideas how I select distinct months and/or years from a UNIX_TIMESTAMP stored in MYSQL table???

Posted: Tue Jul 29, 2003 2:29 pm
by kcomer
Get the unix timestamp for the start and end of the month and then do a SELECT * FROM events WHERE date between $first_timestamp AND $second_timestamp

I think that would work.

Keith

Posted: Tue Jul 29, 2003 2:32 pm
by nielsene
The database I use has function such as month(), day(), etc defined on unix timestamps that I can use in queries. Check the mysql manual, maybe they do too?

Posted: Wed Jul 30, 2003 3:39 am
by kristian
kcomer: Thanks, that's what I'm gonna need to actually search for a specific month, but my problem is getting the DISTINCT months into the drop menu in the first place (see below).

I have this so far...

Code: Select all

<?php
////////////////////////////////////////////////////////////////////
// Dynamic drop down list for Months with Events                  //
////////////////////////////////////////////////////////////////////
?>
<select name="event_month" class="forms-inputbox" id="event_month">
<?php
	$query = "SELECT DISTINCT start_date FROM events ORDER BY start_date";
	$result = mysql_query($query, $link);
		if(mysql_num_rows($result)) &#123;
			while($row = mysql_fetch_row($result))
			&#123;
				$monthyear&#1111;0] = date("M - Y",$row&#1111;0]);
				print("<option value="$row&#1111;0]">$monthyear&#1111;0]</option>\n");
			&#125;
		&#125; else &#123;
			print("<option value="">Error</option>");
		&#125;
?>
</select>
This is what I get...

Image

...there are 8 events in August and so August - 2003 is repeated 8 times...so any idea how I can show only DISTINCT dates...?

Posted: Wed Jul 30, 2003 5:03 am
by kristian
How should I be storing my dates in MYSQL anyway - is using a UNIX_TIMESTAMP not the way to go...?

Posted: Wed Jul 30, 2003 7:48 am
by kristian
Got it...!!!

Code: Select all

<?php
////////////////////////////////////////////////////////////////////
// Dynamic drop down list for Event Months                        //
////////////////////////////////////////////////////////////////////
?>
<select name="event_month" class="forms-inputbox" id="event_month">
<?php
	// each cycle is compared with the last for a match using this var
	$compare = "";
	//
	$query = "SELECT DISTINCT start_date FROM events ORDER BY start_date";
	$result = mysql_query($query, $link);
		if(mysql_num_rows($result)) &#123;
			// we have at least one row, so show all rows as options in select form
			while($row = mysql_fetch_row($result))
			&#123;
				$monthyear&#1111;0] = date("M - Y",$row&#1111;0]);
				if ($monthyear!=$compare)&#123;
					print("<option value="$row&#1111;0]">$monthyear&#1111;0]</option>\n");
				&#125;else&#123;
					// do nothing
				&#125;
				$compare = $monthyear;
			&#125;
		&#125; else &#123;
			print("<option value="">Error</option>");
		&#125;
?>
</select>
...Unless anyone knows a better (more standard) way of doing this...?

Image

Now too sort out the resulting search query once the dropdown menu is used...