mrhenniger Posted June 24, 2011 Share Posted June 24, 2011 I have a table with this structure... int Serial int Ordr int AirframeSN int AirframeVariantSN bigint DateStart varchar DateStartDesig varchar DateStartType bigint DateEnd varchar DateEndDesig varchar DateEndType varchar Ownership int LocSN int LocOrdr varchar MilitarySerial int MilitaryServiceSN varchar NewConstructionNumber varchar CivilRegistration varchar CivilRegistrationNotes varchar Markings int PhotoSN int EventTypeSN varchar Notes varchar SourceDesignation int SourceUserSN varchar Help bigint DateApproved int InsertOrdr varchar InsertNotes varchar Private I am trying to execute this command... UPDATE AirframeEvent SET Ordr=2, AirframeSN=12, AirframeVariantSN=0, DateStart=-820526400, DateStartDesig='Y', DateStartType='C', DateEnd=0, DateEndDesig='', DateEndType='', Ownership='', LocSN=0, LocOrdr=0, MilitarySN='44-73463', MilitaryServiceSN=2, NewConstructionNumber='', CivilRegistration='', CivilRegistrationNotes='', Markings='', PhotoSN=0, EventTypeSN=3, Notes='', SourceDesignation='', SourceUserSN=1, Help='', DateApproved=1238087851, InsertOrdr=0, InsertNotes='', Private='' WHERE Serial=166 LIMIT 1 Unfortunately mysql_query is returning FALSE. I confirmed the user is good for both reads and writes and the password is correct. I don't have much experience with multiple column UPDATEs. I am sure it is a simple syntax thing, but with all of the examples I have Googled I can't see what I am doing wrong. Can someone point me towards the error. Thanks in advance for any help! Mike Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 24, 2011 Share Posted June 24, 2011 If you use mysql_error in the error checking and error reporting logic in your code, mysql will tell you at what point it found a problem in the query. The only apparent thing I see is that your DateStart value is a negative number. Quote Link to comment Share on other sites More sharing options...
mrhenniger Posted June 24, 2011 Author Share Posted June 24, 2011 I should have mentioned that I have it set-up to spit out the mysql_error() result. It is giving nothing... nada... blank. Not much help there. PHP handles negative int's as dates just fine. They represent dates before Jan 1, 1970. Does MySQL not like negative values for BigInt? (I'll try and look that up) Mike Quote Link to comment Share on other sites More sharing options...
mrhenniger Posted June 24, 2011 Author Share Posted June 24, 2011 Just FYI... BIGINT A large integer. The signed range is -9223372036854775808 to 9223372036854775807. I got this from here... http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html I have a few thoughts about things to check out. Still if anyone has any ideas to share feel free. TIA Mike Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 24, 2011 Share Posted June 24, 2011 If mysql_query() returned a false, mysql_error() would tell you why. It is likely that your php code is not doing what you think it is or you are reusing variables and overwriting results. Posting the relevant php code would be the only way that someone could help you with what it is doing. If you are doing this query inside of a loop, posting all the code involved, starting with the outermost loop, would be needed. Quote Link to comment Share on other sites More sharing options...
mrhenniger Posted June 24, 2011 Author Share Posted June 24, 2011 I see where you are going with this. It gave me something to think about. I think I still have a problem with MySQL (asked by provider for the version number, waiting to hear back), but let me make my case first. As you suspected I do have a set of scripts for executing MySQL queries. The scripts manage the databases, users and do some security checks, etc. Here is a support function in that script... function logExecutionQueryError($callingFunction, $command, $error) { global $user; if( $user IS AN ADMINISTRATOR* ) { $aLog = $callingFunction . " - ERROR - " . "Failed to execute [" . $command . "] failed with " . "Error [" . $error . "]"; logError($aLog); } else { logError("Unfortunately there was an error accessing the database. Please wait a few moments and retry. " . "If the issue persists please use the contact link to inform Aerial Visuals."); } } * NOT ACTUAL CODE The logError function is rock solid. It allows a queue of error messages to be stored until the end of script execution, at which time the script may choose to dump them for viewing. I use this for troubleshooting. I have been using it for years. No problems there. Here is a snipit of the function which does the actual query execution... ... $returnValue = mysql_query($command, $connSQL); if( $returnValue === FALSE ) { $error = mysql_error(); logExecutionQueryError("UtilitiesGeneral_DBs:executeQuery", $command, $error); ... I don't give any containers a chance to be stomped before the error gets logged. This is the same execution path for SELECTs, etc., which I don't have problems with. Using the example from before I get this in the log... UtilitiesGeneral_DBs:executeQuery - ERROR - Failed to execute [uPDATE AirframeEvent SET Ordr=2, AirframeSN=12, AirframeVariantSN=0, DateStart=-820526400, DateStartDesig='Y', DateStartType='C', DateEnd=0, DateEndDesig='', DateEndType='', Ownership='', LocSN=0, LocOrdr=0, MilitarySN='44-73463', MilitaryServiceSN=2, NewConstructionNumber='', CivilRegistration='', CivilRegistrationNotes='', Markings='', PhotoSN=0, EventTypeSN=3, Notes='', SourceDesignation='', SourceUserSN=1, Help='', DateApproved=1238087851, InsertOrdr=0, InsertNotes='', Private='' WHERE Serial=166 LIMIT 1] failed with Error [] ...and you can see there is no error logged. However, if I try to set just one column... UPDATE AirframeEvent SET Ordr="2" WHERE Serial=166 LIMIT 1 ...I get no execution failure and the result is applied to the value in the database. This of course is good, but it is only one column/field and I would like to set a number of them at one time. Getting back to my original statement in this post I am thinking it has something to do with the syntax of my multiple-column update or one of the individual values. I am going to experiment with this. I'll post here if I find something. Again if anyone thinks of something first I would love to read about it. Thanks again for your help. I appreciate having someplace to use as a sounding board in this solo project of mine. Mike Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 24, 2011 Share Posted June 24, 2011 You are not using $connSQL in your mysql_error(.......) statement and are probably not getting the actual mysql_error information from the query that was just executed using the $connSQL connection. Use mysql_error($connSQL) to insure you are getting the last error from the correct connection that the query was executed on. Quote Link to comment Share on other sites More sharing options...
mrhenniger Posted June 25, 2011 Author Share Posted June 25, 2011 Well I learned something new today. Thanks! I added the resource to the mysql_error call, and I finally got the error I was looking for. It pointed me right to the bug. Thanks for the help! Mike 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.