PHP, MySQL, Date Mainpulations

[radar]

Alright so no clue how to get this done.  Im working on a website currently, that is all about giving a daily deal.  Each deal added through the administration panel will run for a full 7 days, but it will be deal of the day on its first day that it will be active.

 

So in my deals table I have the following fields.

id (int, 11)

d_id (id from customers table, mysql_insert_id acquires this during insert)

title

img (folder the slideshow images are stored in)

date_added (now() )

min_qty

email

password (random password generated for account)

website

phone

fax

street

city

state

zip

status

txtAbout

business

date_start

date_end

expiry

coupon_prefix

 

Okay, so I have the date that it was added as now(), but what I need to figure out how to do is create the start date.  Since only one deal can be the deal of the day, this would need to query the database, and check all of the date_start fields and create this accordingly.  Or perhaps it would be easier to create it so that it reads all of the date_end fields, and if now() is  less than 24 hours away from the latest date_end, then we do now + 1 day for the start time, then now + 7 days for the end time..  im pulling my hair out with this one -- any help appreciated.

[radar]

Another option for this, that ive been thinking about is to maybe add in another table, config

 

and in this table is only one thing next_date

 

So when the deal is added, I would query for next_date from that table.

 

$now = now();

$next = mysql_result($sql, 0);

 

but then comes the rest of the code..  and its probably more indepth than what im thinking on paper but paper is meant to be basic..

 

if ($next < $now) {

 

$now = $now + 1 day

$end = $now + 7 days

$next = $now + 1 day

} else {

$now = $next + 1 day

$end = $now + 7 days

$next = $now + 1 day

}

 

or something to that effect..  and of course i have no clue how to do the math on everything...

 

my next_date, start_date and end_date would be all of the date type in the database, or would it be better to use date_time even though time doesnt really have any bearing on my output expected?

 

 

just doing more thinking...

 

theoretically i could query something like this

<?php
$id = $id-1;
$sql = mysql_query('SELECT end_date FROM deals ORDER BY id DESC LIMIT 1');
$cnt = mysql_num_rows($sql);
if ($cnt == 0) {
// add in today plus 1 day
} else {
// add in end date from query above, plus 1 day
}?>

 

although after looking that with syntax highlighting, i notice that end date wouldnt work, because that would mean 1 deal for 7 days.  when i want a new featured deal every day..  so i would have to query the start date instead of end date...  i have no clue wtf im doing lol

[radar]

Okay, so after playing around a little i have a file that is giving me the wanted output...  I havent tried to insert anything into the database as of yet..  but here is my switch code...  can someone maybe tell me if ive got it, or if i need to change something as far as the logic goes?

 

<?php
case add:
					switch($_sact) {
						default:
							$page = 'deals_add.tpl';
						break;

						case save:
							$sql = "SELECT id FROM customers WHERE email = ".$_POST['email']." LIMIT 1";
							$sql = mysql_query($sql);
							if (mysql_num_rows($sql) == '0') {
								$sql = "INSERT INTO customers SET
									f_name = '".$_POST['fname']."',
									l_name = '".$_POST['lname']."',
									email = '".$_POST['email']."',
									password = '".$aws->createRandomPassword()."',
									status = '".$_POST['status']."'";
								$data = mysql_query($sql);
								$id = mysql_insert_id($data);
							} else {
								$id = mysql_result($sql, 0);
							}
							$sql = "SELECT start_date FROM deals ORDER BY id DESC LIMIT 1";
							$sql = mysql_query($sql);
							$cnt = mysql_num_rows($sql);
							$sql = mysql_result($sql, 0);
							if ($cnt == 0) {
								$now = date('Y-m-d');
								$oned = strtotime("$now +1 day");
								$sevd = strtotime("$now +1 week");
								$oned = date('Y-m-d', $oned);
								$sevd = date('Y-m-d', $sevd);	
							} else {
								$oned = strtotime("$sql +1 day");
								$sevd = strtotime("$sql +1 week");
								$oned = date('Y-m-d', $oned);
								$sevd = date('Y-m-d', $sevd);
							}
							$sql = "INSERT INTO deals SET
							d_id = '".$id."',
							title = '".$_POST['title']."',
							img = '".$_POST['folder']."',
							date_added = now(),
							min_qty = '".$_POST['minimum']."',
							email = '".$_POST['email']."',
							password = '".$aws->createRandomPassword()."',
							website = '".$_POST['website']."',
							phone = '".$_POST['phone']."',
							fax = '".$_POST['fax']."',
							street = '".$_POST['street']."',
							city = '".$_POST['city']."',
							state = '".$_POST['state']."',
							zip = '".$_POST['zip']."',
							status = '".$_POST['status']."',
							txtAbout = '".$_POST['about']."',
							business = '".$_POST['business']."',
							start_date = '".$oned."',
							date_end = '".$sevd."',
							expiry = '".$_POST['expiry']."',
							coupon_prefix = '".$_POST['prefix']."'";
						break;
					}
				break;?>