[RESOLVED] Adding to a timestamp

[Nvenom]

Im trying to make a <when customer will return> type thing

<input type="text" id="pickup" name="pickup"size="5" maxlength="2">

<select name="set" id="set" rows="4" style="width: 113;">
        <option value="Minutes">Minutes</option>
	<option value="Hours">Hours</option>
	<option value="Days">Days</option>
	<option value="Weeks">Weeks</option>
	<option value="Months">Months</option>
</select>

What im trying to do is so that when you create a ticket via submit button it
creates a time stamp in mysql db column and than adds the time typed and selected above into it.

EG:

i choose 23 Minutes

$selectedtime = 23 minutes

on submit

timestamp = 2011-06-17 02:31:33 + $selectedtime = 2011-06-17 02:54:33

and finally 2011-06-17 02:54:33 gets inserted

and the date/time format im using to display the times is 'F d, Y, g:i A' if its important even though i dont think it is as im just posting not reading XD

Any help would be great sorry if its confusing had trouble trying to explain it XD

[Nvenom]

I found DATE_add from here w3schools but im not completely sure how i would implement it into reacting to my _POST['inputbox1'] etc..

[NogDog]

Assume you have validated/filtered the user inputs so that $date is the start date/time string in "Y-m-d H:i:s" format, $amount is the integer amount of time to be added, and $unit is a string with one of the values MINUTE, HOUR, DAY, WEEK, MONTH:

$sql = "INSERT INTO table_name (start_time, end_time) 
VALUES('$date', DATE_ADD('$date', INTERVAL $amount $unit)"; 

[Nvenom]

thanks for the reply that is exactly what im trying to do and it makes alot more sense now. for some odd reason though it isint adding the INTERVAL. i echoed all the variables and they all turn out okay, i even echoed the sql and it ended up like this

     
INSERT INTO problems (SDate, RDate) VALUES ('2011-06-17 18:48:47', DATE_ADD('2011-06-17 18:48:47', INTERVAL 2 HOUR)

i must be missing something really small i just cant seem to find it.

code im using

	
	
 <input type="text" id="pickup" name="pickup" size="5" maxlength="2">
					 
 <select name="set" id="set" rows="4" style="width: 113;">
	<option value="MINUTE">Minutes</option>
	<option value="HOUR">Hours</option>
	<option value="DAY">Days</option>
	<option value="WEEK">Weeks</option>
	<option value="MONTH">Months</option>
 </select>
 $Date = date('Y-m-d H:i:s');
$Amount = $_POST['pickup'];
$Unit = $_POST['set'];

$insert = "INSERT INTO problems (SDate, RDate)

VALUES ('$Date', DATE_ADD('$Date', INTERVAL $Amount $Unit)";
				
$add_member = mysql_query($insert);

could it be because my mysql db column is set to timestamp? should it just be text or something?

as timestamp when i submit it just ends up 0000-00-00 00:00:00

[NogDog]

You may need to use TIMESTAMPADD(), instead. (Note the syntax difference/sequence for the arguments.)

(Or, use a DATETIME columns instead of TIMESTAMP if you don't need the special functionality of TIMESTAMP. )

[Nvenom]

i have attempted all of those with no success however i do have another way of doing this i just need a bit more guidance.

Right now it posts like '3' 'DAY' into my table and on my other page it grabs them as variables once again but this time i want to try assign the DATE_add
to a variable is that possible?

right now i have

$Date = $row[0] // Printed as 2011-06-17 20:30:49 timestamp format
$Pickup = $row[1] // 1,2,3,4,5 etc...
$Pickset = $row[2] // MINUTE, HOUR, DAY etc...

and what im trying to do is like

$NDate = DATE_add('$Date' INTERVAL $Pickup $Pickset);

cant seem to plug it in though not even sure if its possible.

[NogDog]

The DATE_ADD() stuff I was referencing is MySQL, not PHP. If you want to do it within your PHP script, it's a different syntax and requires the use of a PHP DateTime object (see http://www.php.net/manual/en/datetime.add.php).

You may find it simpler to use strtotime(), e.g.:

$today = time();
$nextWeek = strtotime('+1 week', $today);
echo "Today: " . date('Y-m-d', $today) . ", Next Week: " . date('Y-m-d', $nextWeek); 

[Nvenom]

that worked perfectly

have any way to convert a mysql timestamp to unix time?

ive tried mktime() but it output the complete wrong value probally because it dosent read 0000-00-00 00:00:00 format properly and i have to break it up right?

[Nvenom]

NVM got it with

$sql = "SELECT unix_timestamp(Date) FROM problems WHERE Fixed = 0 AND ID = '$_POST[id]'";

        if($result=mysql_query($sql))
            {   
                       
                while($row = mysql_fetch_row($result))
                    {
                    $Ndate = $row[0];
                    }
                        
            }
else {echo "Query ERROR";} 

thanks a bunch i realize this was really stretched out lol XD