Creating an array of appointments, sorted by date (MySQL/PHP)

Question

I am building a website where users can reserve appointments.

I am currently building a javascript app for the project, where user can pick a date from a calendar and see the available appointments. When I am building the calendar, I need to color dates by available appointments (ex. green if there's any).

In order to do that I need to iterate a javascript array that contains all available reservations. At the moment it looks something like this:

[Object, Object, Object...]

Where the object is a javascript object that contains appointment info. Here is the php service that serves the JSON:

<?php
require_once('../include/dbconnect.php');

$sql = "SELECT appointment.example,... 
person.example,... 
FROM appointment, person
WHERE appointment.reserved=0";

$stmt = $db->prepare($sql);
$stmt->execute();

$array = array();
while($row = $stmt->fetchObject()){
array_push($array, $row);
}

echo json_encode($array);
?>

So this finally brings us to the question.

For easier javascript array scanning, I'd need an array/object includes appointments arranged/sorted by date. Then when I am creating an element that represents the date, I can check the object for matching data. Data like this:

{
15.09.2012 : Object,
16.09.2012 : Object{
    appointment1 : Object,
    appointment2 : Object
}

}

In the database an appointment has an attribute "date" which is currently a string like "16.09.2012". Should I also change it to unix timestamp?

How should I change the PHP service to output a JSON object that includes appointments filed under dates?

Why in your sql request, you don't add a `ORDER BY appointment.date_field` ? — Alain Tiemblo, Sep 15 '12 at 10:15

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

A possible solution is to use associative arrays in php :

$assoc = array("key" => "value");

When you fetch your database records you can do something like this :

$array = array();
while($row = $stmt->fetchObject()){
$array[$row -> date] = $row;
}

echo json_encode($array);

For sorting, you can use the ksort ( http://php.net/manual/en/function.ksort.php ) php function, to sort the array by key.

Now you will have a Javascript Object and not Javascript array. Now you can iterate the object with for .. in javascript loop ( How to Loop through plain JavaScript object with objects as members? )

edited May 23 '17 at 12:12

Community

1
1

answered Sep 15 '12 at 10:20

Radoslav Georgiev

1,366
8
15

Thank you, this did exactly what I wanted. Great! – Klemola Sep 15 '12 at 10:34
I noticed something. I added two appointments with the same date. The above loop only adds one appointment per day. I guess I have to add a if/else condition that makes a subarray if two or more appointments share the same date? – Klemola Sep 15 '12 at 11:12
Well, you can just store in the following format : appointment_date => array of appointments And just check if the key exists - add to the array, else, create new array and insert the appointment – Radoslav Georgiev Sep 15 '12 at 11:17

score 1 · Answer 2 · answered Sep 15 '12 at 10:24

You can give try as below:

$sql = "SELECT appointment.example,... 
person.example,... 
FROM appointment, person
WHERE appointment.reserved=0 ORDER BY appointment.date_field ASC";

$stmt = $db->prepare($sql);
$stmt->execute();

$array = array();
while($row = $stmt->fetchObject()){
$array[$row->date_field][]=$row;
array_push($array, $row);
}

echo json_encode($array);
?>

score 1 · Answer 3 · answered Sep 15 '12 at 11:14

You don't need to construct an object consisting of date keys. Instead:

include the dates in your current structure and sort the array by date:

<?php
require_once('../include/dbconnect.php');

$stmt = $db->query('
  SELECT   DATE(appointment.datetime) AS date,
           appointment.datetime,
           appointment.reserved,
           appointment.example,     -- do you really want to send sensitive
           person.example           -- data about appointments to the browser?
  FROM     appointment JOIN person ON ...
  WHERE    appointment.reserved = 0 -- should this be = 1?
  ORDER BY appointment.datetime
');

echo json_encode($stmt->fetchAll(PDO::FETCH_OBJ));
?>

then skip over matching days when progressing through the array:

// loop over all dates displayed in calendar
for (
  currentDate  = startDate, i = 0;
  currentDate <= endDate;
  currentDate.setDate(currentDate.getDate() + 1)
){

  // determine whether the current date is booked
  // and advance pointer to next date in array
  for (
    booked = false;
    i < arr.length && arr[i].date == currentDate;
    ++i
  ) booked |= arr[i].reserved;  // or just `booked = true` if query
                                // returned only booked appointments

  // apply appropriate CSS to currentDate
  // ...
}

You might even consider reducing the client-side overhead by first returning a JSON array only of booked dates (in which case the inner loop above could be replaced with a simple if statement):

SELECT   DISTINCT DATE(appointment.datetime) AS date
FROM     appointment JOIN person ON ...
WHERE    appointment.reserved = 0 -- should this be = 1?
ORDER BY date

And then, once the user selects a date, make a further query for the bookings on that date:

SELECT   appointment.datetime
FROM     appointment JOIN person ON ...
WHERE    appointment.reserved = 0 -- should this be = 1?
     AND DATE(appointment.datetime) = ?
ORDER BY appointment.datetime

I'd like to thank you for this, but I needed the appointments object because I am using it after user selects a day with free appointments (to list available appointments for that day). This is also why I am fetching only unreserved appointments. Your design may come in handy later on, in a different case. — Klemola, Sep 18 '12 at 07:39

Creating an array of appointments, sorted by date (MySQL/PHP)

3 Answers3