asked on

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

racek

REPLACE(str,from_str,to_str)

racek

first test ir with select

SELECT select(EstablishmentName ,' ','-') as NewurlSlug
FROM your_table

Open in new window

syedasimmeesaq

$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 . "'");
}

racek

if ok then

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

test correction:
SELECT REPLACE(EstablishmentName ,' ','-') as NewurlSlug
FROM your_table;

racek

@syedasimmee you dont need loops - it can be done with one update .... looping take much more time ...

syedasimmeesaq

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

liz1976

ASKER

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

v2Media

Post your function so we know how to call it.

liz1976

ASKER

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

racek

somthing like this ?

$result = mysql_query("CREATE TABLE resTable
         SELECT a.*," . toSlug($row['EstablishmentName']) . " as slug
         FROM DataBackup27may09");

Open in new window

liz1976

ASKER

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

SOLUTION

v2Media

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

liz1976

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial