Solved Select last_insert_id and assign to variable

Hewlett-packard / Cq61 notebook
June 9, 2013 at 06:40:05
Specs: Windows 7, AMD sempron 2 gb
Hi I am creating an hotel reservation site for a friend.I found a nice tutorial on html.it and the code runs fine from the command line, no problem. Now I need to build an interface say to insert clients's data. So I have a form.My problem is that for the site to work I need to save last_insert_id to a variable called @codCliente.Can somebody just write for me that line of code so that I can insert it in the php file that handles the form? Please don't send me to some url looking for the answer, I've tried them all. Thank you.
P.S:If you need the whole code please let me know.

See More: Select last_insert_id and assign to variable

Report •


#1
June 9, 2013 at 07:25:11
✔ Best Answer
I'm not sure why you would possibly want that, but it sounds like a guaranteed race condition. Read: http://dev.mysql.com/doc/refman/5.0...

How To Ask Questions The Smart Way


Report •

#2
June 10, 2013 at 02:37:59
Hi thanks for your answer.The reason I want that is because I don't know any other way. I have not understood yet how to execute MySQL queries from a web page except by using php.If you want to take time to look at this site:http://www.html.it/pag/32165/tutorial-di-esempio-database-prenotazioni-alberghiere-i/

you can probably see what I am trying to do. In the 1st part you create the DB and tables, populate the various tables etc.In the 2nd part you actually execute MySQL queries to insert client's data, reserve the room, calculate the price and supplements etc. and all is revolving around that @codCliente.I am sorry is in Italian but I am sure you can figure it out.As I said if I run everything from the command line or phpMyAdmin it works fine, but my problem is that I have to deliver a web site from which to execute the various tasks. Thank you


Report •

#3
June 10, 2013 at 05:28:17
Right, but you haven't addressed the race condition. What happens if two customers try to reserve something at the same time? One of them is going to win the race and overwrite the other customer's entry. This is why you use the AUTO_INCREMENT feature of MySQL. http://en.wikipedia.org/wiki/Race_c...

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
June 10, 2013 at 05:55:32
Hi no this will not happen, if you examine the site you'll see that there is an instruction to lock the rooms before making the reservation.If you run that from the prompt or phpMyAdmin it will display all the available rooms of the kind you're looking for,and no one can access the site at that moment.

Report •

#5
June 10, 2013 at 06:40:35
So only one customer ever can use the site at the same time? You don't have faith in your friend's business, do you?

Look, it's obvious you're not reading the articles I'm linking, so I'll give you a thought experiment. I'm not drinking coffee, so it's not explained well. That's fine, because the Wikipedia entry explains it better.

1) Customer A reserves a room and hits 'Submit.' codCliente == 5.
2) Customer B reserves a different room and hits 'Submit.' codCliente == 5.
3) You insert the data into slot 5 and increment codCliente for Customer A. codCliente == 6.
4) You insert the data into slot 5 and increment codCliente for Customer B. codCliente == 6.
Now what? If you're lucky enough (read: smart enough to use UNIQUE constraints), you'll error out on Customer B, he'll be frustrated, but tries again. If you're not, you take Customer A's money, but don't reserve his room. If you're really unlucky, you've horribly corrupted your database records, and you'll need to spend all day manually adding and modifying records to get everything where it should be.

How To Ask Questions The Smart Way


Report •

#6
June 10, 2013 at 08:51:31
Oh boy, I didn' think about that.Of course the tutorial says that that site is for beginners like me and that it can be improved a lot. I read the articles you're linking but I have to admit that usually I don't understand what they mean I am sorry.Anyway I think I'll forget that last_id business and keep studying php and MySQL until I get it.Can you tell me something though? Am I right in wanting to create user interfaces (forms) for the hotel people to insert client's data, reservation dates etc or is there a better way to do it? Thank you.
PS> I just started studying php and MySQL not too long ago so please bear with me,I appreciate your efforts.

Report •

#7
June 10, 2013 at 10:14:36
What, for adding records one at a time? Sure. We as an industry really haven't found a better way to do this. I'd suggest reading a book on UI design, or at least working with the end users on the layout, ass well as using the tabindex attribute and possibly implementing hotkeys. The general rule for data entry is that you don't want the user to switch input devices. You can require a keyboard, a mouse, or touch, but not some combination of devices.

Of course, there are bulk inputs, but that's reserved for adding many records at once.

How To Ask Questions The Smart Way


Report •

#8
June 11, 2013 at 01:50:05
Hi finally I got it working, here is the line of code that does it:
$codiceCliente = mysql_ insert_id();
it works fine so this is solved. By the way I thought about what you said about customers wanting to make a reservation when the site is locked.Actually I looked at the code below and realized that you don't block the whole site but just one room of the available ones on a particular date, to make sure that that room is taken.I think for a beginner like me is a pretty good site of course it needs improvement now I have to put together all this, still there is a lot of work ahead and probably I'll be bothering you guys again.Thanks a lot for your help.

LOCK TABLES camere c READ, prenotazioni p READ,
supplementi READ, prezzi READ, clienti WRITE,
prenotazioni WRITE, supplementi_prenotati WRITE;
SET @inizioPeriodo = '2006-06-03';
SET @finePeriodo = '2006-06-10';
SELECT c.* FROM camere c
WHERE tipo = 'matrimoniale'
AND NOT EXISTS
(SELECT * FROM prenotazioni p WHERE p.camera = c.numero
AND (p.periodoDal < @finePeriodo and @inizioPeriodo < p.periodoAl)
);
The dates of course are old and will need to be changed in the price table.
About the layout that part is done I am pretty good at that, I need to work on getting the data from the webpage to the database.


Report •

#9
June 17, 2013 at 01:42:02
Hi the reason I have not picked you as the best answer is because I don.t know how to do it.I looked everywhere but don't see any button to click to do it, please tell me how and I'll do it.Thank you

Report •


Ask Question