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)) {
while($row = mysql_fetch_row($result))
{
$monthyearї0] = date("M - Y",$rowї0]);
print("<option value="$rowї0]">$monthyearї0]</option>\n");
}
} else {
print("<option value="">Error</option>");
}
?>
</select>
This is what I get...
...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)) {
// we have at least one row, so show all rows as options in select form
while($row = mysql_fetch_row($result))
{
$monthyearї0] = date("M - Y",$rowї0]);
if ($monthyear!=$compare){
print("<option value="$rowї0]">$monthyearї0]</option>\n");
}else{
// do nothing
}
$compare = $monthyear;
}
} else {
print("<option value="">Error</option>");
}
?>
</select>
...Unless anyone knows a better (more standard) way of doing this...?
Now too sort out the resulting search query once the dropdown menu is used...