bacarudaguy Posted December 22, 2009 Share Posted December 22, 2009 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted December 22, 2009 Share Posted December 22, 2009 Unless there is a duplicate key, use update -- impossible to tell since we see nothing of the actual database. Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 22, 2009 Author Share Posted December 22, 2009 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 Quote Link to comment Share on other sites More sharing options...
gevensen Posted December 22, 2009 Share Posted December 22, 2009 i would use date for your dates and not text Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 22, 2009 Author Share Posted December 22, 2009 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']) Quote Link to comment Share on other sites More sharing options...
gevensen Posted December 22, 2009 Share Posted December 22, 2009 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 Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 22, 2009 Author Share Posted December 22, 2009 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... Quote Link to comment Share on other sites More sharing options...
fenway Posted December 22, 2009 Share Posted December 22, 2009 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. Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 23, 2009 Author Share Posted December 23, 2009 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)"; Quote Link to comment Share on other sites More sharing options...
fenway Posted December 23, 2009 Share Posted December 23, 2009 So what doesn't work now? I'm lost. Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 24, 2009 Author Share Posted December 24, 2009 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted December 27, 2009 Share Posted December 27, 2009 Can't you just see if the column value changed? Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 27, 2009 Author Share Posted December 27, 2009 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! Quote Link to comment Share on other sites More sharing options...
fenway Posted December 28, 2009 Share Posted December 28, 2009 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. Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 29, 2009 Author Share Posted December 29, 2009 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 29, 2009 Share Posted December 29, 2009 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. Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 29, 2009 Author Share Posted December 29, 2009 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 29, 2009 Share Posted December 29, 2009 I guess the question I have is why you care if it updates "nothing". Quote Link to comment Share on other sites More sharing options...
bacarudaguy Posted December 29, 2009 Author Share Posted December 29, 2009 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 29, 2009 Share Posted December 29, 2009 Yup. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.