How can I generate a unique Doctor Appointment ID

[sohailsaif]

Can some one help to generate a unique Doctor Appointment ID when some one save the registration form, I am trying to make online doctor appoint booking system, I want something like for each doctor, each date, it will generate a unique id, that may be something like following

 

Dr_ID-Date-Auto increment number( DR001-03-23-2016-0001 )

 

If any one have better idea to generate a short and unique id, please suggest me.

I am a beginner in PHP, please help me guys

 

[requinix]

You have those pieces of data in your database, right?

booking_id | doctor_id | date
-----------+-----------+-----------
         1 |         1 | 2016-03-23

Then combine those into the ID that you show to people. You can get the zero-padding with sprintf.

$friendlyid = sprintf("%03u-%s-%04u", $doctor_id, date("m-d-Y", $date), $booking_id);

Unless you're thinking of the auto-increment number being a per-customer number? Okay, but you have a problem now: these are not unique anymore, and if someone has a problem and emails you their ID then you don't know which person it corresponds to.

 

Couple other considerations:

 

- Four-digit number only allows for 9999 bookings. Surely you want to support more than that?

- An auto-increment ID will be easy to spot. You should probably (a) not start at 1 and not increment by 1, or (b) not use the auto-increment ID directly and instead do some math on it to get a "random" but still unique number.

[sohailsaif]

Please see the attached image, What I want is when patient will open the the Appointment Registration form, he/she can select doctors from list, select available dates, enter his/her name, address, mobile number and save the form, a booking code will be generated, time will be assigned(starting a time, there will be a increment of 10 minutes for each patient, for example see the appointment_time, row in the appointment table), a booking code will be generated(using doctor id, appointment date and 4 digit number which will be increment 1 for each patient for that selected date and doctor, please see the booking_code in appointment_table).

booking code will start if a patient select another date and if there is no appointment, then it will again start from DR001-27-03-2016-0001.

Hope you can understand my problem, If I some one can help me with full codes, I will be very thankfull, I am new in PHP, Please Help me.

Edited March 23, 2016 by sohailsaif

[Barand]

Normalize your data. You have info in both those those tables that doesn't belong. You should have

  +----------------+         +---------------------+        +-----------------+                                         
  | doctor         |         | appointment         |        | patient         |                                         
  +----------------+         +---------------------+        +-----------------+                                         
  | doctor_id (PK) |---+     | appointment_id (PK) |    +---| patient_id (PK) |                                         
  | doctorname     |   +----<| doctor_id      (FK) |    |   | patientname     |                                         
  +----------------+         | patient_id     (FK) |>---+   | address         |                                         
                             | app_time            |        | mobile          |                                         
                             +---------------------+        +-----------------+

If you want doctors with appointment dates, use a query

SELECT DISTINCT
    d.doctor_id
  , doctorname
  , DATE(app_time) as Appointment_dates
FROM appointment a
INNER JOIN doctor d USING (doctor_id);

+-----------+------------+-------------------+
| doctor_id | doctorname | Appointment_dates |
+-----------+------------+-------------------+
|         1 | Dr A       | 2016-03-23        |
|         1 | Dr A       | 2016-03-27        |
|         2 | Dr B       | 2016-03-24        |
|         2 | Dr B       | 2016-03-28        |
+-----------+------------+-------------------+

Store dates and time using sql DATETIME types. Other formats cannot be be compared and sorted. eg

mysql> SELECT * FROM appointment;
+----------------+-----------+------------+---------------------+
| appointment_id | doctor_id | patient_id | app_time            |
+----------------+-----------+------------+---------------------+
|              1 |         1 | 3243       | 2016-03-23 12:05:00 |
|              2 |         1 | 9574       | 2016-03-23 12:15:00 |
|              3 |         1 | 2174       | 2016-03-27 12:05:00 |
|              4 |         2 | 5474       | 2016-03-24 14:05:00 |
|              5 |         2 | 8763       | 2016-03-24 14:15:00 |
|              6 |         2 | 5674       | 2016-03-28 14:05:00 |
+----------------+-----------+------------+---------------------+

Also, you should not store derived data and your "appointment code" is derived from data already stored and by counting the appointments each day for each doctor. Having got the required data by query you would format it as shown by requinix.

 

Example

$sql = "SELECT 
            DATE_FORMAT(app_time, '%d-%m-%Y') as formatteddate
          , TIME_FORMAT(app_time, '%l:%i %p') as time
          , @seq := IF(@prevday=DATE(app_time) AND @prevdoc=doctor_id, @seq+1, 1) as seq
          , @prevdoc:=doctor_id as doctor_id
          , @prevday:=DATE(app_time) as date
        FROM 
            (
            SELECT doctor_id
              , app_time
            FROM appointment
                JOIN (SELECT @sequence:=0, @prevdoc:=0, @prevday:='1901-01-01') as init
            ORDER BY doctor_id, app_time
            ) apps";
$res = $pdo->query($sql);

echo "<pre>\n";
echo "Doctor_id    Date            Seq  Code\n";
foreach ($res as $row) {
    printf("    %d       %-15s  %3d  DR%03d-%s-%04d\n"
                , $row['doctor_id']
                , $row['date']
                , $row['seq']
                , $row['doctor_id']
                , $row['formatteddate'] 
                , $row['seq']  );
}
echo "</pre>\n";

Results:

Doctor_id    Date            Seq  Code
    1       2016-03-23         1  DR001-23-03-2016-0001
    1       2016-03-23         2  DR001-23-03-2016-0002
    1       2016-03-27         1  DR001-27-03-2016-0001
    2       2016-03-24         1  DR002-24-03-2016-0001
    2       2016-03-24         2  DR002-24-03-2016-0002
    2       2016-03-28         1  DR002-28-03-2016-0001

[Barand]

After some experimenting, here's another option for you but it will only work with a MyISAM table

 

Define a multi-column primary key so that the auto_incrementing column comes last (doctor_id, date, appointment_id)

CREATE TABLE `appointment` (
  `appointment_id` int(11) NOT NULL AUTO_INCREMENT,
  `doctor_id` int(11) NOT NULL DEFAULT '0',
  `patient_id` varchar(45) DEFAULT NULL,
  `app_date` date NOT NULL DEFAULT '0000-00-00',
  `app_time` time DEFAULT NULL,
  PRIMARY KEY (`doctor_id`,`app_date`,`appointment_id`)
) ENGINE=MyISAM ;

Also note I have separated date and time so the date can be used in the key without the time.

 

So in the table I now have

+----------------+-----------+------------+------------+----------+
| appointment_id | doctor_id | patient_id | app_date   | app_time |
+----------------+-----------+------------+------------+----------+
|              1 |         1 | 3243       | 2016-03-23 | 12:05:00 |
|              2 |         1 | 9574       | 2016-03-23 | 12:15:00 |
|              1 |         1 | 2174       | 2016-03-27 | 12:05:00 |
|              1 |         2 | 5474       | 2016-03-24 | 14:05:00 |
|              2 |         2 | 8763       | 2016-03-24 | 14:15:00 |
|              1 |         2 | 5674       | 2016-03-28 | 14:05:00 |
+----------------+-----------+------------+------------+----------+

If I now insert a new appointment for doctor #1 on the 23rd, the new record is created with id=3

INSERT INTO appointment (doctor_id, patient_id, app_date, app_time)
VALUES (1, 9991, '2016-03-23', '15:00');

+----------------+-----------+------------+------------+----------+
| appointment_id | doctor_id | patient_id | app_date   | app_time |
+----------------+-----------+------------+------------+----------+
|              1 |         1 | 3243       | 2016-03-23 | 12:05:00 |
|              2 |         1 | 9574       | 2016-03-23 | 12:15:00 |
|              1 |         1 | 2174       | 2016-03-27 | 12:05:00 |
|              1 |         2 | 5474       | 2016-03-24 | 14:05:00 |
|              2 |         2 | 8763       | 2016-03-24 | 14:15:00 |
|              1 |         2 | 5674       | 2016-03-28 | 14:05:00 |
|              3 |         1 | 9991       | 2016-03-23 | 15:00:00 |  <--- new
+----------------+-----------+------------+------------+----------+

Now all that is needed is to assemble your code

[sohailsaif]

Thanks Barand for you help, but Can you help me how auto assign time to each patient, suppose starting Time for DR A is 12:05PM, when first patient book his appointment, time 12:05 PM will be automatically assigned to him,/her, when second patient book appointment, then 12:15 PM will be assigned to him.her, for each patient there will be a increment of 10 minutes, how to do this?

[Barand]

So I ring the doctor's to arrange an appointment and the next timeslot is 12:15. Unfortunately it is impossible for me to get there before 2pm. Now what happens? Do I keep ringing back until the next available slot is 2:00 then take the appointment?

[sohailsaif]

Dear Barand, I am trying to develop something that, patient don't need to ring doctor, he/she can book the appointment online, If he/she reaches the clinic after specified time then he/she have to wait there for the next available slot.

[Barand]

OK, instead of phoning I keep trying online all morning until a suitable timeslot is offered to me. I have now wasted an hour of my life. Alternatively I arrive late and sit there for hours waiting for an appointment slot.

 

This is not a great user experience.

 

Having established which date I want and which doctor I want to see, why don't you then offer me a list of available times so I can select one that suits me?