[RESOLVED] Auto_increment question

[DigiRev]

From the responses in this thread:
http://www.vbforums.com/showthread.php?t=513904

I'm doing a simple script for someone adding banners to their site:
id
banner_type
file_name
'etc...

I want the file_name to always be unique. So I will append the id field to the beginning of the file name. But I won't know the ID until I have inserted the new record.

What's the best way to do this? Insert the record with a blank file_name field, then use the mysql_insert_id() and then update that record after?

A real quick example of what I'm talking about:
<?php
// Insert the record.
mysql_query("INSERT into banners (id,banner_type,file_name,url,alt_text) VALUES
(NULL,'1','','www.something.com','Visit something.com!')");

// Get ID of last insert.
$insert_id = mysql_insert_id($dbc); //<- $dbc is current connection value.

// Get unique file name.
$fname = $insert_id . "_$actual_file_name";

// Update record.
mysql_query("UPDATE banners SET file_name='$fname' WHERE id='$insert_id' LIMIT 1");
?>

Is this the correct way to do it?

[penagate]

The insert and update is a safe non-transactional method. I'll have to think a bit more to see if there is a more elegant way.

(LAST_INSERT_ID() + 1) works to a degree, but only if you've already done an insert using that connection, and the auto_increment value hasn't been manually altered. This seems unacceptable.

By the way, you can omit auto increment columns altogether from your INSERT query.

[penagate]

If you were to use a transaction, you could use the SHOW TABLE STATUS query. To reduce code and overhead, I would write a stored procedure to do this.

[visualAd]

From the responses in this thread:
http://www.vbforums.com/showthread.php?t=513904

I'm doing a simple script for someone adding banners to their site:
id
banner_type
file_name
'etc...

I want the file_name to always be unique. So I will append the id field to the beginning of the file name. But I won't know the ID until I have inserted the new record.

What's the best way to do this? Insert the record with a blank file_name field, then use the mysql_insert_id() and then update that record after?

A real quick example of what I'm talking about:
<?php
// Insert the record.
mysql_query("INSERT into banners (id,banner_type,file_name,url,alt_text) VALUES
(NULL,'1','','www.something.com','Visit something.com!')");

// Get ID of last insert.
$insert_id = mysql_insert_id($dbc); //<- $dbc is current connection value.

// Get unique file name.
$fname = $insert_id . "_$actual_file_name";

// Update record.
mysql_query("UPDATE banners SET file_name='$fname' WHERE id='$insert_id' LIMIT 1");
?>

Is this the correct way to do it?
Why do you want the file name to be unique yet at the same time you are appending a unique value to the string? It makes no sense. Remove the unique constrain on the file name as you clearly do not need it.

[dclamp]

i would just make a random hash and make that the file name

using rand() you can do many things with it.

EXAMPLES

//simple - create a random number between 100 and 99999
$id = rand(100,99999);

//multiplying - multiply random numbers together
$id = rand(100,99999)*rand(1,999);

//using MD5 with it makes it more unique
$id = md5(rand(100,99999));

//using date function:
$id = md5(date('r'));

[DigiRev]

Why do you want the file name to be unique yet at the same time you are appending a unique value to the string? It makes no sense. Remove the unique constrain on the file name as you clearly do not need it.

I don't understand what you mean?

He uploads a file which is a banner image. The name of the file needs to be unique so it never overwrites the image that is already on his server. I'd rather not ask him to change the name of the image if one already exists, so I want it to always be unique.

[penagate]

i would just make a random hash and make that the file name

Not guaranteed to be unique.

[DigiRev]

penagate - I've never used stored procedures but I'm reading into it (http://www.databasejournal.com/features/mysql/article.php/3525581)...

i would just make a random hash and make that the file name

using rand() you can do many things with it.

EXAMPLES

//simple - create a random number between 100 and 99999
$id = rand(100,99999);

//multiplying - multiply random numbers together
$id = rand(100,99999)*rand(1,999);

//using MD5 with it makes it more unique
$id = md5(rand(100,99999));

//using date function:
$id = md5(date('r'));


Yeah I guess using the md5(date) would always be unique, not sure why I didn't think of this, might be easier. But what if the date gets reset on his server or something? That could cause problems...

[dclamp]

then try this:


//using date function:
$datehash = date('r');
$randhash = rand(100,99999)*rand(1,999);

$id = $datehash . $randhash;
$id = md5($id);

[penagate]

How can it be more unique? It's either unique or it isn't. Checksums are not unique. They are designed to be used for verification when combined with other data metrics.

[visualAd]

I don't understand what you mean?

He uploads a file which is a banner image. The name of the file needs to be unique so it never overwrites the image that is already on his server. I'd rather not ask him to change the name of the image if one already exists, so I want it to always be unique.
I apologise. I misunderstood you. :( :blush:

[DigiRev]

No problem visual :)

I'm sure using the hashes there's like a 0.000001% chance it will fail but I'd rather just do it the "correct" way and not have to worry about it ever failing. I'm kind of a perfectionist...:o

His MySQL version is not up-to-date either and I think stored procedures are not supported with his version. :confused:

Should I stick with the way I'm doing it?

[visualAd]

INSERT --> UPDATE is fine as the operation of saving the file is dependant on the operation of adding the record. You could however use the previous ID column - but that would again rely on a second query.