Re: [PHP-DB] Modify MySQL Record
When you have an auntoincremente field inyour table, you don't have toespecify 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 Date: Viernes, 05 de Septiembre de 2003 01:15:21 p.m. To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]] Sent: Friday, September 05, 2003 2:07 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] 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="" -- 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]] Sent: Friday, September 05, 2003 12:44 PM To: [EMAIL PROTECTED] Subject:
RE: [PHP-DB] Modify MySQL Record
That's ok Richard,I posted the message'cause i haven't seen yours. As a matter of fact. I'm really new to MySQL, my DB background is MSSQL, so, in the MSSQLBooks Online I found this: A. Use the IDENTITY property with CREATE TABLE This example creates a new table using the IDENTITY property for an automatically incrementing identification number.USE pubs IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'new_employees') DROP TABLE new_employees GO CREATE TABLE new_employees ( id_num int IDENTITY(1,1), fname varchar (20), minit char(1), lname varchar(30) ) INSERT new_employees (fname, minit, lname) VALUES ('Karin', 'F', 'Josephs') INSERT new_employees (fname, minit, lname) VALUES ('Pirkko', 'O', 'Koskitalo') The only one thing i have to do was to try it in MySQL and fortunately it works. Anyway, Jeff have 2 options to select, right?... eLFeR ---Original Message--- From: Hutchins, Richard Date: Viernes, 05 de Septiembre de 2003 02:19:23 p.m. To: [EMAIL PROTECTED] Subject: RE: [PHP-DB] Modify MySQL Record 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:[EMAIL PROTECTED]] Sent: Friday, September 05, 2003 4:00 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP-DB] 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:[EMAIL PROTECTED] Date: Viernes, 05 de Septiembre de 2003 01:15:21 p.m. To: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Subject: Re: [PHP-DB] 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" [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Friday, September 05, 2003 2:07 PM To: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Subject: Re: [PHP-DB] 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
Re: [PHP-DB] DISTINCT not working
I havelookingthe MySQL manual for"distinctrow" andI didn't findit, i think you should change for"distinct" $query="CREATE TABLE IF NOT EXISTS temp3TEMP SELECT DISTINCT ROUND($tbl2.Net*.175,2) AS ExpNet175, CONCAT($tbl2.Journal,$tbl2.Description) AS CON175 FROM $tbl2 WHERE $tbl2.Dept='$rcc' AND $tbl2.Net0"; Hope it helps eLFeR... ---Original Message--- From: Dillon, John Date: Jueves, 21 de Agosto de 2003 11:03:51 a.m. To: [EMAIL PROTECTED] Subject: [PHP-DB] DISTINCT not working Any ideas on why the DISTINCT would not work in the following testing code: $query="CREATE TABLE IF NOT EXISTS temp3TEMP SELECT DISTINCTROW ROUND($tbl2.Net*.175,2) AS ExpNet175, CONCAT($tbl2.Journal,$tbl2.Description) AS CON175 FROM $tbl2 WHERE $tbl2.Dept='$rcc' AND $tbl2.Net0"; $query="CREATE TABLE IF NOT EXISTS temp3 SELECT DISTINCTROW temp3TEMP.ExpNet175, LEFT(temp3TEMP.CON175,2048) AS CON175 FROM temp3TEMP ORDER BY temp3TEMP.CON175"; John http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php . IncrediMail - Email has finally evolved - Click Here
RE: [PHP-DB] Sorting issue
This would happens ifyou have defined the data type of the columns as char or varchar. ---Original Message--- From: Aaron Wolski Date: Viernes, 08 de Agosto de 2003 08:51:04 a.m. To: 'Jeff'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Sorting issue Seeing code might help us. Aaron -Original Message- From: Jeff [mailto:[EMAIL PROTECTED]] Sent: August 8, 2003 10:44 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Sorting issue Why would a mysql db think that 55 was greater than 14000. I have several headings that display sql data under them, when you click on the heading, it sorts that section (descending/ascending), but it only seems to be looking at the first two numbers in the one heading. Is there a command that forces it to look at the entire record before sorting it? Thanks Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php . IncrediMail - Email has finally evolved - Click Here
Re: [PHP-DB] Sorting issue
You have to change kwo VARCHAR(10) NOT NULL, for kwoint(10) NOT NULL, Hope this helps Fernando Soto From: Jeff Date: Viernes, 08 de Agosto de 2003 10:36:33 a.m. To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Sorting issue Yes duh... I didn't realize numbers weren't considered characters. What's the sql entry for numeric characters? I'm new at this, and figure I'm not doing to bad so far. . "John W. Holmes" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Jeff wrote: kwo VARCHAR(10) NOT NULL, lsd VARCHAR(20) NOT NULL, duh -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php . IncrediMail - Email has finally evolved - Click Here