How to update each row of a MYSQL database with a new PHP variable?

hi,
i have a very large database with 50,000 hotels in it. each hotel has an 'establishment name' in the EstablishmentName column. i also have an empty column called urlSlug which i would like to populate with the 'slug' version of whatever the establishment name is.

e.g. 'the london hilton would become 'the-london-hilton'

i have a function which creates the slug and am trying to use it to populate the urlSlug column. however the code below is as far as i've got. it updates the urlSlug column but at the moment it's just putting the slug of whatever the first hotel it gets in as the slug for all the hotels.

so i basically have two problems:

1) how can i alter the code below to get the correct slug appearing in each row, and
2) having tried this so far with only the hotels in Andorra the whole thing took quite a long time and i'm worried that if i try and run a query like this over 50,000 rows it might crash my database. does anyone have any advice?

thanks very much in advance!

liz

$result = mysql_query("SELECT EstablishmentName 
					   FROM DataBackup27may09
					   where GeoCountry like 'An%'");
 
 
while ( $row = mysql_fetch_array($result) ) {
	$slug = toSlug($row['EstablishmentName']);
	mysql_query("UPDATE DataBackup27may09 SET urlSlug = '" . $slug . "'");
}

Open in new window

liz1976Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

racekCommented:
REPLACE(str,from_str,to_str)
0
racekCommented:
first test ir with select
SELECT select(EstablishmentName ,' ','-') as NewurlSlug
FROM your_table

Open in new window

0
syedasimmeesaqCommented:
$result = mysql_query("SELECT EstablishmentName
                                 FROM DataBackup27may09
                                 where GeoCountry like 'An%'");
 
 
while ( $row = mysql_fetch_array($result) ) {
      $text = toSlug($row['EstablishmentName']);
                    $slug= str_replace(" ", "_", $text);  


      mysql_query("UPDATE DataBackup27may09 SET urlSlug = '" . $slug . "'");
}

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

racekCommented:
if ok then

UPDATE  your_table
SET urlSlug = REPLACE(EstablishmentName ,' ','-');

test correction:
SELECT REPLACE(EstablishmentName ,' ','-') as NewurlSlug
FROM your_table;
0
racekCommented:
@syedasimmee you dont need loops - it can be done with one update .... looping take much more time ...
0
syedasimmeesaqCommented:
hi Racek. You are absolutely right. I just wanted to correct the code he pasted and include what he wanted to do in it.
Thanks
0
liz1976Author Commented:
hi,
thanks for all your quick responses but i'm still a bit stuck. i already have a function which creates the url slug (as well as converting non-standard characters and other things) so i have all of the slugs sorted. i just need to know how to put them into the table...?

liz
0
v2MediaCommented:
Post your function so we know how to call it.
0
liz1976Author Commented:
hi,

my function is below and i call it in my first code snippet like this:

$slug = toSlug($row['EstablishmentName']);

hope that helps.

thanks,

liz
//function to create URL slug
function toSlug($string,$space="-") {   
       
    if (function_exists('iconv')) {   
        $string = @iconv('UTF-8', 'ASCII//TRANSLIT', $string); //converts special characters, e.g. e with an accent, to their base letter
    }   
  
    $string = preg_replace("/[^a-zA-Z0-9 -]/", "", $string);   
    $string = strtolower($string);   
    $string = str_replace(" ", $space, $string);  
    $string = str_replace("---", "-", $string); //to stop e.g. "the ritz - london" becoming "the-ritz---london"
  
    return $string;   
}

Open in new window

0
racekCommented:
somthing like this ?
$result = mysql_query("CREATE TABLE resTable
         SELECT a.*," . toSlug($row['EstablishmentName']) . " as slug
         FROM DataBackup27may09");
 

Open in new window

0
liz1976Author Commented:
hi racek,

thanks for that but i really want to just put the urlSlug column in my original table, not create a new one. is this possible?

thanks,

liz
0
v2MediaCommented:
You need a script that is aware of how long it has executed and how much time is left before PHP max_execution kicks in and kills the process. You would select a limited result, say 500 records for each 30 seconds of execution time. Next, iterate through the results, check the time, and if there's time left, call the urlslug function and update the record.

About 2 seconds before the script gets terminated, output the ID of the last row updated. This ID is then used to limit the next 500 records starting from that ID. Rinse and repeat about 10 times and you should have the whole table updated.
0
liz1976Author Commented:
hi again,
thanks very much for your help everyone but i'm afraid i think you were misunderstanding what i wanted to do. i literally just wanted to get the slugs and put them in a table. i came round to thinking that it would be easier to create a new table with the slugs and do a left join with the main table, which i did using the code below.

thanks for the posts anyway.

liz
$result = mysql_query("SELECT HotelId, EstablishmentName 
					   FROM Data");
 
while ( $row = mysql_fetch_assoc($result) ) {
	$slug = toSlug($row['EstablishmentName']);
	mysql_query("INSERT INTO Slugs (HotelId, urlSlug) 
				 VALUES('" . $row['HotelId'] . "','" . $slug . "')  ");
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.