Update Now() in php mysql

Does anyone know how to update the current time of an entry when i edit an entry using a sql statement ?

Like   $sql="UPDATE $table set  etc etc ..

I know how to INSERT INTO $table (field,field,field) VALUES (Now(),'$_POST[field]')";
but how the heck do you update the current date.

The current update sql line I have is ...
$sql = "UPDATE $table set building='$building', idate='$idate', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor' where id='$id'";

Can anyone incorporate Now() into that line ?

Jon
jonlapalmeAsked:
Who is Participating?
 
cianwhalleyCommented:
Delete the idate column and recreate it as per what we discussed.
0
 
cianwhalleyCommented:
I'm going to assume that your date field is named date for the sake of this example.  It can be either a datetime field, or a date field depending on if you need the time or not.

$sql = "UPDATE $table SET building='$building', idate='$idate', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor', date=NOW() where id='$id'";
0
 
jonlapalmeAuthor Commented:
the date field is called  idate

I cant login to experts exchange on my slax computer to paste this and yahoo wont paste it I'll try that
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
jonlapalmeAuthor Commented:
ok tried this, nothing happened

$sql = "UPDATE timtable set building='$building', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor', subletcost='$subletcost', idate=NOW() where id='$id'";

Also tried

$sql = "UPDATE timtable set building='$building', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor', subletcost='$subletcost', date=NOW() where id='$id'";

Nothing there either, have a feeling its because i dont have a field called "date".   My INSERT INTO script is sending to  idate
0
 
cianwhalleyCommented:
$sql = "UPDATE timtable set building='$building', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor', subletcost='$subletcost', idate=NOW() where id='$id'";

Should work, and by nothing happened, what do you mean, did none of the other data get updated either?  Or just not the date field (which would be weird).
0
 
jonlapalmeAuthor Commented:
In my Show.php script where I view to see if the change has been made  It shows it as 2006-11-02    directly below it is a Timestamp  00:00:00     the timestamp doesnt change from the zeros.

By nothing happened, my edit2.php script that updates the database just echos its been sent, but when I check its not there. So im assuming the sql didnt work.
0
 
jonlapalmeAuthor Commented:
I'll post the whole script on here, waiting for yahoo to wake up
0
 
jonlapalmeAuthor Commented:
Ok, heres edit.php sending to edit2.php

edit.php

<?
$id = $_GET[id];

$user="root";
$password="toor";
$database="Tim";
$table = "timtable";
$today=(Ymd);

$link = mysql_connect('localhost', $user, $password);
 if (!$link) {
    die('Could not connect: ' . mysql_error());
 }



//connection to the database
@mysql_select_db($database) or die( "Unable to select database");



// echo 'Connected successfully';


$sql = "SELECT * FROM $table where id='$id'";

$result=mysql_query($sql);
$num=mysql_numrows($result);
mysql_close();

$i=0;

while ($i < $num) {

$id=mysql_result($result,$i,"id");
$d1=mysql_result($result,$i,"building");
$d2=mysql_result($result,$i,"idate");
$d3=mysql_result($result,$i,"purchase");
$d4=mysql_result($result,$i,"repair");
$d5=mysql_result($result,$i,"cost");
$d6=mysql_result($result,$i,"chequeid");
$d7=mysql_result($result,$i,"subletlabor");
$d8=mysql_result($result,$i,"subletcost");
$d9=mysql_result($result,$i,"description");
$d10=mysql_result($result,$i,"photo");

echo "<br> <table border=0 bgcolor=#FFFFFF width=90% align=center cellspacing=4 cellpadding=5 valign=top>
<tr>
<td width=33%>


<font size=1>
<FORM ACTION='edit2.php' METHOD='post'>
<h4>Modify Entry</h4>
<INPUT TYPE='hidden' VALUE='$id' NAME='id'>
<br>
<b>Choose Apartment</b>:<br><INPUT TYPE='text' VALUE='$d1' NAME='building'>
<br>
<br>
 <br><INPUT TYPE='hidden' VALUE='$d2' NAME='idate'><br><br>
<b>Purchases Made</b>:<br> <INPUT TYPE='text' VALUE='$d3' NAME='purchase'><br><br>


<b>Repairs Made</b>:<Br>
<INPUT TYPE='text' VALUE='$d4' NAME='repair'><br>
<br>
<b>Cost</b>: <br> <INPUT TYPE='text' VALUE='$d5' NAME='cost'><BR>

<br>
<b>Cheque Id #</b>: <br> <INPUT TYPE='text' VALUE='$d6' NAME='chequeid'><BR>


<br>
<b>Sublet Labor</b>:<br><INPUT TYPE='text' VALUE='$d7' NAME='subletlabor'>


<br><br>
<b>Sublet Cost</b>: <br><INPUT TYPE='text' VALUE='$d8' NAME='subletcost'>
<br><br>
</td>
</tr><tr>
<td width=45% valign=top>


<INPUT TYPE='submit' VALUE='Submit Changes'>
</font><br>
<font size=1>
<br><br> <a href=mod.php>Modify Another Record</a> | <a href=add.html>Add a new Record</a> | <a href=authenticate2.php>Main Menu</a></td></tr></table></font>
";

$i++;

}

?>


edit2.php

<?
$user="root";
$password="toor";
$database="Tim";
$table = "timtable";

$id=$_POST[id];
$building=$_POST[building];
$idate=$_POST[idate];
$purchase=$_POST[purchase];
$repair=$_POST[repair];
$cost=$_POST[cost];
$chequeid=$_POST[chequeid];
$subletlabor=$_POST[subletlabor];
$subletcost=$_POST[subletcost];



$link = mysql_connect('localhost', $user, $password);
 if (!$link) {
    die('Could not connect: ' . mysql_error());
 }


// echo 'Connected successfully';

@mysql_select_db($database) or die( "Unable to select database");



$sql = "update timtable set building='$building', idate='$idate', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor', subletcost='$subletcost' where id='$id'";

$result=mysql_query($sql);
//$num=mysql_numrows($result);
mysql_close();

$totalcost=($cost+$subletcost);

echo "<table border=1 width=50% cellpadding=10 cellspacing=10 align=center><tr><td>
<H4>Database Updated...</H4>
Building: $building<BR>
Purchase: $purchase<BR>
Repairs: $repair<BR>
Cost: $cost<br>
Cheque Id #: $chequeid <br>
Subletlabor: $subletlabor<br>
SubletCost: $subletcost<br>


<BR><font size=1>
<A HREF=mod.php>Modify Record</A><BR>
<A HREF=add.html>Add New Record</A>
</font>
</td>
</tr>
</table>
"
;

?>

0
 
cianwhalleyCommented:
Can I see the code snippet from your show.php that queries the db and echos the date and time?  Also, what field type is idate?  As well, can I see the code for edit2.php?  I should be able to track it down, as the SQL is fine...
0
 
jonlapalmeAuthor Commented:
I have idate set for Timestamp    Under Attributes it says ..   ON UPDATE CURRENT_TIMESTAMP    dont know what it means.
Heres the show script  its actually mod.php


<?

$user="root";
$password="toor";
$database="Tim";
$table = "timtable";


$link = mysql_connect('localhost', $user, $password);
 if (!$link) {
    die('Could not connect: ' . mysql_error());
 }


// echo 'Connected successfully';

@mysql_select_db($database) or die( "Unable to select database");


// echo 'Connected successfully';


$sql = "SELECT * FROM $table order by idate";


$result=mysql_query($sql);
$num=mysql_numrows($result);
mysql_close();


echo "


<FONT SIZE=1><a href=add.html>Add new entry</a> </FONT> | <FONT SIZE=1><a href=authenticate2.php>Main Menu</a> | <FONT SIZE=1><a href=apartmentsearch.html>Search for Apartment</a></FONT>
<br><br>
<TABLE BORDER=1 valign=top cellspacing=3 cellpadding=3>
<TR valign=top>
<td><FONT SIZE=1><img src=images/building.jpg></FONT></td>
<td></td>
<td><FONT SIZE=1><img src=images/purchases.jpg></FONT></td>
<td><FONT SIZE=1><img src=images/costcheque.jpg></FONT></td>
<td><FONT SIZE=1><img src=images/subletlabor.jpg></FONT></td>
<td><img src=images/picture.jpg></td>
<td><FONT SIZE=1><img src=images/total.jpg></FONT></td>
<td></td>
</TR>
";

$i=0;

while ($i < $num) {


if ($i % 2) {
$color='#cccccc';
}else{
$color='#eeeeee';
}


$id=mysql_result($result,$i,"id");
$d1=mysql_result($result,$i,"building");
$d2=mysql_result($result,$i,"idate");
$d3=mysql_result($result,$i,"purchase");
$d4=mysql_result($result,$i,"repair");
$d5=mysql_result($result,$i,"cost");
$d6=mysql_result($result,$i,"chequeid");
$d7=mysql_result($result,$i,"subletlabor");
$d8=mysql_result($result,$i,"subletcost");
$d9=mysql_result($result,$i,"photo");

$totalcost=($d5+$d7);
echo "<TR BGCOLOR=$color><TD><FONT SIZE=1><a href=edit.php?id=$id>$d1</a></FONT></TD><TD><FONT SIZE=1>$d2</FONT></TD><TD><FONT SIZE=1><em>Purchase</em>: $d3 <br><em>Repair(s)</em>: $d4</FONT></TD><TD><FONT SIZE=1><em>Part(s)</em>: $ $d5 <br> <em>Cheque Id</em>: $d6</FONT></TD><TD><FONT SIZE=1><em>Labor</em>: $d7 <br><em> Cost</em>: $ $d8</FONT></TD><td><a href=edit-pic.php?id=$id><font size=1>Edit</font></a><br><a href=$d9><img src=$d9 width=40px></a></td> <td><FONT SIZE=1>$ $totalcost</FONT></td><td><font size=1><a href=delete.php?id=$id>Delete</a></font></td></TR>";

$i++;



}



?>
0
 
cianwhalleyCommented:
Ahh I see your problem now.  You're using the TIMESTAMP type in mysql.  If you were using the DATETIME type, then this would work:

$sql = "UPDATE timtable set building='$building', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor', subletcost='$subletcost', idate=NOW() where id='$id'";

However, if you want to use the TIMESTAMP type, then you don't even need to UPDATE the idate field, as it will automatically be updated by mySQL.  Thus, the following should work:

$sql = "UPDATE timtable set building='$building', purchase='$purchase', repair='$repair', cost='$cost', chequeid='$chequeid', subletlabor='$subletlabor', subletcost='$subletcost' where id='$id'";

Also - I noticed that one of your scripts was still using mysq_numrows() again instead of the correct mysql_num_rows().
0
 
jonlapalmeAuthor Commented:
It wont let me switch from Timestamp to Datetime  Gives me an  error of  

Invalid ON UPDATE clause for 'idate' column  in phpmyadmin
Any idea what that is ?
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.