how to retrieve the last stored record information from a database table

[IVYY]

Hi all,

I have a slight problem with retrieval of records.

Currently, I have a form with reservation number (an auto incremental primary key of my table), ISBN (a primary key from my books table), Email (a primary key from my users table), number of orders, and collection date.

My reservation number and collection date are hidden textfields while ISBN and email are read only textfield. So when users key in number of orders, it will insert a record into my reservation table in my database and i will be redirected to reservation confirmation page.

How do i show the reservation number immediately on my reservation confirmation page?

Is it to use mysql_insert_id() ?

If I am supposed to use it, how should i place it?

Please elaborate more as I am quite new to php. Thanks!

[David_Powers]

I have moved this thread to the Develop server-side applications in Dreamweaver forum, which is a more appropriate place for such discussions.

[MurraySummers]

THe easy way to get the last stored record is to sort the recordset in DESC order by record ID in your SQL statement. The first one will then be the last one added.  Using mysql_insert_id() is OK too, but it should be done immediately below the SQL statement, and assigned to a variable at that point.

[bregent]

>THe easy way to get the last stored record is to sort the recordset in DESC

>order by record ID in your SQL statement. The first one will then be the last one added

Murray, that technique is not safe because the returned value may not necessarily be the last one inserted by the current connection. In other words, if two people are inserting rows at the same time, the SELECT statement can return the wrong value. This is known as a race condition. mysql_insert_id, or its equivalent PDO function, ensures the value return is the one last inserted by the current connection.

[IVYY]

Hi bregent,

You have mentioned that I should use mysql_insert_id.

May I know how do i apply mysql_insert_id? I am clueless in going about doing it. Do I put it in my reservation page or reservation confirmation page?

Thanks!

[David_Powers]

You need to use mysql_insert_id() immediately after the record has been inserted into the database. Place it immediately after the record insertion code.

$res_num = mysql_insert_id();

You can then use $res_num to display it on the same page.

If you need to display it on a different page, make sure you add session_start(); at the top of both pages, and use a session variable instead:

$_SESSION['res_num'] = mysql_insert_id();

I don't want to sound rude, but if you are, as you say, "clueless" about how to do something like this, you really need to learn the basics of PHP. You can get advice and help in a forum like this, but it's impossible for people like bregent, Murray, or myself to hold other people's hands every step of the way. As I said before, my main concern is that you're using the original MySQL functions, which have been deprecated as of PHP 5.5, and are scheduled for removal.

You should be using mysqli (MySQL Improved) or PDO, neither of which are supported by Dreamweaver server behaviors. Do yourself a favour, and get a good book that teaches modern methods of using PHP. I've written one called PHP Solutions, 2nd Edition. But there are several other good ones on the market, such as those by Larry Ullman. I'm not writing this in the hope of selling you a copy of my book, but in the hope of pointing you in the right direction with regard to creating database-driven websites. It's a complex subject, and there's a lot to learn. Once you have the basic knowledge, you'll find it much easier to get help in online forums. Good luck.

[IVYY]

Hi all,

I have removed the hidden field reservation number as it is unnecessary. (users do not need to key in it as reservation number will be auot-generated in the database)

I am actually inserting a new record. When a user logs in to the log in page, the username will be captured as session variable. When the user selects a book, the ISBN will be captured as session variable too. Then if the user wants to reserve the book, the username and ISBN will be populated by the session variables and be set as read only in the form textfields. The user justs need to enter the number of books to be ordered. The collecton date is set to be 14 days from the current date and it is hidden from view.

So when the user presses submit button, it will insert a new record in my database, filling up the username, ISBN, number of orders, collection date, while the reservation ID (primary key of this table) is auto generated.

The user will then be directed to the reservation confirmation page. My question is how do I show the reservation ID immediately in the reservation page.

Thanks!

[David_Powers]

Your description is rather confusing. You say that the reservation number is an auto-increment primary key, but you also say that it's a hidden field in your form. So, what are you actually doing?

If the reservation number is in a hidden field, it presumably refers to an existing record in your database. You can use that primary key to update the record, but you can't use it to insert a new record. Inserting a new record will generate a new primary key.

As Murray, says, you can get the latest number by using DESC to get the results in reverse order.

My main concern is that you're new to PHP, yet you're using deprecated mysql functions, such as mysql_insert_id(). I suspect that you're also using Dreamweaver server behaviors. In a word, DON'T. They haven't been updated for many years, and they use outdated code, which will eventually stop working. Although it will take time to learn how to write your own PHP code, you'll be glad in the end.