Solved

Update Now() in php  mysql

Posted on 2006-11-25
12
465 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

623 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