sql using update - need help

[weddingm]

I am trying to enter the following code but keeps erring out. Can anyone help?

UPDATE table set column= replace(column, ‘<a href="link1SqA&amp;offerid=90283.10000059&amp;type=4&amp;subid=0"><img src="link1SqA31.gif" border="0" alt="sample" />’, ‘<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>’);

Thanks!

[cffrost2]

Should only need this

UPDATE table SET column = '$string'

What error are you getting?

[weddingm]

#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]

I am trying to update text within a field, not the whole field.

[rocknbil]

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]

All quotes and special characters need to be escaped, as well, with a slash (e.g. \")

[weddingm]

I tried all these and still get the same error. Maybe it is erring out at the period?

[eelixduppy]

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&amp;offerid=90283.10000059&amp;type=4&amp;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]

Thanks, I will try as above!

[rocknbil]

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]

using slash or regex for best solution