Welcome to WebmasterWorld Guest from 54.221.28.179

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

sql using update - need help

     

weddingm

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!

cffrost2

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?

weddingm

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

weddingm

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.

rocknbil

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');

eelixduppy

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. \")

weddingm

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?

eelixduppy

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.

weddingm

1:25 pm on Apr 11, 2012 (gmt 0)

5+ Year Member



Thanks, I will try as above!

rocknbil

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.

hoangvu

7:52 am on Apr 12, 2012 (gmt 0)



using slash or regex for best solution
 

Featured Threads

Hot Threads This Week

Hot Threads This Month