SELECT by the hour!

[Jumpy09]

I'm trying to set up a time-based query which will allow users to sponsor themselves for the duration of an hour.

 

Since I code backwards, I'd figure I would get the query out of the way as the script behind inserting may be a bit more difficult than I would like.

 

So, let's say Bob has a sponsorship at 9pm, and Bill has one at 10pm.

From 9PM to 9:59PM Bob should have his sponsorship, and at 10pm... of course on refresh...  Bill should begin his.

 

I have no idea what to even look for, as anything I've typed in .. comes to various other results.

 

Anyone have an idea?

[Jumpy09]

I got :

 

<?php

$sponsor = mysql_query("SELECT * FROM `specialty_sponsors` WHERE showtime = HOUR(NOW())") or die(mysql_error());;

?>

 

I thought it would work, but it doesn't seem to want to.

[a.stilliard]

Your showtime field, how are the times formated? standard mysql time (now()) or just the hour?

 

have a look round for things like... "mysql time differences", or "mysql time date" maybe.

 

EDIT, i cant gues the sql without an idea of the data format.

[Jumpy09]

I have DateTime as the showtime.  I figure it was wrong because it would pull anything on that hour, if it had happened to work.

 

Table Layout:

 

specialty_sponsor

 

sponsorid -- uid -- message -- showtime -- date

 

SponsorID - Is the sponsor Identification, basically the primary key since nothing else can be made primary.

UID -- User Identifaction - Shows who will be hosted during that hour.

Message -- Should speak for itself.

Showtime -- datetime formated time period of when someone should show.

Date -- Date it was added to the database.  I was planning a Countdown to show users when they are being sponsored.

 

I'll check those on Google, the ideas you submitted.  It will come in handy for differences.

[Jumpy09]

I've gotten this!

 

<?php

$time = time() + 3600;
$sponsor = mysql_query("SELECT * FROM `specialty_sponsors` WHERE `showtime` < '$time' AND `showtime` + 3600 > 'time()' ORDER BY showtime LIMIT 1") or die(mysql_error());
$sponsorrow = @mysql_fetch_array($sponsor);

if($sponsor == true) {
$sponsoruid = $sponsorrow['uid'];
$sponsormessage = $sponsorrow['message'];
$sponsorshowtime = $sponsorrow['showtime'];

} else if($sponsor == false) {
$sponsormessage = 'Currently there isn\'t a Sponsor!';

}

?>

 

It doesn't display anything though, and --

 

$sponsor = mysql_query("SELECT * FROM `specialty_sponsors` WHERE `showtime` < 'time() + 3600' ORDER BY showtimeDESC LIMIT 1") or die(mysql_error());

 

Shows only the last result, but if no one is hosting a sponsorship at 2pm, I don't want it showing anyone.

[Jumpy09]

If only we could modify out posts.

 

 

I now have:

$time = time() + 3600;
$sponsor = mysql_query("SELECT * FROM `specialty_sponsors` WHERE `showtime` < '$time' AND `showtime` + 3600 > 'time()' ORDER BY showtime LIMIT 1") or die(mysql_error());
$sponsorrow = @mysql_fetch_array($sponsor);

if(mysql_num_rows($sponsor) == 0) {
	 $sponsormessage = 'Currently there isn\'t a Sponsor!';
} else {
  $sponsoruid = $sponsorrow['uid'];
$sponsormessage = $sponsorrow['message'];
$sponsorshowtime = $sponsorrow['showtime'];
}

 

Still doesn't work, but shows there isn't a sponsor.

[Jumpy09]

Should I change the database field to something else?  Change the date() or time() thing to something else?  I know it's possible, just can't quite figure out how.

[Jumpy09]

Finally, I figured it out!  Whew!  Thanks for all the help guys!

[James25]

GUuys in phpfreaks are really smart!