MySQL UPDATE using PHP function call

Anon

Hi, I wonder if you can help?
I a newbie to PHP and I'm having a problem with running an update query which modifed a field via a function call.

The function strips leading "http://", trailing "index.htm(l)" etc from a URL - to tidy up entries in a links database. The function is:

function url_strip($arg_url)
.....

And works correctly when run against individual strings and updates of individual records.

However, when I use the following mysql call, it does not affect any change to the "url" field in the "links" table.

$result = mysql_db_query(reviews, "UPDATE links SET url = url_strip(url)");

Is this because functions cannot be applied withing the SQL statement and if this is the case, what would you recommend to perform this update?

Thanks.

Anon

You will have to read the value of the url column into a php variable first. Then, process it with the url_strip() function, thus:
$url = // get value of url column //

$cleanurl = url_strip($url);
$result = mysql_db_query(reviews, "UPDATE links SET url = $url");

🙂
t

Anon

Thanks for the quick reply but won't that update ALL records to have the same url field ($url) rather than run the url_strip function on each individual record?

IE.

Before

PKID URL
1 http://www.here.there/index.html
2 http://www.hi.there/
3 www.my.com/index.html

update links set url = $cleanurl
(assuming $cleanurl is the result of the url_strip() of the first record)

After
PKID URL
1 www.here.there
2 www.here.there
3 www.here.there

Anon

Sure will. Good call. Add "WHERE url = '$url'" to the end of it and that should update only the proper records, right? Or perhaps you have a unique key that you could use to reference which record should be updated.

I am assuming you are doing this inside some sort of loop to work with each record individually.

Anon

I see what you are getting at. Here I am trying to work harder not smarter.

Try this. (hope you are using test data)

$query="update links set url = substring_index((trim('http://' FROM url)),'/',1);"

See page 189 of the MySQL docs for details on these functions.

Anon

To quote:
"
I am assuming you are doing this inside some sort of loop to work with each record individually.
"

No, I am hoping to run an SQL query on the whole table (this is where our misunderstanding has come from I think 🙂

I was hoping that PHP functions can be used within the MySQL UPDATE query dataset RATHER than working on each entry individually by reading each row, processing and writing back.

I.e.: UPDATE table SET record = function(record)

Anon

Thanks, I'll give that a go, I guess the problem is the processing order of expanding the strip_url function applied to a field within the query.

Thanks for the tip on the substr stuff.