Jump to content

How can I generate a unique Doctor Appointment ID


sohailsaif

Recommended Posts

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

8r8zw.jpg

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 by sohailsaif
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.