store the value of next auto_increment into php variable using last_insert_id()

[joshgarrod]

Hi, I need to find out what the next auto increment ID is going to be when I make a submission to my database via a form because I am uploading images too and they are being stored in  new folder that is made and named the same as the id so that i can link them up when displaying the data. I have this line of code at the moment to store the value of last_insert_id() into the variable $next_id but it tells me it is undefined?

<?php
$query = "SELECT * FROM classifieds (last_insert_id(), '$next_id')"; 

	echo "the next ID to be inserted is $next_id";
	?>

 

Thanks!

[rhodesa]

do the INSERT, use PHP's mysql_insert_id() to get the ID of the row it just created, then use that to move the file to the directory

[sasa]

use this scenario

1. insert row in table with some date

2. get last insert id

3. move file

4. update row in table (use id from step 2) or delete row if get error on step 3

[joshgarrod]

Ok, yeh I have the process sorted its just the syntax in which i use last_insert_id()

[premiso]

<?php
      // insert statement here
      $next_id = mysql_insert_id();
      
      echo "the next ID to be inserted is $next_id";
?>

 

Reading up on the last_insert_id it is suggested not to use it, but if you must, I think this would work:

$query = "SELECT last_insert_id(colname) as next_id FROM classifieds";
$next_id = mysql_fetch_assoc(mysql_query($query));
$next_id = $next_id['next_id'];
echo "the next ID to be inserted is $next_id";

[joshgarrod]

thanks for taking the time on this everyone.

 

I am getting this warning:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

and it is not printing the variable

[premiso]

thanks for taking the time on this everyone.

 

I am getting this warning:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

and it is not printing the variable

 

Post the code you are using, or even read up on the last_insert_id, there are alot of articles that explain it's usage etc. Also check that your mysql version is at least 4.1 or greater or else you are just barking up the wrong tree here.

[Mchl]

You can't reliably get value of NEXT insert id from mysql database. You can only get value of the LAST id inserted within current connection.

[joshgarrod]

Is there a better way to go about what I am trying to achieve? For example: query the database for the highest value ID and then add one on and name my image folder that?

[Mchl]

Is there a better way to go about what I am trying to achieve? For example: query the database for the highest value ID and then add one on and name my image folder that?

 

Won't work eecause

1. ID Increment value is not always 1 (depends on MySQL server configuration)

2. If two or more users are doing it in the same time, they might get same ID

 

Try doing it like this:

 

1. Upload a file to a temporary folder

2. Insert data into database

3. Get last insert ID

4. Create a folder and move file from temporary folder.

 

(pretty much rhodesa already told you that)

[joshgarrod]

how would i use mysql_insert_id()  like rhodesa said?

[rhodesa]

you have to change the order of your script a bit. what does your code look like?