[eluser]Dam1an[/eluser]
Damn, how did I miss this? I'm currently in the process of auto generating several millions of unique slugs. My scenario is a little more specific then this, but thought I'd share what I have here anyway, in the hope it's of use to someone as well
(Explaination after the code)
Code:
<?php
// Code to populate the cities table here (too boring and irrelevant to show)
// And now we can take care of creating unique slugs
$table = 'cities';
// First of all, go through all the cities, and make a default slug
$query = $this->db->get($table);
$results = $query->result();
foreach($results as $result) {
$slug = strtolower($result->name);
$slug = url_title($slug);
$data = array('slug'=>$slug);
$this->db->where('id', $result->id);
$this->db->update($table, $data);
}
// Now everything has a slug, find any duplicates
$query = $this->db->query("SELECT `slug`, COUNT(`slug`) AS count FROM `$table` GROUP BY `slug` HAVING count > 1 ORDER BY count");
$results = $query->result();
// For each duplicate, get all rows matching that slug
// We will then append the country name to the end
foreach ($results as $result) {
$this->db->select("$table.*, countries.name as country_name");
$this->db->where("$table.slug", $result->slug);
$this->db->join('countries', "$table.country_code = countries.country_code");
$query = $this->db->get($table);
$dupes = $query->result();
foreach($dupes as $dupe) {
$data = array('slug'=>$dupe->slug.'-'.url_title(strtolower($dupe->country_name)));
$this->db->where('id',$dupe->id);
$this->db->update($table, $data);
}
}
// Now everything has a slug, find any duplicates
$query = $this->db->query("SELECT `slug`, COUNT(`slug`) AS count FROM `$table` GROUP BY `slug` HAVING count > 1 ORDER BY count");
$results = $query->result();
// For each duplicate, get all rows matching that slug
// We will then append the country name to the end
foreach ($results as $result) {
$this->db->select("$table.*, countries.name as country_name");
$this->db->where("$table.slug", $result->slug);
$this->db->join('countries', "$table.country_code = countries.country_code");
$query = $this->db->get($table);
$dupes = $query->result();
foreach($dupes as $dupe) {
$data = array('slug'=>$dupe->slug.'-'.url_title(strtolower($dupe->country_name)));
$this->db->where('id',$dupe->id);
$this->db->update($table, $data);
}
}
// Just for good measure, make sure we have elimitaed all duplicates
$query = $this->db->query("SELECT `slug`, COUNT(`slug`) AS count FROM `$table` GROUP BY `slug` HAVING count > 1 ORDER BY count");
$results = $query->result();
echo '<pre>', print_r($results), '</pre>';
Whats going on
This version is for creating slugs for 20,000 cities
1) For every row in the table, create a base slug, which is a lower case url_title of the city name
2) Once this is done, find any duplicates
3) For each duplicate, append the lower case url_title of the country that city is in
4) Once again, check for any duplicates (apparently it's possible to have the same city more then once in a country)
5) For each duplicate, append -number in descending order, so the largest city has no suffix, and the smallest city has the largest number for it's suffix (size is determined by number of hotels in this case)
6) Relax in the satisfaction you now have unique slugs
Bra gjort!
