Increment Date Problem

Using php too write to mysql

[chrisjackson67]

Can some one help me please,
I'm trying to write a set of consecutive dates to a mysql database. The first date stored ok then the rest of the values are reset to 0000-00-00 in the number of days requested.
Everything works fine till this line:

$day = date('Y-m-d', strtotime("$day +1 day"));

Any suggestions would be appreciated.



if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
mysql_select_db($database_aaat, $aaat);

$day = GetSQLValueString($_POST['day'], "date");
$avail = GetSQLValueString($_POST['avail'], "text");
$num_days = GetSQLValueString($_POST['num'], "int");

$num = 1;
while ( $num <=$num_days ) {

$insertSQL = sprintf("INSERT INTO fawltytours (`day`, pax1, avail) VALUES (%s, %s, %s)",
$day,
$num,
$avail);

$Result1 = mysql_query($insertSQL, $aaat) or die(mysql_error());
$day = date('Y-m-d', strtotime("$day +1 day"));
$num++;
}

The form I'm using


<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">yyyy-mm-dd format1</td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Date:</td>
<td><input type="text" name="day" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"># of days to insert into database:</td>
<td><input type="text" name="num" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Spots Available Available:</td>
<td><input type="text" name="avail" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input type="submit" value="Insert record" /></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1" />

[midtempo]

strtotime("$day +1 day");

isn't the right syntax

strtotime takes a unix timestamp and adds a length of time to it. if the timestamp isn't specified, it's assumed to be now.

so, what you're wanting is

strtotime("+1 day", mktime(0, 0, 0, $month, $day, $year)));

[rocknbil]

Welcome aboard chrisjackson67! Three things:

- Why are you using sprintf on the select statement? It's an odd way to go about it.

- If you give users the opportunity to "mess it up," THEY WILL. It is not a "maybe," it is a mathematical certainty. Don't use a text field for "day." Use three select lists. Hence the extra coding in my example, visualizing these named as "dt_month, dt_day, dt_year."

- If you're going to use mySQL, use mySQL date math. It's by far easier.

$avail = GetSQLValueString($_POST['avail'], "text"); 
$num_days = GetSQLValueString($_POST['num'], "int");  
// Oh, and be sure to DO THIS! 
if (! ($num_days > 0)) { 
 die("Invalid date"); // the preference would be to return to the form 
} 
// 
// Malicious input containing anything but numbers calculates to zero 
if (($_POST['dt_month'] > 0) and ($_POST['dt_day'] > 0) and ($_POST['dt_year'] > 0)) { 
 $date = "$_POST['dt_month']-$_POST['dt_day']-$_POST['dt_year']"; 
 } 
else { die("Invalid date"); } // return to form with error! 
// 
// Note ZERO and LESS THAN. This is so the first time through, 
// it DOES NOT increment the date. 
// 
for ($i=0;$i<$num_days;$i++) { 
 $date_calc = "date_add('$date',interval $i day)"; 
 $num = $i+1; 
 $insertSQL = "INSERT INTO fawltytours (`day`, pax1, avail) VALUES ($date_calc,'$num','$avail'); 
 $Result1 = mysql_query($insertSQL) or die(mysql_error());  
 // be sure to take mysql_error off after going live 
} 

Replace your loop with this, give it a shot. You can remove all your PHP date stuff if it works (it will. :-) )

I've seen that GetSQLValueString code around, I don't know where it comes from . . .but there are some issues with it, the way it quotes or doesn't quote is one . . . no matter. The above should work.

[chrisjackson67]

Thanks for the replies and taking time have a look,

this little projest i though ok 10 mins to set up ( the extra code is from dreamweaver (hense the sprintf and GetSQLValueString that i usually chop out when i've got some thing working)

I am still having problems if I do a dummy output and echo:
$insertSQL = "INSERT INTO fawltytours (`day`, pax1, avail) VALUES ($date_calc, $num, $avail)";

with the loop as described


I get:
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-12, 1, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-13, 2, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-14, 3, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-15, 4, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-16, 5, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-17, 6, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-18, 7, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-19, 8, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-20, 9, 12)
INSERT INTO fawltytours (`day`, pax1, avail) VALUES (2000-12-21, 10, 12)


but in the mysql table entry day is 0000-00-00 for each entry pax1 and avail is ok

what am i missing?
have been staring at this for days and still see no solution

[rocknbil]

Well, you missed something. Look:

$date_calc = "date_add('$date',interval $i day)";

So your select statement, for the first iteration, should echo like

INSERT INTO fawltytours (`day`, pax1, avail) VALUES (date_add('2000-12-12',interval 0 day), 1, 12)

The answer to why you
are getting zeroes: date needs to be quoted, without quotes it will evaluate to 000-00-00. But that select statement says "something else is happening." This is what I mean about that function, no quotes.

[chrisjackson67]

Thanks rocknbil
PROBLEM SOLVED!
I understand whats going on as the code is passed to the mysql now.
I changed 1 line to:
{ $date = $_POST['dt_year']."-".$_POST['dt_month']."-".$_POST['dt_day']; }
The other tips were very helpful as well keep up the good work.

[rocknbil]

<smacks head> DOH . . . man I am so peeved that PHP doesn't have a qq operator like Perl, I'm always making that mistake . . . <<<HERE is just not the same thing.