On Monday, December 15, 2008 7:29 PM, gouldimg@mac.com wrote:
I have a mySQL database with 700,000 records in it, which are presently keyed with an "auto-increment" field.
What I'd like to do is create another field with a field where each and every record number has a unique keyvalue. Example: "su5e23vlskd" for records 1, and "34fdfdsglkdj4" for record 2. All that matters is that it's unique, and isn't a number that can be guessed or an "autoincrement" number, where a hacker can just figure out the keyvalue by incrementing numbers. It doesn't matter to me if each keyvalue field is just numbers, or a number/letter combination - - - all that matters is that each keyvalue field is unique. Is there an automatic way that mySQL could do that, or would I need to write a php script to somehow go through each record and create this unique value?
Here is my answer to your question.
You can use this same logic to create unique id's for many things.
Hope this comes out okay in an email, if not I also put it online here:
http://www.bigdoghost.com/downloads/php-284646.html
<?php
error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);
ini_set('display_startup_errors','1');
ini_set('display_errors','1');
function dec2base($dec)
{
$digits = "23456789ABCDEFGHJKLMNPQRSTUVWXYZ";
$value = "";
$base = strlen($digits);
while($dec>$base-1)
{
$rest = $dec % $base;
$dec = $dec / $base;
$value = $digits[$rest].$value;
}
$value = $digits[intval($dec)].$value;
return (string) $value;
}
/*
// Step 1) define database connection
define('DB_HOST', 'localhost'); // Change this to the proper DB Host name
define('DB_USERNAME', 'myusername'); // Change this to the proper DB User
define('DB_PASSWD', 'mypassword'); // Change this to the proper DB User password
define('DB_NAME', 'mydatabase'); // Change this to the proper DB Name
@mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWD) or die("Error: Database connection information is incorrect");
@mysql_select_db(DB_NAME) or die("Error: Database connection information is incorrect");
*/
/*
// Step 2) create test schema
CREATE TABLE IF NOT EXISTS
test (
id int(11) NOT NULL auto_increment,
mykey varchar(20) NOT NULL,
PRIMARY KEY (
id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
*/
/*
// Step 3) create 700,000 records
for ($i=1; $i <= 700000; $i++)
{
@mysql_query("INSERT INTO test VALUES ('', '')");
}
*/
/*
// Step 4) update 700,000 records
// The larger this number is detrmines the size of "mykey"
$int = 1000000;
$result = @mysql_query("SELECT id FROM test ORDER BY id ASC");
if (@mysql_num_rows($result) > 0)
{
while ($row = @mysql_fetch_object($result))
{
// Add the two numbers together and base it
$mykey = dec2base($int+$row->id);
@mysql_query("UPDATE test SET mykey='".$mykey."' WHERE id='".$row->id."'");
}
}
*/
?>