cffrost2
msg:4439142 | 12:22 pm on Apr 10, 2012 (gmt 0) |
Should only need this
UPDATE table SET column = '$string'
What error are you getting?
|
weddingm
msg:4439161 | 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
|
weddingm
msg:4439162 | 1:34 pm on Apr 10, 2012 (gmt 0) |
I am trying to update text within a field, not the whole field.
|
rocknbil
msg:4439219 | 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');
|
eelixduppy
msg:4439221 | 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. \")
|
weddingm
msg:4439408 | 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?
|
eelixduppy
msg:4439560 | 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("
UPDATE
`table`
SET
`column` = '%s'
WHERE
`column` = '%s'
",
mysql_real_escape_string($replacement),
mysql_real_escape_string($where)
);
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.
|
weddingm
msg:4439584 | 1:25 pm on Apr 11, 2012 (gmt 0) |
Thanks, I will try as above!
|
rocknbil
msg:4439661 | 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.
|
hoangvu
msg:4439911 | 7:52 am on Apr 12, 2012 (gmt 0) |
using slash or regex for best solution
|
|
