[PHP-DB] UPDATE query
I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] UPDATE query
On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE query
I was wondering this Bastien. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Tuesday, March 08, 2011 11:20 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] UPDATE query On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE query
I found a way to do this Bastien: UPDATE `database`.`table` SET `toll_free` = CONCAT( '1-', `toll_free` ) WHERE `toll_free` LIKE '866-%' Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Tuesday, March 08, 2011 11:20 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] UPDATE query On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update Query
Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query
How do you know it failed? What is the mysql_error() returning to you? I'd also recommend you echo out the query to the browser window before sending it so you can see exactly what's being sent to the database. That'll help you troubleshoot SQL problems on your own. -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update Query Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- 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
RE: [PHP-DB] Update Query
I did that below is the exact excerpt from the source of the web browser !--insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=10-- The My Sql Error is Query failed : You have an error in your SQL syntax near '' at line 1 I have tried entering () around the select statement but that seamed to make more errors Thanks Aaron -Original Message- From: Hutchins, Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:07 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Update Query How do you know it failed? What is the mysql_error() returning to you? I'd also recommend you echo out the query to the browser window before sending it so you can see exactly what's being sent to the database. That'll help you troubleshoot SQL problems on your own. -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update Query Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query
Aaron, I'll tell you right now that I don't have ANY experience creating temporary tables. None. Haven't needed to go down that road yet. So, in the true spirit of the learning process, I'm going to do what anybody else would (and should) do: Take a wild guess. And here it is: I think your process for creating your temporary table is flawed. I've looked through the MySQL manual and found this: 6.5.3 CREATE TABLE Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] So, without a really good understanding of what you're trying to select into the temporary table, your query might look like: CREATE TEMPORARY TABLE $tbl_temp SELECT distinct(company_id) from Associations where product_id=9; Then you can do whatever query you need to against the temporary table then, when you're done, you'll drop the temporary table. Your MySQL error is probably occurring because there is no table on which to perform the query you're sending to the database. I'm positive there are others on this list with more experience using temporary tables than I, but this might be a push in the right direction (I hope). Hope this helps. Rich -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:18 PM To: [EMAIL PROTECTED] Subject: RE: [PHP-DB] Update Query I did that below is the exact excerpt from the source of the web browser !--insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=10-- The My Sql Error is Query failed : You have an error in your SQL syntax near '' at line 1 I have tried entering () around the select statement but that seamed to make more errors Thanks Aaron -Original Message- From: Hutchins, Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:07 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Update Query How do you know it failed? What is the mysql_error() returning to you? I'd also recommend you echo out the query to the browser window before sending it so you can see exactly what's being sent to the database. That'll help you troubleshoot SQL problems on your own. -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update Query Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- 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 -- 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
[PHP-DB] UPDATE Query
Hi, I have (amogst others) three tables in my database named Bookings, User, and Representative. A User and a Representative are different types of user, however I now want to merge these tables into one - User. When a Booking is made, the User_ID and the Rep_ID are stored in the Booking table. I have now merged the two tables (User and Representative), how can I update the Rep_ID column in Bookings so that it refers to the User_ID for each Representative in the User table? Thanks for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE query
$query1 = UPDATE news,autori SET news.titolo='$titolo', news.testo='$testo', news.data='$data', news.nome='$nome', autori.mail='$mail' WHERE news.nome = autori.nome AND id='$id'; You can't do an update across tables the last time I checked. Even if it's possible in newer versions, using mysql_error() after your query will tell you what the error is. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP-DB] UPDATE query
Can someone please unsubscribe/boot the ydnarb.com address below? Every time I send a message to the list I receive a blank reply from the address. I assume this is happening for others, also. ---John Holmes... - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:32 PM Subject: Re: Re: [PHP-DB] UPDATE query -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update Query Help...
I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
$tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Try: $update = UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query Help...
is it a mysql field type of datetime? if so, you may want to use this instead $timestamp=date(Y-m-j H:i:s); $update = UPDATE accounts SET atime='$timestamp' WHERE id-sys='$tmp'; that puts the current timestamp in mysql format for entry into your table. Cheers Jason -Original Message- From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:35 PM To: '[EMAIL PROTECTED]' Subject: [PHP-DB] Update Query Help... I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- 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
Re: [PHP-DB] Update Query Help...
SCOTT, I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. NOW() is a function and should not be part of a quoted string. =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
SCOTT, The list's crystal ball filter is down for maintenance. Show us the tbl schema and the debug print of $update. Then we won't be firing blind! =dn I understand that the column name does not normally need quotes, but without quotes on the column name I get a mysql error message about no column named 'id'. Unfortunately, I cannot even get the UPDATE statement working outside of PHP through an direct SQL statement. Everything looks like it should work, but the affected columns is always zero, and obviously the atime column is not getting the timestamp. This is quite frustrating. Thanks again for the help. Hopefully someone will come up with something to help me get this working. -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Try: $update = UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- 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
RE: [PHP-DB] Update Query Help...
I have definitely isolated the problem. If I change the column name that the match is being performed on from 'id-sys' to 'id_sys' I can execute the query without having to worry about quoting the column name and everything works. I think that I am just going to leave the column name changed and take this as a lesson never to use '-' in a column name again. Thanks again for the help. Maybe we all learned something from this. -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 2:00 PM To: NIPP, SCOTT V (SBCSI); '1LT John W. Holmes'; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... SCOTT, The list's crystal ball filter is down for maintenance. Show us the tbl schema and the debug print of $update. Then we won't be firing blind! =dn I understand that the column name does not normally need quotes, but without quotes on the column name I get a mysql error message about no column named 'id'. Unfortunately, I cannot even get the UPDATE statement working outside of PHP through an direct SQL statement. Everything looks like it should work, but the affected columns is always zero, and obviously the atime column is not getting the timestamp. This is quite frustrating. Thanks again for the help. Hopefully someone will come up with something to help me get this working. -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Try: $update = UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- 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
Re: [PHP-DB] Update Query Help...
I have definitely isolated the problem. If I change the column name that the match is being performed on from 'id-sys' to 'id_sys' I can execute the query without having to worry about quoting the column name and everything works. I think that I am just going to leave the column name changed and take this as a lesson never to use '-' in a column name again. Thanks again for the help. Maybe we all learned something from this. If you want to use a column name like id-sys, which you shouldn't, though, as you've learned, you can enclose it in backticks, like `id-sys`. Note that those are not single quotes, they are backticks, to the left of the number 1 key. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] UPDATE query to add to a field
hello all: I need to be able to update a field in MySQL, the catch is that I have to add on to text that is already there and I have to be able to do it within MySQL (phpMyAdmin). My first idea was to do: SET prod_desc = prod_desc + more info to tack on to end but this is a mathematical function which returns 0! Any ideas? Thanks Make it idiot-proof and someone will make a better idiot. - Do You Yahoo!? Yahoo! Auctions Great stuff seeking new owners! Bid now!
Re: [PHP-DB] UPDATE query to add to a field
At 9:06 -0800 1/28/02, Adv. Systems Design wrote: hello all: I need to be able to update a field in MySQL, the catch is that I have to add on to text that is already there and I have to be able to do it within MySQL (phpMyAdmin). My first idea was to do: SET prod_desc = prod_desc + more info to tack on to end but this is a mathematical function which returns 0! Use CONCAT(). Any ideas? Thanks -- PHP Database 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-DB] UPDATE query to add to a field
SET prod_desc = prod_desc + more info to tack on to end but this is a mathematical function which returns 0! update table set field = concat(field, more info) where id = 1; find the string functions in the mysql manual... with kind regards, Joffrey van Wageningen -- PHP Database 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-DB] UPDATE query to add to a field
Hello Adv. Systems Design, I need to be able to update a field in MySQL, the catch is that I have to add on to text that is already there and I have to be able to do it within MySQL (phpMyAdmin). My first idea was to do: SET prod_desc = prod_desc + more info to tack on to end but this is a mathematical function which returns 0! Any ideas? SET prod_desc = CONCAT( prod_desc, more info to tack on to end ) Manual: 6.3.2 String Functions Regards, =dn -- PHP Database 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-DB] Update Query?
Hi, The query code seems fine to me - did you try to otput $query to see if all the variables you are using are OK. May be $order,$stationid or $orderNewsID is misspeled or is not set properly. HTH Dobromir -Original Message- From: Jay Paulson [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Tuesday, October 02, 2001 2:22 AM Subject: [PHP-DB] Update Query? Hello- I'm trying to run an update query in an application I'm making. I keep getting a MySQL error however when I try and print out the mysql_error() nothing is returned. I run the same query in phpMyAdmin and the query runs fine. Below is the update query I'm running and the php code that I'm using to build it. Any suggestions? update news set newsOrder=2 where station_id=5 and id=3 $query = update news set newsOrder=.$order. where station_id=$stationid and id=.$orderNewsID; thanks, jay -- PHP Database 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 Database 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-DB] Update Query?
Hello- I'm trying to run an update query in an application I'm making. I keep getting a MySQL error however when I try and print out the mysql_error() nothing is returned. I run the same query in phpMyAdmin and the query runs fine. Below is the update query I'm running and the php code that I'm using to build it. Any suggestions? update news set newsOrder=2 where station_id=5 and id=3 $query = update news set newsOrder=.$order. where station_id=$stationid and id=.$orderNewsID; thanks, jay -- PHP Database 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]
