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');      
 
rromanjrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nanharbisonConnect With a Mentor Commented:
So it must be some picky detail, because you are calling it correctly.
This might be grasping at straws, but did you try setting the date time equal to a variable first? SOmetimes that works for me.
$thistime = (DATE_ADD(NOW(), INTERVAL 2 HOUR);

for the insert query:
'".$thistime."'

Open in new window

0
 
nanharbisonCommented:
Have you getting mysql errors using
" or die (mysql_error());"
0
 
rromanjrAuthor Commented:
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);

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rromanjrAuthor Commented:
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);
0
 
Shinesh PremrajanEngineering ManagerCommented:
Please define the field names in quotes like

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

This may be the problem with

Hope this helps
0
 
rromanjrAuthor Commented:
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.
0
 
nanharbisonCommented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.