I am trying to remove the daily doctor sessions from the priem table. The sample should be on a daily date, i.e. How many patients each doctor took each day:

$result2 = mysqli_query($con,"SELECT prdate, fio, doctor_uid, COUNT(*) FROM priem WHERE doctor_uid=$uidforfaq GROUP BY prdate");

Here prdate is the column with the date of admission, fio is the patient's full name, doctor_uid is the unique identifier of each doctor. based on the above request how to display the number of admission in numbers. Those. for example 2017-06-14 - 4 receptions, 2017-06-13 - 2 receptions.

  • prdate, fio, doctor_uid by prdate, fio, doctor_uid , and not just by date. - Visman
  • I did that, but now each date represents the same number of receptions on that day. I would like a date once and the number next to it the number of receptions for that day. And show the names of patients inside this date - Alex Stassov
  • I did not correctly understand your name, it was read not as a patient, but as a doctor :) If you didn’t need the name of the patients, you could group by date and number of the doctor, and so - let someone else come up with a tricky query;) - Visman
  • one
    dev.mysql.com/doc/refman/5.7/en/… - Visman
  • one
    And you have there a date or a date with time. if the second, then when grouping, the time must be discarded (using the date() function). And so the group by prdate, doctor_uid should give the correct result. And fio except the list with the help of group_concat as in the comment above - Mike

1 answer 1

If the sample should be on a daily date, then the date should be specified in the sample conditions. Only then the sample will work as you want.

 SELECT prdate, fio, doctor_uid, COUNT(*) FROM priem WHERE doctor_uid=1 AND prdate = "2017-07-20" GROUP BY doctor_uid

Accordingly, for each date you will have one SQL query. It is doubtful that you have a billion visits and a million doctors, but if it turns out that the query is slow and the addition of indices does not help, then the results of such a query are conveniently cached: the data for the day before yesterday is unlikely to ever change.

If, nevertheless, in some way you get the entire data set with a single SQL query, then you will not be able to put it in the cache: you will have to execute it every time. Think maybe it is not profitable for you.

  • sanmai, thanks for the reply and for the help! - Alex Stassov