Selecting Distinct months from UNIX_TIMESTAMP - URGENT!!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kristian
Forum Newbie
Posts: 10
Joined: Tue Jul 29, 2003 1:21 pm

Selecting Distinct months from UNIX_TIMESTAMP - URGENT!!!

Post 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???
Last edited by kristian on Wed Jul 30, 2003 4:53 am, edited 1 time in total.
kcomer
Forum Contributor
Posts: 108
Joined: Tue Aug 27, 2002 8:50 am

Post 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
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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?
kristian
Forum Newbie
Posts: 10
Joined: Tue Jul 29, 2003 1:21 pm

Post 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...?
kristian
Forum Newbie
Posts: 10
Joined: Tue Jul 29, 2003 1:21 pm

Post by kristian »

How should I be storing my dates in MYSQL anyway - is using a UNIX_TIMESTAMP not the way to go...?
kristian
Forum Newbie
Posts: 10
Joined: Tue Jul 29, 2003 1:21 pm

Post 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...
Post Reply