Page 1 of 1

[SOLVED] Selecting unique "timestamp"s from a MySQ

Posted: Mon Sep 12, 2005 10:36 pm
by Mr Tech
Hey there...

I have a script that allows someone to add the service times for a chuch into database using time(). There are normally about three church services on the same day.

Basically there is a form where they select the day, month and year. When adding the service to the database, I use this code to convert the service times into timestamp:

Code: Select all

$service_datday = mktime(0, 0, 0, $HTTP_POST_VARS[month], $HTTP_POST_VARS[day], $HTTP_POST_VARS[year]);
$service_dattime = mktime($hour, $HTTP_POST_VARS[minute], 0, $HTTP_POST_VARS[month], $HTTP_POST_VARS[day], $HTTP_POST_VARS[year]);
The database tables:

Code: Select all

service_intid
service_strname 
service_datday
service_dattime
That all works fine... But now with the question...

Basically what I want to do is select four of the up coming services from the database. That's easy except some days, like I said above, have three services on that one day... So three of the service_datday would be exactly the same because the service is on the same day. I only want to select one of those three from the database.

So basically I would need something like this... This is just a guess of how it would work...

Code: Select all

$query = mysql_query("select * from {$tbl_name}services order by unique(service_datday) desc") or die("<b>MySQL Error:</b> ".mysql_error());
while($row = mysql_fetch_array($query)) {
ect...
I'm not sure what to use instead of unique(service_datday).

Hopefully that all makes sense :)

Cheers

Posted: Mon Sep 12, 2005 10:46 pm
by John Cartwright
SELECT DISTINCT(`timestamp`) ...
or you can use the GROUP BY clause, in your case you'd GROUP BY `timestamp` or whatever your timestamp field may be

Posted: Mon Sep 12, 2005 11:42 pm
by Mr Tech
Thanks! Exactly what I needed!