homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

sql using update - need help

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

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




 12:22 pm on Apr 10, 2012 (gmt 0)

Should only need this

UPDATE table SET column = '$string'

What error are you getting?


 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


 1:34 pm on Apr 10, 2012 (gmt 0)

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


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


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


 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?


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


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

Thanks, I will try as above!


 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.


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

using slash or regex for best solution

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved