homepage Welcome to WebmasterWorld Guest from 54.161.236.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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
weddingm




msg:4438997
 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>);


Thanks!

 

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&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




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

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