[RESOLVED] Auto_increment question
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 Code:
<?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?
Re: Auto_increment question
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.
Re: Auto_increment question
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.
Re: Auto_increment question
Quote:
Originally Posted by 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 Code:
<?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.
Re: Auto_increment question
i would just make a random hash and make that the file name
using rand() you can do many things with it.
EXAMPLES
PHP Code:
//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'));
Re: Auto_increment question
Quote:
Originally Posted by visualAd
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.
Re: Auto_increment question
Quote:
Originally Posted by dclamp
i would just make a random hash and make that the file name
Not guaranteed to be unique.
Re: Auto_increment question
penagate - I've never used stored procedures but I'm reading into it...
Quote:
Originally Posted by dclamp
i would just make a random hash and make that the file name
using rand() you can do many things with it.
EXAMPLES
PHP Code:
//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...
Re: Auto_increment question
then try this:
PHP Code:
//using date function:
$datehash = date('r');
$randhash = rand(100,99999)*rand(1,999);
$id = $datehash . $randhash;
$id = md5($id);
Re: Auto_increment question
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.
Re: Auto_increment question
Quote:
Originally Posted by DigiRev
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:
Re: Auto_increment question
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?
Re: Auto_increment question
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.
