get the mysql auto_increment value

[lifeworks]

Hi

This isn't specifically a php issue, but i would be using php for the project...

i am creating a site where people can upload articles, photos, docs etc - thing is, I want to keep everything as its own entity and use tables to link articles to images etc... the only commonality is the userid, which will be attached to each file uploaded by a user... be it an article or an image

I want a user to be able to 'attach' a file to an article, but i want to use auto_increment for the files in the DB, but will need to retrieve the id assigned to the file, so that i can insert it into the link table between articles and other files...?! that sounded confusing...

help!?

[phpbeginners]

you need one to many relationship.

one table with userID and the uploaded files database with userID also.

use the userID for every file upploaded do not use auto_increment.

auto_increment is just for the unique file that you want to delete/modify in the future using the auto_increment ID to make it unique but in terms of ownership you need to use the userID.

[lifeworks]

i hear you - and agree, every file will have a userId associated with it... thing is, someone might have 5 articles and 10 photos, and articles can 'share' images... i could upload the images as one step, then display a list and allow the user to 'attach' images to articles... but i want to do that in one step...

i thought i could timestamp each file on upload, then query the file_table and retrieve the most recent file, associated with that user... and then add that id as well as the userid to a link_table... but seems like a bit of a hack

[samarudge]

You can use the mysql_insert_id() function. Run an insert query like this

//Connect to MySQL etc.
mysql_query("INSERT INTO MY_TABLE (NAME, EMAIL, PHONE) VALUES('bob', '[e-mail]', '555-222-111')") or die(mysql_error());
$InsertID = mysql_insert_id();

now $InsertID will contain the PRIMARY_KEY from the table insert.
=)

[lifeworks]

sweeeet...

do you reckon that would work using the mysqli class (dont have much experience with it)...

this is why php is great, there is a function for every common task... or problem...

thanks!!

[samarudge]

Yep except with MySQLi you need link identifyer I.E. when you connect you do something like

$Connection = mysqli_connect(...);

you would just need to use that in your insert query like

mysqli_query($Connection, "INSERT INTO MY_TABLE (NAME, EMAIL, PHONE) VALUES('bob', '[e-mail]', '555-222-111')") or die(mysqli_error($Connection));
$InsertID = mysqli_insert_id($Connection);

or if using the variable type class use

$Connection->query("INSERT INTO MY_TABLE (NAME, EMAIL, PHONE) VALUES('bob', '[e-mail]', '555-222-111')");
$InsertID = $Connection->mysql_insert_id();

=)

[lifeworks]

looks good, thanks again