get last record of a table

Question

I have a form that lets users create new records,
In the field that is the id that auto-increments i want the user to see the record number that this record is by somehow showing latest value+1 in the fields value.

I was thinking of something like:

<input type="text"  value="<?php echo $myQuery['recordId'].length+1"/> 

But that doesn't work.

Again, this is only to get the default value in the <input>

Instead of having to look up the last id, And this form is only used by one admin.

Answer 1

This blog post describes how to get it, but there's no guarantee that it will actually be the correct value upon submission of the form.

Answer 2

You can find the one plus the highest id by selecting it:

SELECT MAX(id)+1 FROM table

But like David said, you're not guaranteed this will be the id that is used, because 2 people could load the page at about the same time.

Answer 3

To get the last id relevant to that connection, use mysql_insert_id

In your case you'll have to insert an empty record in the db before you can guarantee that it will count. It will leave a lot of empty records if the users don't proceed, but you can do a cleanup every time the form is loaded by deleting records created more than one hour ago that don't have a title value or something like that.

Answer 4

if you absolutely need it to be an integer, you can always create a special table with only one auto_increment column, insert to it, get the last_insert_id() and use that. this is kind of mimicking the sequences in oracle. the ids that dont get used will go waste, but the other problems associated with multiuser scenarios will not occur. The following code copied from mysql documentation on information functions. Go there to read on the promises of this code.

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();

Now back to my own words. If it does not have to be an integer you can use guid. There is a uniqid function in php, and there is a uuid function in mysql. the theory says even if everyone keeps generating guids independently all the time, every guid will be unique.

So this is one way of doing it:

<?php
    $query = "select uuid()";
    $result = mysql_query($query);
    $row = mysql_fetch_row($result);
    $uuid = $row[0];
?>
<input type="text" value="<?php echo $uuid; ?>"/>