| 12:22 pm on Apr 10, 2012 (gmt 0)|
Should only need this
UPDATE table SET column = '$string'
What error are you getting?
| 1:27 pm on Apr 10, 2012 (gmt 0)|
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'href="http://link/fs-bin/click?id=aaaaaaa&offerid=90283.100' at line 1
| 1:34 pm on Apr 10, 2012 (gmt 0)|
I am trying to update text within a field, not the whole field.
| 3:42 pm on Apr 10, 2012 (gmt 0)|
Make sure you're doing it according to the manual [dev.mysql.com] (looks like you are) but you may have made the mistake of naming a column a reserved word. "Column", along with date, table, etc. are reserved words for mySQL objects or functions.
This is where backticks save the day. NOT quotes. Backticks.
update table set `column`= replace(`column`, 'from string', 'to string');
| 3:49 pm on Apr 10, 2012 (gmt 0)|
All quotes and special characters need to be escaped, as well, with a slash (e.g. \")
| 2:34 am on Apr 11, 2012 (gmt 0)|
I tried all these and still get the same error. Maybe it is erring out at the period?
| 12:38 pm on Apr 11, 2012 (gmt 0)|
Let's take a step backwards.
Are you executing this query from a PHP script or directly in the database?
If PHP, it should look something like this:
$replacement = '<a href="http://link1SqA283.10000142&subid=0&type=4"><IMG border="0" alt="sample" src="http://link1SqA83.10000142&subid=0&type=4&gridnum=6"></a>';
$where = '<a href="link1SqA&offerid=90283.10000059&type=4&subid=0"> <img src="link1SqA31.gif" border="0" alt="sample" />';
$query = sprintf("
`column` = '%s'
`column` = '%s'
As I've shown, you set the column to what you want the new value to be, and you specify which rows should be updated based on the where condition.
Hope this is helpful.
| 1:25 pm on Apr 11, 2012 (gmt 0)|
Thanks, I will try as above!
| 4:13 pm on Apr 11, 2012 (gmt 0)|
AH here's something else. Look at the single quote marks in your original:
‘ <---- HERE and HERE-----> ’
What the heck are those? :-) Microsoft Word Smart Quotes? Something from a Mac Editor? In any case, look at the ones in eelixduppy's example:
' <---- plain old single quotes --> '
If the first is what your text editor is giving you when you edit PHP/mysql statements, find another text editor. Legal mySQL string delimiters (to my knowledge) are only plain single ' or double " quotes, smart quotes need not apply and will probably error.
While it's true data should be escaped, I don't think that's the problem. Technically this is a "legal" statement - the outer single quotes encapsulate the doubles,
insert into table (fld) values ('This is "my value" ');
and you have no single quotes inside. What myslq_escape_string does,
insert into table (fld) values ('This is "David\'s value" ');
.. so that the statement doesn't end at the first ' in the string and error after. invert that and use double quotes for the delimiters,
insert into table (fld) values ("This is \"my value\" ");
insert into table (fld) values ("This is \"David's value\" ");
myslq_escape_string and myslq_real_escape_string do other things that help prevent SQL injection as well, so you should always use one or the other - but in the context of your problem, I don't think that's it.
| 7:52 am on Apr 12, 2012 (gmt 0)|
using slash or regex for best solution