How to prevent replacing two values in the same field?

Can anyone tell me how to prevent开发者_开发百科 this PHP code replacing two values?

$sql2="UPDATE users SET urls = replace(urls, 'http://someurl.com', '') WHERE uname='$username'";
$result2=mysql_query($sql2);

For example, the field "urls" contains this:

http://someurl.com

http://someurl.com/page.html

Using the above code removes both URLs, leaving just the "/page.html".

How do I make sure it only deletes the value and doesn't remove anything else.

Hi Why not just do it all in a where clause?

UPDATE users SET urls='' where urls='http://someurl.com' and uname='$username'

Or

UPDATE users SET urls='' where urls not like '%.html' and uname='$username'

I'm sure you can work out something for your specific requirement

Sorry, ... @jamesKG - assuming joeys example your code would update nothing, @Widor - this is very particular, only works, if the url to be replaced is in the first line.

Joey, basically it's no recommended practice holding multiple values in one field ... unneccessary to say. by now you know yourself :-) Whatsoever, assuming your existing data structure better get the urls content first, replace it the way you like in PHP and update urls with your result afterwards. This should be much easier than by SQL, though i'm a fan of using database features. With PHP you can explode the urls by "\r\n" into single items and handle each one seperately.

Don't worry about an additional database access (if there's an index on username).

If the field actually contains the line break between those two urls then you could do something like

$sql2="UPDATE users SET urls = replace(urls, 'http://someurl.com\r\n', '') WHERE uname='$username'";

For records with only one URL, you could do it in a WHERE clause:

WHERE urls='http://someurl.com' and uname='$username'

Add a 'Limit' to your update clause.

UPDATE tbl
   SET urls = replace(urls, 'http://someurl.com', '')  
 WHERE uname = $username 
 ORDER BY uname, urls 
 LIMIT n

Use this if you have an update that COULD update multiple records, but you only want a single record modified.

I use an ORDER BY as a safety net, so that you can predict which record will be updated when you are attempting to debug your data or code.

Note: this kind of problem is usually a symptom of a weak architecture, where your data is not normalized enough or your primary key is not defined sufficiently. Ideally using your primary key in the where clause would allow you to specify a single record for the update.

-- J Jorgenson --