Date not getting deleted

[monkeybidz]

I have this code to update mysql db. It deletes and updates everything in the row except for the date. The date remains the same. In my db i have it under "prices" and it is named "date". Here is the code I am using.

 

if($user_id !="" && $name !="" && $update=="yes"){
    mysql_query("DELETE FROM prices WHERE id='".$_POST['id']."'");
    $resultado = "You successfully updated this posting!<br>Thank you for participating."; 
$date=getdate();
$insert=mysql_query("INSERT INTO prices VALUES ('', '$user_id', '$name', '$bus_type', '$street', '$city', '$state', '$zip_code', '$brand', '$quantity', '$price1', '$price2', now())");

} 

[litebearer]

is your date field a datetime ie YYYY-MM-DD HH:MM:SS?

[monkeybidz]

Just YYYY-MM-DD no time.

[batwimp]

I'm not an expert in MySQL, but try using CURDATE() instead of NOW() and see if that works.

[Pikachu2000]

Why are you deleting a record if your goal is to update it?

[monkeybidz]

Well, it is to update the prices and it has to display the date it was updated. I just noticed that it is not deleting anything at all, it is just creating a new row with all the information and new date. I tried changing it to this:

if($user_id !="" && $name !="" && $update=="yes"){
    mysql_query("DELETE id, user_id, name, bus_type, street, city, state, zip_code, brand, quantity, price1, price2, date FROM prices WHERE id='".$id."'");
    $resultado = "You successfully updated this posting!<br>Thank you for participating."; 
$date=getdate();
$insert=mysql_query("INSERT INTO prices VALUES ('', '$user_id', '$name', '$bus_type', '$street', '$city', '$state', '$zip_code', '$brand', '$quantity', '$price1', '$price2', now())");

} 

 

I get the same result. How would I make it to just update the row instead of deleting?

[Pikachu2000]

You'd use an UPDATE query instead.

 

http://dev.mysql.com/doc/refman/5.5/en/update.html

[monkeybidz]

I tried this, but nothing:

 

mysql_query("UPDATE prices SET price1='".$price1."', price2='".$price2."', date='".$date."' WHERE id='".$id."' ");

 

Would I have to declare each value or just the ones I need updated?

[Drummin]

A little different take on it.

if(isset($price1) && isset($price2) && $update=="yes"){
mysql_query("UPDATE prices SET price1='$price1', price2='$price2', date='now())' WHERE id='$id'");

[AyKay47]

I tried this, but nothing:

 

mysql_query("UPDATE prices SET price1='".$price1."', price2='".$price2."', date='".$date."' WHERE id='".$id."' ");

 

Would I have to declare each value or just the ones I need updated?

 

 

You would declare only the field values that you want to update.

If this is not working for you, add a little debugging.

 

$sql = "UPDATE prices SET price1='$price1', price2='$price2', date='$date' WHERE id='$id'";
mysql_query($sql) or die($sql . "<br />" . mysql_error());

[monkeybidz]

Niether of the above worked. Not sure what you mean by debugging.

[AyKay47]

Niether of the above worked. Not sure what you mean by debugging.

 

debugging your query will show an error upon failure of the query.

If you did not receive any errors using the code I have posted, the problem is not with your query.

[litebearer]

simple debgging for you

if(isset($price1) && isset($price2) && $update=="yes"){

$date = date("Y-m-d");
$query = "UPDATE prices SET price1='$price1', price2='$price2', date='$date' WHERE id='$id'";
echo $user_id > "<br />";
echo $query;
exit():

[litebearer]

Oops, that > in the echo $user_id should be a  period

[monkeybidz]

Ok, with a bit of info from all you guys, It is now resolved. I went with a full delete and new insert. This was required as the person that updates the record will now be assigned to be the person that edited the post. Only the id, price1, price2 and date are really changed by the user. The rest of the vars are echoed on a form and are not editable by the user. Here is what I went with.:

 

if($user_id !="" && $name !="" && $update=="yes"){
    
$query = "DELETE FROM prices WHERE id='$id'";
    mysql_query($query);
echo mysql_error();	
    $resultado = "You successfully updated this posting!<br>Thank you for participating."; 
$insert=mysql_query("INSERT INTO prices VALUES ('', '$user_id', '$name', '$bus_type', '$street', '$city', '$state', '$zip_code', '$brand', '$quantity', '$price1', '$price2', now())");

}

 

Thanks a bunch people.

[Pikachu2000]

The problem with deleting a record and reinserting a new one when all you need to do is update an existing record is that you end up with a new, different primary key, thereby possibly destroying the referential integrity of the database. Take the time to make the update query work.