Should only need this
UPDATE table SET column = '$string'
What error are you getting?
#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
I am trying to update text within a field, not the whole field.
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');
All quotes and special characters need to be escaped, as well, with a slash (e.g. \")
I tried all these and still get the same error. Maybe it is erring out at the period?
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.
Thanks, I will try as above!
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.
using slash or regex for best solution