[SOLVED] mysqli insert id

[947740]

I have a script that inserts data into the database based on the last entry.  I currently use MAX(ID) to get the last ID in the database, and then I just add one to create the data.  This works fine, as long as you have not deleted data from the database.

 

I am aware of the mysqli insert id function, but I am unsure as to how I need to incorporate it into my code.  Another might-be problem is that the last insertion did not occur in this instance of the script.

 

As you can see, the current way takes three lines and adding 1: to get the ID of this insertion:

if($a == "afterschool" && $x == "CheckOut") {
   	$q = "SELECT MAX(ID) FROM afterschool";
   	$n = mysqli_fetch_row(mysqli_query($cxn,$q));
$n = $n[0] + 1;
$values[] = '"'."<a href='afterschool/out.php?ID=$n'>Check Out</a>".'"';
}

 

Help is appreciated.

[JonnoTheDev]

In this situation I wouldn't use an auto incremental field for your primary key. Prior to inserting a record run a function that gets the next available ID (using SELECT MAX()) and use that in your insert query. You then have the id available wherever you need it.

[947740]

Okay.  So what would happen when there is no data in the database?  (i.e., what would select max() return without data?)

[JonnoTheDev]

You will +1 to the returned number to get the next Id anyhow so if there is 0 rows the function would effectivly do 0+1 giving you Id 1

function maxnum($table, $column) {
  $result = mysql_query("SELECT MAX($column) AS maxnum FROM $table");
  $row = mysql_fetch_assoc($result);
  return $row['maxnum'] + 1;
}

[947740]

Okay, sweet.  Thanks Neil.