Yup, Fernando is correct. But it is not incorrect to specify a NULL value in your query for an autoincrement column. Right out of the MySQL manual: "If you insert NULL into an AUTO_INCREMENT column, the next number in the sequence is inserted." Although the MySQL manual implies it by giving examples where rows are inserted into tables with an autoincrement column, I could not find a spot where it explicitly states that you don't have to provide a value for that column in an INSERT query. Probably just falls into the personal preference category. -----Original Message----- From: Fernando Soto [mailto:fsoto@ocaso.com.mx] Sent: Friday, September 05, 2003 4:00 PM To: jroth1@telus.net; php-db@lists.php.net Subject: Re: Modify MySQL Record When you have an auntoincremente field in your table, you don't have to especify the field name in the query, as the following: $query = "INSERT INTO cnrl_db (kwo, lsd, date, well, field, uni, license, formation, perfs, event, fluid, mode, type, vhd, file, kb, grd, open, sour, tube, landed, casing, landed2, shut_date, shut_time, pres, tag) VALUES ('$kwo', '$lsd', '$date', '$well', '$field', '$uni', '$license', '$formation', '$perfs', '$event', '$fluid', '$mode', '$type', '$vhd', '$file', '$kb', '$grd', '$open', '$sour', '$tube', '$landed', '$casing', '$landed2', '$shut_date', '$shut_time', '$pres', '$tag')"; hope it helps eLFeR..... -------Original Message------- From: Jeff <mailto:jroth1@telus.net> Date: Viernes, 05 de Septiembre de 2003 01:15:21 p.m. To: php-db@lists.php.net <mailto:php-db@lists.php.net> Subject: Re: Modify MySQL Record Ok, so I added what you suggested, but it still doesn't assign any numbers to the records. $query = "INSERT INTO cnrl_db (gradient, kwo, lsd, date, well, field, uni, license, formation, perfs, event, fluid, mode, type, vhd, file, kb, grd, open, sour, tube, landed, casing, landed2, shut_date, shut_time, pres, tag) VALUES ('NULL', '$kwo', '$lsd', '$date', '$well', '$field', '$uni', '$license', '$formation', '$perfs', '$event', '$fluid', '$mode', '$type', '$vhd', '$file', '$kb', '$grd', '$open', '$sour', '$tube', '$landed', '$casing', '$landed2', '$shut_date', '$shut_time', '$pres', '$tag')"; $result = mysql_query($query, $link_id) OR die(mysql_error()); gradient - the auto increment field. "Richard Hutchins" < Richard.Hutchins@Getingeusa.com <mailto:Richard.Hutchins@Getingeusa.com> > wrote in message 1EA7D3AE70ACD511BE6D006097A78C1E033C92B8@USROCEXC">news:1EA7D3AE70ACD511BE6D006097A78C1E033C92B8@USROCEXC... > Jeff, > > When you want to increment an autoincrement field, you just put NULL into > the query. For example: > > INSERT (NULL,'Jeff','php list','Modify MySQL Record') INTO mail; > > Where NULL is in the same spot in the field list as your autoincrement > field. > > That concept is covered in the MySQL manual. Pretty simple stuff. If you're > in a situation where you have an autoincrement field that you sometimes > autoincrement and sometimes manually assign a value to, I think you're > misusing the autoincrement field (IMHO). If you have that situation, use the > autoincrement field to assign a unique ID to the table row and use a > separate field with another name for the manually assigned data. > > As far as the stuff you mentioned about clicking things and getting the > messages you expected, I'm kinda' lost. But if you use the autoincrement > field as it is intended, the logic should present itself. > > > -----Original Message----- > > From: Jeff [ mailto:jroth1@telus.net <mailto:jroth1@telus.net> ] > > Sent: Friday, September 05, 2003 2:07 PM > > To: php-db@lists.php.net <mailto:php-db@lists.php.net> > > Subject: Re: Modify MySQL Record > > > > > > Ok, so it sorta works now *grin* > > > > Two new problems > > > > I added an auto increment field in my table and changed the > > php to reflect > > that field - But for some reason, it doesn't work.. > > When I submit a new record, do I need to tell SQL to add one to the > > greatest, or should it do it automatically when a new record > > is created? > > gradient MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT, > > and I made it the primary. > > > > Problem two, > > > > If I manually enter a number into that field, I can make > > changes to the > > record no problem! but... it goes into some kind of > > continuous loop. It > > comes up with a window that says what I told it to say -- > > user_message("All > > records regarding $kwo have been changed!", > > > > "$PHP_SELF?action=view_record&gradient=$new_gradient"); -- > > I hit OK and it jumps to -- if(empty($kwo)) error_message('Empty > > Gradient!!'); -- if you hit ok again, it just loops. > > > > This might be solved as soon as I get that one field to auto > > populate. :P > > > > > > "Richard Hutchins" < Richard.Hutchins@Getingeusa.com <mailto:Richard.Hutchins@Getingeusa.com> > wrote in message > > 1EA7D3AE70ACD511BE6D006097A78C1E033C92B2@USROCEXC">news:1EA7D3AE70ACD511BE6D006097A78C1E033C92B2@USROCEXC... > > > Jeff, > > > > > > No problem. Happens to everyone, trust me. > > > > > > Anyway, I see you're using mysql_affected_rows($link_id) to > > get see how > > many > > > rows were affected by the UPDATE. Try getting rid of the > > $link_id in the > > > declaration and just use mysql_affected_rows(). Personally, > > I have always > > > had trouble when specifying a resource in a mysql function > > like this (it's > > > probably just me). But, the mysql_affected_rows() function will, by > > default, > > > use the last connection used by your script. > > > > > > Also, you're checking to see if it's not set e.g.,: > > > > > > > > > > $num_rows = mysql_affected_rows($link_id); > > > > > > > if(!$num_rows) error_message("Nothing changed!"); > > > > > > According to the documentation, mysql_affected_rows() > > returns a -1 if the > > > query failed. And I'm pretty sure it returns 0 if your query was > > successful > > > but didn't affect any rows. I'm pretty sure ! checks for > > FALSE which is > > not > > > one of mysql_affected_rows() valid return values. So your > > check isn't > > doing > > > what you think it should. > > > > > > Give that a shot and see if it helps out at all. > > > > > > Good luck. > > > > > > Rich > > > > > > > -----Original Message----- > > > > From: Jeff [ mailto:jroth1@telus.net <mailto:jroth1@telus.net> ] > > > > Sent: Friday, September 05, 2003 12:44 PM > > > > To: php-db@lists.php.net <mailto:php-db@lists.php.net> > > > > Subject: Re: Modify MySQL Record > > > > > > > > > > > > K, my tired eyes last night missed a few stupid mistakes... > > > > > > > > First: The Commas > > > > Second: I removed the period form the first line of the array > > > > -- $field_str > > > > = " kwo = '$kwo', "; > > > > Third: two typos. > > > > > > > > *hangs head in shame* > > > > > > > > Sorry for bothering everyone. :P > > > > > > > > BUT! I'm still having a problem - when I try to submit the > > > > change it comes > > > > back and says "Nothing Changed!" even if I change every field. > > > > > > > > Gonna do some more hunting - I'm doubting myself now. ;) > > > > > > > > > > > > > > > > > > > > "Richard Hutchins" < Richard.Hutchins@Getingeusa.com <mailto:Richard.Hutchins@Getingeusa.com> > > > wrote in message > > > > 1EA7D3AE70ACD511BE6D006097A78C1E033C92B1@USROCEXC">news:1EA7D3AE70ACD511BE6D006097A78C1E033C92B1@USROCEXC... > > > > > I'd suggest you first output your SQL statement to the > > > > browser to see > > > > > exactly what it's sending to the database. Might give you > > > > the answer right > > > > > there. > > > > > > > > > > > -----Original Message----- > > > > > > From: Jeff [ mailto:jroth1@telus.net <mailto:jroth1@telus.net> ] > > > > > > Sent: Friday, September 05, 2003 12:35 PM > > > > > > To: php-db@lists.php.net <mailto:php-db@lists.php.net> > > > > > > Subject: Re: Modify MySQL Record > > > > > > > > > > > > > > > > > > Yah, I noticed that after I had submitted this, and fixed it > > > > > > - still no > > > > > > change - gives the same exact error. > > > > > > > > > > > > "Jeffrey N Dyke" < jeffrey_n_Dyke@Keane.com <mailto:jeffrey_n_Dyke@Keane.com> > wrote in message > > > > > > OFFEE2B14B.9BAC564B-ON85256D98.005AAEE6@keane.com">news:OFFEE2B14B.9BAC564B-ON85256D98.005AAEE6@keane.com <OFFEE2B14B.9BAC564B-ON85256D98.005AAEE6@keane.com">mailto:news:OFFEE2B14B.9BAC564B-ON85256D98.005AAEE6@keane.com> ... > > > > > > > > > > > > > > looks like you stopped putting commas after your fields at > > > > > > > $field_str .= " formation = '$formation' "; > > > > > > > $field_str .= " perfs = '$perfs' "; > > > > > > > $field_str .= " event = '$event' "; > > > > > > > $field_str .= " fluid = '$fluid' "; > > > > > > > > > > > > > > hth > > > > > > > Jeff > > > > > > > > > > > > > > > > > > > > > > > > > > > > "Jeff" > > > > > > > < jroth1@telus.net <mailto:jroth1@telus.net> To: > > > > > > php-db@lists.php.net <mailto:php-db@lists.php.net> > > > > > > > > cc: > > > > > > > Subject: > > > > > > Modify > > > > > > MySQL Record > > > > > > > 09/05/2003 12:28 > > > > > > > PM > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Helloooo! > > > > > > > > > > > > > > I'm having a small problem with modifying the data in a > > > > MySQL DB. > > > > > > > > > > > > > > And here's the error: > > > > > > > > > > > > > > (After I click the "Submit Changes" button) Error: 1064: > > > > > > You have an error > > > > > > > in your SQL syntax near '(10-10-10-10)' kwo='1235', > > > > > > lsd='10-10-10-10', > > > > > > > date='2003-05-05', well' at line 1 > > > > > > > > > > > > > > Here's the code: > > > > > > > > > > > > > > function edit_record() { > > > > > > > global $default_dbname, $gradient_tablename, > > > > > > $access_log_tablename; > > > > > > > global $new_lsd, $kwo, $lsd, $date, $well, $field, > > > > > > > $uni, $license, $formation, $perfs, $event, > > $fluid, $mode, > > > > > > > $type, $vhd, $file, $kb, $grd, $open, $sour, $tube, > > > > > > > $landed, $casing, $landed2, $shut_date, $shut_time, > > > > $pres, $tag; > > > > > > > > > > > > > > if(empty($kwo)) error_message('Empty Gradient!'); > > > > > > > > > > > > > > $link_id = db_connect($default_dbname); > > > > > > > if(!$link_id) error_message(sql_error()); > > > > > > > > > > > > > > $field_str = ''; > > > > > > > if($kwo != $new_kwo) $field_str = " kwo = '$new_kwo', "; > > > > > > > if(!empty($lsd)) { > > > > > > > $field_str .= " lsd = other_lsd('$lsd') "; > > > > > > > } > > > > > > > > > > > > > > $field_str .= " kwo = '$kwo', "; > > > > > > > $field_str .= " lsd = '$lsd', "; > > > > > > > $field_str .= " date = '$date', "; > > > > > > > $field_str .= " well = '$well', "; > > > > > > > $field_str .= " field = '$field', "; > > > > > > > $field_str .= " uni = '$uni', "; > > > > > > > $field_str .= " license = '$license', "; > > > > > > > $field_str .= " formation = '$formation' "; > > > > > > > $field_str .= " perfs = '$perfs' "; > > > > > > > $field_str .= " event = '$event' "; > > > > > > > $field_str .= " fluid = '$fluid' "; > > > > > > > $field_str .= " mode = '$mode' "; > > > > > > > $field_str .= " type = '$type' "; > > > > > > > $field_str .= " vhd = '$vhd' "; > > > > > > > $field_str .= " file = '$file' "; > > > > > > > $field_str .= " kb = '$kb' "; > > > > > > > $field_str .= " grd = '$grd' "; > > > > > > > $field_str .= " open = '$open' "; > > > > > > > $field_str .= " sour = '$open' "; > > > > > > > $field_str .= " tubing = '$tubing' "; > > > > > > > $field_str .= " landed = '$landed' "; > > > > > > > $field_str .= " casing = '$casing' "; > > > > > > > $field_str .= " landed2 = '$landed2' "; > > > > > > > $field_str .= " shut_date = '$shut_date' "; > > > > > > > $field_str .= " sut_time = '$shut_time' "; > > > > > > > $field_str .= " pres = '$pres' "; > > > > > > > $field_str .= " tag = '$tag' "; > > > > > > > > > > > > > > > > > > > > > $query = "UPDATE $gradient_tablename SET $field_str WHERE > > > > > > kwo = '$kwo'"; > > > > > > > > > > > > > > $result = mysql_query($query); > > > > > > > if(!$result) error_message(sql_error()); > > > > > > > > > > > > > > $num_rows = mysql_affected_rows($link_id); > > > > > > > if(!$num_rows) error_message("Nothing changed!"); > > > > > > > if($lsd != $new_kwo) { > > > > > > > $query = "UPDATE $access_log_tablename SET kwo = > > > > > > '$new_kwo' WHERE kwo > > > > > > = > > > > > > > '$kwo'"; > > > > > > > $result = mysql_query($query); > > > > > > > if(!$result) error_message(sql_error()); > > > > > > > > > > > > > > user_message("All records regarding $lsd have been > > > > changed!", > > > > > > > > > "$PHP_SELF?action=view_record&kwo=$new_kwo"); > > > > > > > } > > > > > > > else { > > > > > > > user_message("All records regarding $lsd have been > > > > changed!"); > > > > > > > } > > > > > > > } > > > > > > > > > > > > > > -- > > > > > > > PHP Database Mailing List ( http://www.php.net/ <http://www.php.net/> ) > > > > > > > To unsubscribe, visit: http://www.php.net/unsub.php <http://www.php.net/unsub.php> > > > > > > > > > > > > -- > > > > > > PHP Database Mailing List ( http://www.php.net/ <http://www.php.net/> ) > > > > > > To unsubscribe, visit: http://www.php.net/unsub.php <http://www.php.net/unsub.php> > > > > > > > > > > > > > > -- > > > > PHP Database Mailing List ( http://www.php.net/ <http://www.php.net/> ) > > > > To unsubscribe, visit: http://www.php.net/unsub.php <http://www.php.net/unsub.php> > > > > > > > > -- > > PHP Database Mailing List ( http://www.php.net/ <http://www.php.net/> ) > > To unsubscribe, visit: http://www.php.net/unsub.php <http://www.php.net/unsub.php> > > -- PHP Database Mailing List ( http://www.php.net/ <http://www.php.net/> ) To unsubscribe, visit: http://www.php.net/unsub.php <http://www.php.net/unsub.php> . ____________________________________________________ <http://www.incredimail.com/redir.asp?ad_id=309&lang=9> IncrediMail - Email has finally evolved - <http://www.incredimail.com/redir.asp?ad_id=309&lang=9> Click Here
