MySQL: Inserting Unique Column Value

Hi pain_is_relative,
I wish it could be that simple. But clients today demand SEO friendly URLs, and not URLs that simply work (and work well).

I figure I could ditch the loop, and change my code to something like this:

$safe_title = 'hello_world';
$results = $db->execute(
      "SELECT * FROM `posts` WHERE `safe_title` REGEXP '^$safe_title(_[0-9]+)?$' LIMIT 1"
);

/**
 * Check to see if we found another post with the same
 * safe title
 */
if (count($results) > 0) {
      preg_match('/^(.*?)(_([0-9]))?$/', $results->safe_title, $matches);
      
      /**
       * If the safe title already has a _[0-9] appended to
       * it, we need to increment the [0-9] by one. Otherwise
       * add _1 to the safe title.
       */
      if (!empty($matches[2])) {
            $safe_title = $matches[0] . '_' . ($matches[2] + 1);
      } else {
            $safe_title = $matches[0] . '_1';
      }
}

$db->insert("INSERT INTO ....") // Insert post with safe title

That still doesn't solve the problem of another blog post with the same safe title being inserted microseconds before I call $db->insert(). Also I've never used regular expressions in a SELECT statement before, and I don't know what the performance would be like on large data sets.

You could always add the session id to the date and then md5 it then add it to the safe name... but yess.. its ugly...

you could invesitigate using modrewrite, but i am not a fan of that.

i still think that a url of : blah/blah/blogpost.php?id=678 still looks reasonable.
you could even do this:
blah/blah/blogpost.php?id=678&name=my_safe_name_url_is_ace
that way you preserve the page name, and display it in the url, without ever using it. (just faking it it)

@pzurowski -
You gave me an idea. I could try SQL like this:

INSERT INTO `posts` (`id`, `title`, `content`, `user_id`, `safe_title`)
SELECT
null,
'Hello World',
'This is a test',
1,
IF(COUNT(*) > 1, CONCAT('hello_world_', COUNT(*)), 'hello_world')
FROM `posts` WHERE `safe_title` REGEXP '^hello_world(_[0-9]+)?$'
LIMIT 1

That would insert the blog post, and generate the safe title all in one transaction. This would be very quick, and it becomes unlikely that another post with the same safe title would be inserted before the transaction is complete.

Any thoughts on this kind of SQL?

@pzurowski -
I assume this is better performance wise, because I'm not requesting a count of all matching rows twice?

First of all, because there is no regexp, which are slower. Moreover you can quicker read it, and it will be faster to expand or bug fix in future :)

Hi headzoo,

$safe_title = getSafeTitle('Hello World'); // this will give you white spaces removed string.
 
$results = $db->execute("SELECT count(*)+1 AS total FROM `posts` WHERE `safe_title` LIKE '$safe_title%'");
 
if( $count > 2 ){
   $safe_title .= "_" . $count;
} 
 
$db->insert("INSERT INTO ....") // Insert post with safe title
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

I missed some comments in my last post so consider these too.

// Above mentioned code is utilizing only 2 hits in post table.
1) check existing
2) Insert valid information

** There is no way to sence the duplicate data during insertion of post in table.

/* You would use one table transaction for check duplication before inserting data in table.