-1

I have a php browser based application that is for a hospital management system. The situation is that there are 3 departments from which payments are made ie. reception, lab and pharmacy. The worker at each location above is different and so is their input. Each of these departments sends a receipt information to one single table called receipts and hence an ID is generated which is the next ID after the last ID of the table. The data is sent back to the same department based on this ID (that was generated based on the last ID from the table).

Now the problem occurs when two departments click the submit button at the same time. Both these people get the same ID since at that time the last ID is the same for both queries. This will cause problems for storing and sending back data to the department.

Now is there a solution for this? I was told triggers would solve it but I don't want to go there and keep it simple. I thought of random ID generation but the hospital guys want continuous ID as it will appear on the receipt. Also take into account that the system should not slow down (considerably).

EDIT: Whoops seems like there is a lot more info here hence Autoincrement is not working. There are 3 columns to consider ie. id(Pkey), receiptno and debitno. Now if the person pays at the same time then the id and receiptno will be increased by one together and debitno will be empty. But if he is going to pay later then his receiptno is going to be NULL while his ID and debitno will increase by one from the last ID entered. Therefore it is not necessary that the receiptno (that is going to be sent back to the department person) will be filled and hence auto increment would not work then correct? Thanks guys again for your solutions esp autoincrement.

Shakir
  • 273
  • 1
  • 5
  • 14
  • Who the heck told you triggers could solve this? You need to stop listening to them. – Ignacio Vazquez-Abrams Oct 12 '11 at 06:34
  • This is a problem long since solved with `auto_increment` primary key columns. What kind of id generation mechanism are you using? – deceze Oct 12 '11 at 06:35
  • And how the heck did you even consider random ids a good idea? – ThiefMaster Oct 12 '11 at 06:35
  • Why do they have you building their app? – Luchian Grigore Oct 12 '11 at 06:35
  • hehe, i was asked this question and given this solution by another person. random id since no two ids can be the same. – Shakir Oct 12 '11 at 06:40
  • i got one solution when you said unique. Is it possible to make the receiptno column UNIQUE. When two inserts are being made (although they have mistakenly got the same receiptno as say 49) then on the php coding, check to see if mysql is giving an error. If it is not then continue or else add "1" to receiptno then insert again. I am still very new to php so feel free to correct me (politely lol) – Shakir Oct 12 '11 at 09:02

2 Answers2

9

Use an AUTO_INCREMENT (MySQL) field for the ID column so the database takes care of unique ID generation. Other database systems have similar fields, e.g. the serial field type in PostgreSQL.

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
  • yes this is correct but in order to retrieve the data, I have to check the last ID and send it back. If both send at the same time, its possible that the first request gets the ID(info) of the second request – Shakir Oct 12 '11 at 06:38
  • 2
    If you retrieve the ID directly after the insert is performed, that never happens. Ever. You're not the only person that needs to retrieve IDs after an insert has been performed, and this answer is the correct answer :) – Christian Oct 12 '11 at 06:42
  • http://us2.php.net/manual/en/function.mysql-insert-id.php – Adam Trachtenberg Oct 12 '11 at 06:49
  • i have edited the post, would autoincrement still work? – Shakir Oct 12 '11 at 06:58
  • The autoincrement occurs on insert; as it is (deliberately) impossible for two inserts to happen at the same time, there is no duplication. – Ignacio Vazquez-Abrams Oct 12 '11 at 07:04
  • @ChristianVarga is correct about *retrieving* the ID. However, if he's using custom ID generation (SELECT MAX(id) and then using that value +1) he may very well get duplicate IDs - and apparently his ID column is not properly marked as primary key so the INSERT won't fail in that case either. – ThiefMaster Oct 12 '11 at 07:09
  • Autoincrement (as suggested in this answer) is not custom ID generation. – Ignacio Vazquez-Abrams Oct 12 '11 at 07:14
1

I read the update to your question, and you should really break up that database. Just so I understand - you need to create a new order with a unique ID. But the order might not be paid straight away, so when the order is paid, that also needs a unique receipt number. You could generate a random number for receipt, make the field unique, and while-no-error update the field in the database. This is terribly yucky.

You'd be better off creating a new table for payments. So your orders table would store an autoincremented orderID (as per TheifMasters answer), and the other data like userID, customerID, date, description etc. Then you'd have a payments/transactions table, which would store the paymentID (autoincremented), orderID (which relates back to the orders table), payment date, status, amount etc etc.

This is the preferred method of tracking payment transactions. Now that you know where to start, you should definitely do some research on how to actually implement this. We use relational databases for a reason!

Christian
  • 19,605
  • 3
  • 54
  • 70
  • yes lol i agree, tht would make it efficient. I was asked to make changes to the existing structure thats why i wanted a basic solution itself. The thing which i would mostly be told back by the person is that it would make the system slower. Will it? If not then this is the best solution. The system has a lot of components and i fear he fears that system will get laggier – Shakir Oct 12 '11 at 10:49
  • Long story short: it won't make the system slower. If the transaction data is stored with the order data, that is a massive flaw in the system. You shouldn't be trying to work with the flaws, you should be trying to fix them. Payment and receipt data is extremely important so if there's one part of this app you write properly, make it this part. – Christian Oct 12 '11 at 11:16