Getting Date and Time to be inserted into mysql database

run2web

Is there a specific format that must be used to get a date from a form to inserted into a mysql db? I have 2 separate files that I am using, the first is the form where they enter the data that is stored. The second is the file that actually does the insertion.

I have set my variables to the following in the first file:

	$ReqDate = date("Y-d-m");

This is automatically filled with the current date but can be modifed.

In the 2nd file I have the following code:

$db_name = "Hagerstown";
$table_name = "PlotRequests";

$connection = @mysql_connect("localhost", "dbname", "password") or
die("Could not connect");

$db = @mysql_select_db($db_name, $connection) or die("Could not select database.");

$sql = "INSERT INTO $table_name (ID, PlotNo, CustID, ReqDate, ReqTime)
VALUES ('$ID', '$PlotNo', '$CustID', '$ReqDate', '$ReqTime')
";

//result of mysql_query() function
$result = @mysql_query($sql,$connection) or die("Could not execute query.");

..........

<tr bgcolor="#edecfe"> 
          <td  width="34%" height="30"><strong><font color="#000000" face="Arial, Helvetica, sans-serif" size="2">Date 
            Requested : </font></strong></td>
          <td height="30" colspan="5"> <strong><font color="#000000" face="Arial, Helvetica, sans-serif" size="2"><?php echo "$ReqDate"; ?> 
            </font></strong></td>
        </tr>

Nothing is being inserted into the database.

Any suggestions?

TIA

troublemaker

The syntax looks fine.

Except how are you passing the variable $ReqDate, from the first page to the second one?

Since this does not appear to be an input field, you will need to pass it in the header or as a hidden field.

run2web

Sorry, it is being passed as an input field

<input name="ReqDate" type="text" id="ReqDate" value="<?php echo $ReqDate; ?>" size="25" maxlength="15">

malbera

have youu tried to simply echo the query, to see if it is ok?

run2web

The SQL Query is stating the following:

SQL statement says: INSERT INTO PlotRequests (ID, PlotNo, CustID, ReqDate, ReqTime) VALUES ('3', '', '', '2002-29-08', '')

However when I check the db the ReqDate is not being entered. The value there is 0000-00-00

malbera

maybe it's because mysql stores date as YMD , not YDM
try to insert the date this way into the db

hope that helps

run2web

That was it! Thank you. Now I am off to see why the time isn't inserting. Probably a similar problem.

mrmufin

Since you're not affecting the date, and you're already using a temporal data type to store it, why are you fiddling around with the PHP date() command at all?

For example:

$sql = "INSERT INTO $table_name (ID, PlotNo, CustID, ReqDate, ReqTime) VALUES ('$ID', '$PlotNo', '$CustID', NOW(), '$ReqTime')";

Just my too sense...

run2web

Because they are going to be added to a queue. The user will be able to change the date manually but I am just showing the current date so that they do not have to edit that entry if it is from that date.