RE: [PHP] mysql update query
Hi Enda, Does your table have an index per record ? If so, your WHERE clause could just use the index and thus be far simpler, if A_Number is an indexed field, then UPDATE cdr200311 SET Price=0.225 WHERE A_Number = 61210146 Alternatively, try removing the () around the WHERE Darka www.xfcpro.com -Original Message- From: Enda [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 16:33 To: Php-General Subject: [PHP] mysql update query Hi guys Bit of a problem I'm having here with an update query on a PHP/MySQL site: This is the db query I execute: $result6=mysql_query(UPDATE $WorkTable SET Price=$CallCost WHERE (A_Number = $A_Number AND StartDateTime = $D_Full AND Duration=$Duration AND SMSDestinationName = '$SMSDestinationName')); which returns the following SQL query: UPDATE cdr200311 SET Price=0.225 WHERE (A_Number = 61210146 AND StartDateTime = 2003110216502400 AND Duration=15.0167 AND SMSDestinationName = '1010') The query executes successfully, but nothing happens to the database... all connections etc are in place. Am I right to have the brackets around the WHERE conditions? I have also tried this query in phpMyAdmin but no result... and yes, the record does exist.. Help! Thanks Enda -- -- Enda Nagle NightSol e: [EMAIL PROTECTED] w: www.nightsol.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql update query
Hello Enda, Friday, December 19, 2003, 4:32:44 PM, you wrote: E which returns the following SQL query: E UPDATE cdr200311 SET Price=0.225 WHERE (A_Number = 61210146 AND E StartDateTime = 2003110216502400 AND Duration=15.0167 AND SMSDestinationName E = '1010') E The query executes successfully, but nothing happens to the database... all E connections etc are in place. Have you tried placing quotes around the values? The timestamps should be ok, but the price with the decimal place will almost certainly need them. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql update query
Yes I would prefer to do this... problem is that the final table will be populated from a monthly csv file supplied from an external source. this file could have several hundred occurences of each value of A_Number and other fields so the only way to have it as unique is to use several of the fields in the recordset as the 'key'... I tried both suggestions - thanks, but still no luck. Any more ideas? I don't really want to have to go and rewrite the update to include a unique id for each record! Thanks guys for your help. Enda -- -Original Message- From: Jonathan Wilkes [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 16:39 To: Enda; Php-General Subject: RE: [PHP] mysql update query Hi Enda, Does your table have an index per record ? If so, your WHERE clause could just use the index and thus be far simpler, if A_Number is an indexed field, then UPDATE cdr200311 SET Price=0.225 WHERE A_Number = 61210146 Alternatively, try removing the () around the WHERE Darka www.xfcpro.com -Original Message- From: Enda [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 16:33 To: Php-General Subject: [PHP] mysql update query Hi guys Bit of a problem I'm having here with an update query on a PHP/MySQL site: This is the db query I execute: $result6=mysql_query(UPDATE $WorkTable SET Price=$CallCost WHERE (A_Number = $A_Number AND StartDateTime = $D_Full AND Duration=$Duration AND SMSDestinationName = '$SMSDestinationName')); which returns the following SQL query: UPDATE cdr200311 SET Price=0.225 WHERE (A_Number = 61210146 AND StartDateTime = 2003110216502400 AND Duration=15.0167 AND SMSDestinationName = '1010') The query executes successfully, but nothing happens to the database... all connections etc are in place. Am I right to have the brackets around the WHERE conditions? I have also tried this query in phpMyAdmin but no result... and yes, the record does exist.. Help! Thanks Enda -- -- Enda Nagle NightSol e: [EMAIL PROTECTED] w: www.nightsol.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql update query
I used single.. just deleted all records except 1 from the table and changed the query to have just one WHERE condition $result6=mysql_query(UPDATE $WorkTable SET Price=$CallCost WHERE A_Number = $A_Number); this worked... so looks like i'm going to have to rewrite the import bit! unless of course someone can come in with a last minute flash of inspiration! i really really don't need this at 5:12 on a friday afternoon! -Original Message- From: Jonathan Wilkes [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 17:00 To: Enda Subject: RE: [PHP] mysql update query when you added the quotes, did you specify single or double quotes ? -Original Message- From: Enda [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 16:52 To: Php-General Cc: Jonathan Wilkes Subject: RE: [PHP] mysql update query Yes I would prefer to do this... problem is that the final table will be populated from a monthly csv file supplied from an external source. this file could have several hundred occurences of each value of A_Number and other fields so the only way to have it as unique is to use several of the fields in the recordset as the 'key'... I tried both suggestions - thanks, but still no luck. Any more ideas? I don't really want to have to go and rewrite the update to include a unique id for each record! Thanks guys for your help. Enda -- -Original Message- From: Jonathan Wilkes [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 16:39 To: Enda; Php-General Subject: RE: [PHP] mysql update query Hi Enda, Does your table have an index per record ? If so, your WHERE clause could just use the index and thus be far simpler, if A_Number is an indexed field, then UPDATE cdr200311 SET Price=0.225 WHERE A_Number = 61210146 Alternatively, try removing the () around the WHERE Darka www.xfcpro.com -Original Message- From: Enda [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 16:33 To: Php-General Subject: [PHP] mysql update query Hi guys Bit of a problem I'm having here with an update query on a PHP/MySQL site: This is the db query I execute: $result6=mysql_query(UPDATE $WorkTable SET Price=$CallCost WHERE (A_Number = $A_Number AND StartDateTime = $D_Full AND Duration=$Duration AND SMSDestinationName = '$SMSDestinationName')); which returns the following SQL query: UPDATE cdr200311 SET Price=0.225 WHERE (A_Number = 61210146 AND StartDateTime = 2003110216502400 AND Duration=15.0167 AND SMSDestinationName = '1010') The query executes successfully, but nothing happens to the database... all connections etc are in place. Am I right to have the brackets around the WHERE conditions? I have also tried this query in phpMyAdmin but no result... and yes, the record does exist.. Help! Thanks Enda -- -- Enda Nagle NightSol e: [EMAIL PROTECTED] w: www.nightsol.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql update query
just had a thought, what happens when you have only 2 records in the database, does it work, is it possible that you may have had 2 identical records in the first instance, hence it not working ? ignore my idiotic comment, it's friday, it's late and I have 2 weeks off (only 10 minutes to go), so I wasn't really thinking when I typed it. Honestly, a unique index may be the best bet... -Original Message- From: Enda [mailto:[EMAIL PROTECTED] Sent: 19 December 2003 17:13 To: Php-General Subject: Re: [PHP] mysql update query I used single.. just deleted all records except 1 from the table and changed the query to have just one WHERE condition $result6=mysql_query(UPDATE $WorkTable SET Price=$CallCost WHERE A_Number = $A_Number); this worked... so looks like i'm going to have to rewrite the import bit! unless of course someone can come in with a last minute flash of inspiration! i really really don't need this at 5:12 on a friday afternoon! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql update query
Everything looks good to me...try this: $result = mysql_query(UPDATE table SET value1='$value1',value2='$value2' WHERE id='$id'); Although I doubt MySQL cares if certain words are capitalized. Good luck though, Tyler Longren - Original Message - From: Greg Sidelinger [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 04, 2001 9:27 PM Subject: [PHP] mysql update query I'm having trouble getting an update query to work Here is what I'm doing $result = mysql_query(update table set value1='$value1', value2='$value2' where id='$id'); It is not updating the database. All the $vars have values and I'm using the correct columns names. Could someone please point me in the correct direction because I have been trying different things for a while but can't seem to get it to work. Greg -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] mysql update query
are any of the fields of type number/integer/etc? if so, try removing the quotes for that field. -Original Message- From: Tyler Longren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 05, 2001 2:36 PM To: Greg Sidelinger; [EMAIL PROTECTED] Subject: Re: [PHP] mysql update query Everything looks good to me...try this: $result = mysql_query(UPDATE table SET value1='$value1',value2='$value2' WHERE id='$id'); Although I doubt MySQL cares if certain words are capitalized. Good luck though, Tyler Longren - Original Message - From: Greg Sidelinger [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 04, 2001 9:27 PM Subject: [PHP] mysql update query I'm having trouble getting an update query to work Here is what I'm doing $result = mysql_query(update table set value1='$value1', value2='$value2' where id='$id'); It is not updating the database. All the $vars have values and I'm using the correct columns names. Could someone please point me in the correct direction because I have been trying different things for a while but can't seem to get it to work. Greg -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] mysql update query
What error are you getting, exactly? MySQL is not case-sensitive with regards to commands, but it is case-sensitive for table names, column names, etc. Make sure that the table names are exactly exact ;-) including cases, etc. You might double-check the values in the variables: do they contain special characters (like ' or ) that need to be escaped out with addslashes()? Check the datatypes for the columns as well. If the datatype is a number, the single quote is not needed (and will, in fact, cause an error). I've had trouble in the past using variables with single quotes in the string and variable interpolation. In this sort of case I usually use: $query = stuff='.$stuff.'; instead of $query = stuff = '$stuff'; It's clunkier, but it might work. Give it a try as well. Ultimately, though, I suspect that it's your data that's causing the problems. At 07:36 PM 12/4/2001, Tyler Longren wrote: Everything looks good to me...try this: $result = mysql_query(UPDATE table SET value1='$value1',value2='$value2' WHERE id='$id'); Although I doubt MySQL cares if certain words are capitalized. Good luck though, Tyler Longren - Original Message - From: Greg Sidelinger [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 04, 2001 9:27 PM Subject: [PHP] mysql update query I'm having trouble getting an update query to work Here is what I'm doing $result = mysql_query(update table set value1='$value1', value2='$value2' where id='$id'); It is not updating the database. All the $vars have values and I'm using the correct columns names. Could someone please point me in the correct direction because I have been trying different things for a while but can't seem to get it to work. Greg -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] Sliante, Richard S. Crawford http://www.mossroot.com AIM: Buffalo2K ICQ: 11646404 Y!: rscrawford MSN: [EMAIL PROTECTED] It is only with the heart that we see rightly; what is essential is invisible to the eye. --Antoine de Saint Exupéry Push the button, Max! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] mysql update query
I concur Probably this will work $result = mysql_query(UPDATE table SET value1='$value1',value2='$value2' WHERE id=$id); remember integers id=1 are not strings id='1'; not the same! -Original Message- From: Martin Towell [mailto:[EMAIL PROTECTED]] Sent: December 5, 2001 11:37 AM To: 'Tyler Longren'; Greg Sidelinger; [EMAIL PROTECTED] Subject: RE: [PHP] mysql update query are any of the fields of type number/integer/etc? if so, try removing the quotes for that field. -Original Message- From: Tyler Longren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 05, 2001 2:36 PM To: Greg Sidelinger; [EMAIL PROTECTED] Subject: Re: [PHP] mysql update query Everything looks good to me...try this: $result = mysql_query(UPDATE table SET value1='$value1',value2='$value2' WHERE id='$id'); Although I doubt MySQL cares if certain words are capitalized. Good luck though, Tyler Longren - Original Message - From: Greg Sidelinger [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 04, 2001 9:27 PM Subject: [PHP] mysql update query I'm having trouble getting an update query to work Here is what I'm doing $result = mysql_query(update table set value1='$value1', value2='$value2' where id='$id'); It is not updating the database. All the $vars have values and I'm using the correct columns names. Could someone please point me in the correct direction because I have been trying different things for a while but can't seem to get it to work. Greg -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]