Welcome to WebmasterWorld Guest from 54.211.136.250

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

sql using update - need help

   
2:40 am on Apr 10, 2012 (gmt 0)

5+ Year Member



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!
12:22 pm on Apr 10, 2012 (gmt 0)

5+ Year Member



Should only need this

UPDATE table SET column = '$string'

What error are you getting?
1:27 pm on Apr 10, 2012 (gmt 0)

5+ Year Member



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

5+ Year Member



I am trying to update text within a field, not the whole field.
3:42 pm on Apr 10, 2012 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



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)

5+ Year Member



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)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



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.
1:25 pm on Apr 11, 2012 (gmt 0)

5+ Year Member



Thanks, I will try as above!
4:13 pm on Apr 11, 2012 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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