asked on

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

cianwhalley

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'";

jonlapalme

ASKER

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

jonlapalme

ASKER

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

cianwhalley

$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).

jonlapalme

ASKER

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.

jonlapalme

ASKER

I'll post the whole script on here, waiting for yahoo to wake up

jonlapalme

ASKER

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 " <table border=0 bgcolor=#FFFFFF width=90% align=center cellspacing=4 cellpadding=5 valign=top>
<tr>
<td width=33%>


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

Repairs Made: 
<INPUT TYPE='text' VALUE='$d4' NAME='repair'> 
 
Cost: <INPUT TYPE='text' VALUE='$d5' NAME='cost'> 

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

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

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

<INPUT TYPE='submit' VALUE='Submit Changes'>
 

 <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>
";

$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 
Purchase: $purchase 
Repairs: $repair 
Cost: $cost 
Cheque Id #: $chequeid 
Subletlabor: $subletlabor 
SubletCost: $subletcost 

 
<A HREF=mod.php>Modify Record</A> 
<A HREF=add.html>Add New Record</A>

</td>
</tr>
</table>
"
;

?>

cianwhalley

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...

jonlapalme

ASKER

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 "

<a href=add.html>Add new entry</a> | <a href=authenticate2.php>Main Menu</a> | <a href=apartmentsearch.html>Search for Apartment</a>
 
<TABLE BORDER=1 valign=top cellspacing=3 cellpadding=3>
<TR valign=top>
<td><img src=images/building.jpg></td>
<td></td>
<td><img src=images/purchases.jpg></td>
<td><img src=images/costcheque.jpg></td>
<td><img src=images/subletlabor.jpg></td>
<td><img src=images/picture.jpg></td>
<td><img src=images/total.jpg></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><a href=edit.php?id=$id>$d1</a></TD><TD>$d2</TD><TD>Purchase: $d3 Repair(s): $d4</TD><TD>Part(s): $ $d5 Cheque Id: $d6</TD><TD>Labor: $d7 Cost: $ $d8</TD><td><a href=edit-pic.php?id=$id>Edit</a> <a href=$d9><img src=$d9 width=40px></a></td> <td>$ $totalcost</td><td><a href=delete.php?id=$id>Delete</a></td></TR>";

$i++;

}

?>

cianwhalley

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().

jonlapalme

ASKER

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 ?

ASKER CERTIFIED SOLUTION

cianwhalley

membership

This solution is only available to members.

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

Start Free Trial