CODEWITHSUNDEEP
mysqlsql
caramba
caramba

Reputation: 22480

is there a better way to use mysql_insert_id()

I have the following sql statement written in PHP.

$sql='INSERT INTO pictures (`picture`) VALUES ("'.$imgs[$i]['name'].'",)';
$db->query($sql);
$imgs[$i]['sqlID']      = $this->id=mysql_insert_id();
$imgs[$i]['newImgName'] = $imgs[$i]['sqlID'].'_'.$imgs[$i]['name'];
$sql='UPDATE pictures SET picture="'.$imgs[$i]['newImgName'].'" WHERE id='.$imgs[$i]['sqlID'];
$db->query($sql);

Now this writes the image name to database table pictures. After that is done I get the mysql_insert_id() and than I'll update the picture name with the last id in front of the name with underscore.

I'll do this to make sure no picture name can be the same. Cause all those pictures get saved in the same folder. Is there another way to save that ID already the first time I set the sql query? Or are there other better ways to achieve this result?

Thanks for all advices

Upvotes: 0

Views: 66

Answers (2)

Vatev
Vatev

Reputation: 7590

Using the native auto_increment - there is no other way. You need to do the 3 steps you described.

As Dan Bracuk mentioned, you can create a stored proc to do the 3 queries (you can still get the insert id after executing it).

Other possible options are:

  • not storing the id in the filename - you can concatenate it later if you want (when selecting)
  • using an ad-hoc auto increment instead of the native one - I would not recommend it in this case, but it's possible
  • using some sort of UUID instead of auto increment
  • generating unique file names using the file system (Marcell Fülöp's answer)

Upvotes: 1

marekful
marekful

Reputation: 15351

I don't think in this particular case it's reasonable to use MySQL insert Id in the file name. It might be required in some cases but you provided no information why it would be in this one.

Consider something like:

while( file_exists( $folder . $filename ) {
  $filename = rand(1,999) . '_' . $filename;
}

$imgs[$i]['newImgName'] = $filename;

Of course you can use a larger range for rand or a loop counter if you wanted tot systematically increment the number used to prefix the original file name.

Upvotes: 1

Related Questions