asked on

Date_Add Function

I am having a problem with DATE_ADD(NOW(), INTERVAL 2 HOUR) within php adding a record to mysql database. The statement works if I use it right in mysql workbench but not in data-entry.php page. All I get is 00-00-00 00:00:00 when i display the record if I added it through the php page. There is no mysql error nor php error

It works correctly when I use it within my update_entry.php page.

Here are a couple of my sql statements I use in my php pages.

This is the statement on my record entry page that DOES NOT WORK
       $sql = "INSERT INTO NetDevice (date_time, serialnum, modelnum, idManu, idDevType, idLocation, IPAdd, mask, MAC, UnitName, username, password, firmware, idResort) VALUES (DATE_ADD(NOW(), INTERVAL 3 HOUR), '".$_POST['SN']."', '".$_POST['ModelNum']."', '".$_POST['Manu']."', '".$_POST['DType']."', '".$_POST['Location']."', '".$_POST['IPAdd']."', '".$_POST['mask']."', '".$_POST['MAC']."', '".$_POST['UnitName']."', '".$_POST['username']."', '".$_POST['Password']."', '".$_POST['FW']."', '".$_POST['Resort']."')";

This is the statement in my update entry php page that DOES WORK.

       $sql = "UPDATE NetDevice SET date_time=DATE_ADD(NOW(), INTERVAL 3 HOUR), serialnum='".$_POST['SN']."', modelnum='".$_POST['ModelNum']."', idManu='".$_POST['Manu']."', idDevType='".$_POST['DType']."', idLocation='".$_POST['Location']."', IPAdd='".$_POST['IPAdd']."', mask='".$_POST['mask']."', MAC='".$_POST['MAC']."', UnitName='".$_POST['UnitName']."', username='".$_POST['username']."', password='".$_POST['Password']."', firmware='".$_POST['FW']."', idResort='".$_POST['Resort']."' WHERE IPAdd='".$_POST['IPAdd']."'";

I can also type this statement into mysql workbench and get the data to insert correctly. I did not use all the values, only the not_null columns.

INSERT INTO NetDevice (date_time, serialnum, modelnum, IPAdd, mask, MAC) VALUES (DATE_ADD(NOW(), INTERVAL 3 HOUR), 'serialnum1', 'modelnum1', 'IPadd1', 'mask1', 'mac2');

nanharbison

Have you getting mysql errors using
" or die (mysql_error());"

Roger Roman Jr

ASKER

No...I have no errors using the "or die (mysql_error())"

here is my expanded code including the or die statement:

include("../scripts/dbconn.php");
if (mysqli_connect_errno())
      {
       printf("Connection Failed: %s\n", mysqli_connect_error());
       exit();
      }
else
      {
       //add data to net device table
       $sql = "UPDATE NetDevice SET date_time=DATE_ADD(NOW(), INTERVAL 2 HOUR), serialnum='".$_POST['SN']."', modelnum='".$_POST['ModelNum']."', idManu='".$_POST['Manu']."', idDevType='".$_POST['DType']."', idLocation='".$_POST['Location']."', IPAdd='".$_POST['IPAdd']."', mask='".$_POST['mask']."', MAC='".$_POST['MAC']."', UnitName='".$_POST['UnitName']."', username='".$_POST['username']."', password='".$_POST['Password']."', firmware='".$_POST['FW']."', idResort='".$_POST['Resort']."' WHERE IPAdd='".$_POST['IPAdd']."'";
       //Run mysqli query
       $res = mysqli_query($dbconn, $sql) or die(mysqli_error($dbconn));

echo "Record Updated Successfully!";
      }
mysqli_close($dbconn);

Roger Roman Jr

ASKER

Sorry I added the update php code that works....BELOW is the insert php code that does not work

include("../scripts/dbconn.php");
//test for successful connection to database

if (mysqli_connect_errno())
      {
       printf("Connection Failed: %s\n", mysqli_connect_error());
       exit();
      }
else
      {
       //add data to net device table
       $sql = "INSERT INTO NetDevice (date_time, serialnum, modelnum, idManu, idDevType, idLocation, IPAdd, mask, MAC, UnitName, username, password, firmware, idResort) VALUES (DATE_ADD(NOW(), INTERVAL 2 HOUR), '".$_POST['SN']."', '".$_POST['ModelNum']."', '".$_POST['Manu']."', '".$_POST['DType']."', '".$_POST['Location']."', '".$_POST['IPAdd']."', '".$_POST['mask']."', '".$_POST['MAC']."', '".$_POST['UnitName']."', '".$_POST['username']."', '".$_POST['Password']."', '".$_POST['FW']."', '".$_POST['Resort']."')";
       //Run mysqli query
       $res = mysqli_query($dbconn, $sql) or die(mysqli_error($dbconn));

echo "Record Added Successfully!";
      }
mysqli_close($dbconn);

Shinesh Premrajan

Please define the field names in quotes like

INSERT INTO NetDevice (`date_time`, `serialnum`, `modelnum`.............

This may be the problem with

Hope this helps

ASKER CERTIFIED SOLUTION

nanharbison

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger Roman Jr

ASKER

I set the variable and it worked. Not sure why it would't work directly in the sql statement. For now the variable method will work....thanks for the thought nanharbison.

nanharbison

I have had issues like this happen to me often enough that I am good at making little changes and trying them, but I am not an expert on why you can set a table field equal to the date-time thing and then you CAN'T include it in other ways!
Glad to help!