[RESOLVED] UPDATE SQL/PHP duplicating records??

Chud37

Hello,

I have a simple table, called meetings, it has a ID (primary key), location, Datetime, (which is a PHP Timestamp, an INT in the table) address, and extra info field.

Add and delete records works fine, But i have no idea why the UPDATE is working incorrectly. I've now managed to get it updating the record in question, but its producing another record exactly the same in the table, but at the end (with a new ID number).

Why is update doing this? This should be a very simple SQL statement to run. The only thing i can do is narrow down the problem to the Timestamp, Im not sure why but i get the feeling the problem is with that, As it is split into two fields to edit it, H:i, and another textbox with a javascript calendar that produces the format 23-04-10.

Im pretty fed up with it and i cannot find anyone else who has had this problem.

Anyway, here is the code where the SQL is run:

	if(isset($_POST['formID'])) {  					//They selected EDIT, and are now updating the record.
			$newStamp = strtotime($_POST['Time'] . " " . $_POST['newDate']);
			//$srid = mysql_real_escape_string($_POST['SRID']);
			echo $_POST['Time'] . " -- " . $_POST['newDate'] . "<br>";
			openDB();
			$col1 = mysql_real_escape_string($_POST['formID']);
			$col2 = mysql_real_escape_string($_POST['newLoc']);
			$col3 = mysql_real_escape_string($newStamp);
			$col4 = mysql_real_escape_string($_POST['newAddr']);
			$col5 = mysql_real_escape_string($_POST['newExI']);
			echo $newStamp . "<br>\n";
			$query = "UPDATE meetings SET Location = '$col2', Timestamp = '$col3', Address = '$col4', ExtraInfo = '$col5' WHERE ID = '$col1'";
				echo $query;
				mysql_query($query);
			closeDB();
			echo "<br><hr>The meeting at <b>" . ucwords($_POST['newLoc']) . "</b> has been updated.<br><hr>";
	}

Please help 🙁

~Chud37

Chud37

Just as a side note, when i output the $query, and run it in phpMyAdmin, the record updates fine and there are no bloody duplicates.

i need help 🙁

johanafm

I'd guess your php code runs one update query and one insert query. Easy to check by adding another echo for your insert query.

Chud37

flip!

your right!!!

i use the same form for everything.. crap.. how did i miss that!

dont you just hate it when your staring at code for ages and the answer is right in your face yet you've sat there for so long you just cant see it.

That did flick past my mind but i paid no attention to it. Thank you so much, i really appreciate it.

Crowly

Perhaps a bit on the side of your problem, but could be safty net to avoid duplicate entries or errors on unique keys.

You can put ON DUPLICATE KEY UPDATE in a INSERT statement, which results in an update instead of an insert if the primary key is the same.

Heres an example

        $insert=sprintf("INSERT INTO vgpressstatus
                         VALUES ('%s',%d,'%s',%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,
                         STR_TO_DATE('%s','%%Y-%%m-%%d %%T'), STR_TO_DATE('%s','%%Y-%%m-%%d %%T'),
                         STR_TO_DATE('%s','%%Y-%%m-%%d %%T'), STR_TO_DATE('%s','%%Y-%%m-%%d %%T'),
                         STR_TO_DATE('%s','%%Y-%%m-%%d %%T'),NOW())
                         ON DUPLICATE KEY UPDATE
                         press=VALUES(press),
                         product=VALUES(product),
                         stateload=VALUES(stateload),
                         statephase=VALUES(statephase),
                         statenet=VALUES(statenet),
                         statebreak=VALUES(statebreak),
                         grosscopies=VALUES(grosscopies),
                         netcopies=VALUES(netcopies),
                         nominalcopies=VALUES(nominalcopies),
                         allowancecopies=VALUES(allowancecopies),
                         pressspeed=VALUES(pressspeed),
                         maxpressspeed=VALUES(maxpressspeed), 
                         copiesperrev=VALUES(copiesperrev),
                         plannedstart=VALUES(plannedstart),
                         plannedend=VALUES(plannedend),
                         start=VALUES(start),
                         runningstart=VALUES(runningstart),
                         estimatedend=VALUES(estimatedend),
                         oppdatert=NOW()",
                         $row['press'],$row['prodno'],
                         mysql_real_escape_string($row['product']),
                         $row['stateload'],$row['statephase'],
                         $row['statenet'],$row['statebreak'],$row['grosscopies'],$row['netcopies'],$row['nominalcopies'],
                         $row['allowancecopies'],$row['pressspeed'],$row['maxpressspeed'],$row['copiesperrev'],$row['plannedstart'],
                         $row['plannedend'],$row['start'],$row['runningstart'],$row['estimatedend']
                        );

bradgrafelman

On an ON DUPLCIATE KEY UPDATE structure, you don't use the VALUES keyword - you use the same syntax you'd use an in UPDATE query (ironically enough 😉), e.g. "var1='value1', var2='value2', ..." etc.

EDIT:

Crowly wrote:
which results in an update instead of an insert if the primary key is the same.

Don't forget UNIQUE indexes, too!

Crowly

bradgrafelman;10957465 wrote:
On an ON DUPLCIATE KEY UPDATE structure, you don't use the VALUES keyword - you use the same syntax you'd use an in UPDATE query (ironically enough 😉), e.g. "var1='value1', var2='value2', ..." etc.

According to what i found on the mysql pages, you can, so it takes the value from the "insert part", and it works as i am using it in some of my scripts, but you can use the same syntax as with UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Posted by Shane McEwan on October 27 2006 4:09am [Delete] [Edit]

Here is an example of how to update multiple columns using values supplied in the INSERT statement. This assumes that column 'a' is the unique key.

INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=VALUES(d), e=VALUES(e);

This also works for multiple rows:

INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5), (6,7,8,9,10) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=VALUES(d), e=VALUES(e);

If you have a lot of columns it would be nice if you could use the following syntax:

INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5) ON DUPLICATE KEY UPDATE VALUES(b,c,d,e);

and it would match the columns you want to update with the values in the INSERT. Unfortunately, this does not work. You MUST explicitly provide each column assignment.

bradgrafelman

Interesting - I've never used that syntax before. I'll have to remember that one.