Solved

Update Now() in php  mysql

Posted on 2006-11-25
12
454 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:jonlapalme
  • 7
  • 5
12 Comments
 
LVL 1

Expert Comment

by:cianwhalley
ID: 18013533
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
 

Author Comment

by:jonlapalme
ID: 18013536
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
 

Author Comment

by:jonlapalme
ID: 18013549
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
 
LVL 1

Expert Comment

by:cianwhalley
ID: 18013558
$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
 

Author Comment

by:jonlapalme
ID: 18013569
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
 

Author Comment

by:jonlapalme
ID: 18013573
I'll post the whole script on here, waiting for yahoo to wake up
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:jonlapalme
ID: 18013579
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
 
LVL 1

Expert Comment

by:cianwhalley
ID: 18013580
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
 

Author Comment

by:jonlapalme
ID: 18013589
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
 
LVL 1

Expert Comment

by:cianwhalley
ID: 18013608
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
 

Author Comment

by:jonlapalme
ID: 18013615
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
 
LVL 1

Accepted Solution

by:
cianwhalley earned 250 total points
ID: 18013620
Delete the idate column and recreate it as per what we discussed.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
This video shows how use content aware, what it’s used for, and when to use it over other tools.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now