Custom ID Generator PDO

[jbonnett]

Hi All,

I was wondering if there was a way that I could check if an ID already exists if not reserve it for the final query? Maybe with some sort of transaction based query.

 

So if I were to do this:

$exists = 1;
while($exists > 0) {
	$new_id = uniqid();
	$exists = $database->prepare('SELECT COUNT(*) FROM `Employees` WHERE `EmployeeID` = :EmployeeID');
	$exists->execute([":EmployeeID" => $new_id]);
        $exists = $exists->rowCount();
}

Along with:

$query = $database->prepare("
INSERT 
INTO 
	`Employees` 
	(
		`EmployeeID`,
		`Username`,
		`Password`,
		`FirstName`,
		`LastName`,
		`Email`,
		`Timestamp`,
		`Roles_Role`
	) 
VALUES 
	(
		:EmployeeID,
		:Username,
		:Password,
		:FirstName,
		:LastName,
		:Email,
		:Timestamp,
		:Roles_Role
	)
");
		
$query->execute([
	":EmployeeID" => $new_id,
	":Username" => $this->Username,
	":Password" => $this->Password,
	":FirstName" => $this->FirstName,
	":LastName" => $this->LastName,
	":Email" => $this->Email,
	":Timestamp" => $this->Timestmap,
	":Roles_Role" => $this->Roles_Role 
]);
$query->execute();

I may get an error where if I check if the new custom ID exists, if it doesn't then the next query may fail to duplicates.

 

Thank you for any help in advance.

Edited June 27, 2016 by jbonnett

[requinix]

Why are you generating the ID by yourself? Don't do that. Use auto_increment.

[Muddy_Funster]

Couple of things:

 

1> your first code block is screaming "Infinite Loop" at me.  It may not be for every occurrence but I can't see why you made it a loop at all, you really shouldn't ever run queries inside loops unless you absolutely have to.

 

2> What's the nature of your ID column and why are you setting it manually?  Manual setting of ID fields is a bad idea and can lead to serious (i.e. break the whole system) difficulties when multiple users start using the app at the same time. 

[jbonnett]

Thank you for the replies,

I don't want a numerical id, also that loop will only keep looping if the id already exists. E.g. if it already exists generate another id. The generating function will eventually become custom, this is just for testing purposes. I realise the more rows the more likely it is to loop, although this is not the final code.

 

Also you mention that it's a bad idea to create your own... My University seems to think not, as there are more combinations to be had with custom generators (if done the right way). They even use one themselfs. And knowing this is why I mentioned transactions as transactions allow you to work with existing data in a powerful way.

 

Not meaning to come across as ungrateful, although I'm looking for this specifically and not the easiest way of achieving the somewhat same result.

[Muddy_Funster]

It's OK, you don't sound ungrateful - condescending, yeah, but not ungrateful.

 

Well, using an unsigned, auto_inc bigint datatype gives you up to 18,446,744,073,709,551,615 possible records.  How many more "combinations" could someone need? Actually...what do you even mean by "combinations"? I don't know if we're missing some information or your just not open to the idea that what you are trying to do is not best practice. Even the database system itself uses auto_inc data types to manage it's internal tables and referencing.

 

To be honest, and I'm not trying to be flippant or nasty when I say this, if your heart set on ignoring us and doing it the wrong way then you would be better off talking to someone at that University of yours.

[requinix]

Is there a particular reason why it can't simply be a number? Maybe there are other alternatives available - for example, substr(md5($id), 0, 13) (the same length as a uniqid) for $id between 1 and 1000000 is unique, so unless you plan to have more than one million employees that would be an easy option.

[Jacques1]

If you don't want sequential IDs, use a proper(!) random number generator like random_bytes() and encode the result with your favorite encoding (Base64, hexadecimal, ...).

<?php

// random_bytes() is natively available in PHP 7; if you have PHP 5, use the random_compat library:
// https://github.com/paragonie/random_compat

$random_id = bin2hex(random_bytes(16));

You do not need a loop, because the chance of a collision is practically zero.

[kicken]

If you want a more user-friendly ID, make that a secondary Unique column. Use a standard INT AUTO_INCREMENT column internally to link tables and what not, then in another column insert your generated ID. If you get an error due to the UNIQUE constraint, generate a new ID and try again.

[jbonnett]

If you are wondering why I wanted to change to a non numerical system take a look at this:
https://www.youtube.com/watch?v=gocwRvLhDf8

 

The video explains how it could be a security risk and how you can't have as many entries as the following.
 
I also figured out how to do it with transactions, if you would like to know how I managed it for your future code here you go:

try {
    $pdo->beginTransaction(); // start a transaction

    // create and execute query to see if the id exists (like the loop in my first post)
    // Add something to the database using the new id

    $pdo->commit(); // make the changes
} catch (Exception $e) {
    $pdo->rollback(); // if it fails you can just take the database to the previous state
}

Edited November 12, 2016 by jbonnett

[kicken]

Don't try and check then insert, use a UNIQUE constraint on the column for your ID. A transaction won't prevent two separate clients from potentially inserting the same ID. The purpose of a transaction is to enable you to rollback many changes if something fails and provide a consistent view of the data across multiple queries.

 

Note that you don't necessarily have to generate your own IDs just to prevent having a sequential ID show up in the URL. You could still use an auto-increment ID but encode it in some way and use that encoded version in your URLs.

Edited November 12, 2016 by kicken

[Jacques1]

if you would like to know how I managed it for your future code here you go

 

This is BS, and it's sad that you obviously haven't learned anything in the last months despite getting a lot of good advice.

 

First off: If you have trouble with collisions, there's something wrong with your generator or the length of the generated IDs. Proper random IDs (e. g. 128 bits from a device like /dev/urandom) do not collide. In fact, they're commonly used as globally(!) unique IDs.

 

Secondly, stop trying to reinvent the squared wheel. You've been told two times already to use uniqueness constraints, yet you're still fumbling with your transactions.

 

If this is an educational project, I'm afraid you've missed the educational part.

[jbonnett]

There is a unique constraint on the column, transactions should work due to ACID. Also MySQL doesn't natively support base64.

Edited November 12, 2016 by jbonnett

[kicken]

Nothing about ACID prevents duplicates, and if you have a UNIQUE constraint then there's no need to first check if the ID you generated exists.

 

For example if you had two clients working at the same time and their requests get interleaved such as:

 

Client A connects

Client B connects

 

Client A issues SELECT for ID#abcd (to check if it exists)

Client B issues SELECT for ID#abcd (to check if it exists)

 

Client A gets no result

Client B gets no result

 

Client A issues INSERT ID#abcd

Client B issues INSERT ID#abcd

 

Now you have two ID#abcd records if there were no constraint. A transaction does not prevent this from happening, only a UNIQUE constraint.