Use UPDATE or INSERT...ON DUPLICATE KEY?

[bacarudaguy]

Hi all -

 

I'm having a hard time try to figure out which to use for my situation... UPDATE or INSERT ... ON DUPLICATE KEY.

 

I have several pages that edit different information for users, seasons, venues, etc. My obvious db query is to update the records based anything that might be changed. Where I'm running into the issue is if I just pull up the page and hit submit like I changed something, but didn't actually change anything.

 

Here's my current query, along with a note of my original query I was using. Both queries trigger my error checking for mysql_affected_rows()... I'm stumped!

 

NOTES:

- The $id variable is passed from the prior page as the id (primary key in DB, auto increment on new record) that is being edited

- By default when the page loads, all 3 variables ($sn, $sd, $ed) are set and true based on error checking

 

Thanks in advance... I'm sure it's something simple I've overlooked...

 

//check all variables
if ($sn && $sd && $ed) {

	$query = "INSERT INTO seasons (s_id, s_name, s_startdate, s_enddate) VALUES ('$id', '$sn', '$sd', '$ed') ON DUPLICATE KEY UPDATE s_name = VALUES('$sn'), s_startdate = VALUES('$sd'), s_enddate = VALUES('$ed')"; 
	//UPDATE seasons SET s_name='$sn', s_startdate='$sd', s_enddate='$ed' WHERE s_id = $id (original)
	$result = mysql_query ($query) or trigger_error ("Query: $query\n<br />MySQL Error: " . mysql_error());

	if ((mysql_affected_rows() == 1) OR (mysql_affected_rows() == 2)) { // If it ran OK.
		echo '<p class="success"><b>Season successfully updated!</b></p>';
		include ('./includes/footer.html');
		exit();

	} else {

		echo '<p class="error">The season could NOT be updated due to a system error. We apologize for any inconvenience.</p>';
		include ('./includes/footer.html');
		exit();

	} //end if of mysql_affected_rows

} else {

	//something else failed
	echo '<p class="error">Please try again.</p>';

} //end check all variables

[fenway]

Unless there is a duplicate key, use update -- impossible to tell since we see nothing of the actual database.

[bacarudaguy]

Unless there is a duplicate key, use update -- impossible to tell since we see nothing of the actual database.

 

Here's the table structure... sorry, didn't realize you might need it. There shouldn't ever be a duplicate key as that would mean a duplicate season. At least that's my ultimate goal.

 

CREATE TABLE `seasons` (
`s_id` tinyint(3) unsigned NOT NULL auto_increment,
`s_name` varchar(15) NOT NULL,
`s_startdate` char(10) NOT NULL,
`s_enddate` char(10) NOT NULL,
PRIMARY KEY  (`s_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

[gevensen]

i would use date for your dates and not text

[bacarudaguy]

i would use date for your dates and not text

 

I originally was, but then found the way MySQL flips the format, it wasn't going to work for how I needed the dates displayed, and I couldn't quite get the chain of code figured out to swap YYYY-DD-MM around. I'm using an ereg() expression to check and verify the date is inputted in the format that it's supposed to be in my error checking for all submitted variables.

 

Thanks for the thought!

 

ereg ('^[0-9]{2}\/[0-9]{2}\/[0-9]{4}$', $_POST['end_date'])

[gevensen]

you could reformat it using explode after the verification is done

if you need to do any searches on dates you will regret not saving its as yyyy-mm-dd

its pretty simple to format it going in and converting it coming out

the reason im saying it is because its a mistake i made that drove me nuts till i fixed it

i like mm/dd/yyyy not yyyy-mm-dd but found it simple to reformat coming out using explode

[bacarudaguy]

you could reformat it using explode after the verification is done

if you need to do any searches on dates you will regret not saving its as yyyy-mm-dd

its pretty simple to format it going in and converting it coming out

the reason im saying it is because its a mistake i made that drove me nuts till i fixed it

i like mm/dd/yyyy not yyyy-mm-dd but found it simple to reformat coming out using explode

 

Thanks for the advice on that, but for this application, there won't be any date searching going on. My dates are the problem in this thread either...

[fenway]

Your dates WILL be a problem -- use a proper DATE column type, and use DATE_FORMAT() to get it displayed how you'd prefer -- you'll thank me later.

 

But I digress.... you're using ON DUPLICATE KEY UPDATE incorrectly.  if you use VALUES(), you need to refer to the column name, not the value.

[bacarudaguy]

Your dates WILL be a problem -- use a proper DATE column type, and use DATE_FORMAT() to get it displayed how you'd prefer -- you'll thank me later.

 

But I digress.... you're using ON DUPLICATE KEY UPDATE incorrectly.  if you use VALUES(), you need to refer to the column name, not the value.

 

So my dates will be a problem regardless if I use an ereg() to format and error control them?? I'll get to fixing that...

 

I just tried changing the VALUES() to reference the column name, instead of the variables and it's still triggering an error saying the season couldn't be updated...

 

I've tried it with and without reference to the s_id column.

 

$query = "INSERT INTO seasons (s_id, s_name, s_startdate, s_enddate) VALUES ('$id', '$sn', '$sd', '$ed') ON DUPLICATE KEY UPDATE s_id = VALUES(s_id), s_name = VALUES(s_name), s_startdate = VALUES(s_startdate), s_enddate = VALUES(s_enddate)";

[fenway]

So what doesn't work now? I'm lost.

[bacarudaguy]

It's not updating on DUPLICATE KEY unless I've misunderstood something. Does mysql_affected_rows() return 2 if it updates when using ON DUPLICATE KEY? I know it returns 1 on an insert....

 

$query = "INSERT INTO seasons (s_id, s_name, s_startdate, s_enddate) VALUES ('$id', '$sn', '$sd', '$ed') ON DUPLICATE KEY UPDATE s_id = VALUES(s_id), s_name = VALUES(s_name), s_startdate = VALUES (s_startdate), s_enddate = VALUES(s_enddate)"; 
	//UPDATE seasons SET s_name='$sn', s_startdate='$sd', s_enddate='$ed' WHERE s_id = $id (original)
	$result = mysql_query ($query) or trigger_error ("Query: $query\n<br />MySQL Error: " . mysql_error());

	if ((mysql_affected_rows() == 1) OR (mysql_affected_rows() == 2)) { // If it ran OK.
		echo '<p class="success"><b>Season successfully updated!</b></p>';
		include ('./includes/footer.html');
		exit();

	} else {

//this else is being triggered if i just hit submit and don't change anything...
		echo '<p class="error">The season could NOT be updated due to a system error. We apologize for any inconvenience.</p>';
		include ('./includes/footer.html');
		exit();

	} //end if of mysql_affected_rows

[fenway]

Can't you just see if the column value changed?

[bacarudaguy]

Can't you just see if the column value changed?

 

What's your suggestion for going about that? I'm all self-taught on PHP from a book I have, browsing the web and reading these forums. From someone who knows what they're doing, I'd like to hear a good way!

[fenway]

Sorry, now I'm really confused -- you're setting the old values to the new values, and they're exactly the same, so of course nothing has changed.

[bacarudaguy]

Sorry, now I'm really confused -- you're setting the old values to the new values, and they're exactly the same, so of course nothing has changed.

Well... what I'm trying to do is, say the season needs to be changed, the user clicks the edit link, comes to this page, then realizes everything is actually correct and hits submit. I know the values are the same, but I would think it should still pass the values to the DB regardless if they're the same or not. Make sense?

[fenway]

Oh... I see what you're doing.  You're trying to merge the logic for create & update.  That's less than ideal in the long term.

[bacarudaguy]

Oh... I see what you're doing.  You're trying to merge the logic for create & update.  That's less than ideal in the long term.

Anything you suggest or am I stuck with this problem?

[fenway]

I guess the question I have is why you care if it updates "nothing".

[bacarudaguy]

I guess I don't... was just trying to think of things from the users pov... I guess I should just stick with my original UPDATE statement then?

[fenway]

Yup.