I have the php script below to get data from a database and return it to a calendar as part of a booking system. The title field, $row["title"]
, is actually the username of different people for each booking.
Everything works well, but I want to change things so that each user can only see their own username on the calendar, not each other’s. I want them to see 'booked' instead.
I’m pretty new to php, but my guess is that I need to iterate over the created $data
array, changing only the title
field if it doesn’t match the logged in user. I’m thinking this would come from this in my login script:
$_SESSION["username"] = $username; <=== I think this needs to be incorporated into the script and the php loop.
What I am trying to do is replace the title
field with ‘booked’ if it doesn’t match the logged in user.
I also need to allow all users to see public entries too, say, unavailable
, holiday
-- so those title
values should always be shown.
<?php
$connect = new PDO('mysql:host=localhost;dbname=xxx', 'xxx', 'xxx');
$data = array();
$query = "SELECT * FROM events ORDER BY id";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$data[] = array(
'id' => $row["id"],
'title' => $row["title"],
'start' => $row["start_event"],
'end' => $row["end_event"]
);
}
echo json_encode($data);
?>
Let's say Mary is logged in. The data array will look like this:
[
{"id":"365","title":"Kerry","start":"2021-08-19 20:00:00","end":"2021-08-19 20:40:00"},
{"id":"366","title":"John","start":"2021-08-19 19:00:00","end":"2021-08-19 19:40:00"},
{"id":"367","title":"Mary","start":"2021-08-20 10:00:00","end":"2021-08-20 10:40:00"},
{"id":"368","title":"Mary","start":"2021-08-20 12:00:00","end":"2021-08-20 12:40:00"},
{"id":"369","title":"Betty","start":"2021-08-20 15:00:00","end":"2021-08-20 15:40:00"}
]
But I want to change it to this before sending it to the calendar:
[
{"id":"365","title":"booked","start":"2021-08-19 20:00:00","end":"2021-08-19 20:40:00"},
{"id":"366","title":"booked ","start":"2021-08-19 19:00:00","end":"2021-08-19 19:40:00"},
{"id":"367","title":"Mary","start":"2021-08-20 10:00:00","end":"2021-08-20 10:40:00"},
{"id":"368","title":"Mary","start":"2021-08-20 12:00:00","end":"2021-08-20 12:40:00"},
{"id":"369","title":"booked","start":"2021-08-20 15:00:00","end":"2021-08-20 15:40:00"}
]
If the the username in the SESSION is the same as the row's title, then show the title, otherwise show booked.
Extension: To show the title
value when it matches the logged in user's name OR if it matches so communal/public strings, pile them all into an IN()
condition.
Recommendation:
$sql = "SELECT id,
IF(title IN (?,'unavailable','holiday'), title, 'booked') AS title,
start_event AS start,
end_event AS end
FROM events
ORDER BY id";
$statement = $connect->prepare($sql);
$statement->execute([$_SESSION['username']]);
echo json_encode($statement->fetchAll(PDO::FETCH_ASSOC));
If you want this to be a dynamic condition, you can prepare your whitelist array in advance:
$allowTitles = [
$_SESSION['username'],
'unavailable',
'holiday',
];
Then you create the necessary number of placeholders and feed the array to execute()
.
$placeholders = implode(',', array_fill(0, count($allowTitles), '?'));
$sql = "SELECT id,
IF(title IN ($placeholders), title, 'booked') AS title,
start_event AS start,
end_event AS end
FROM events
ORDER BY id";
$statement = $connect->prepare($sql);
$statement->execute($allowTitles);
echo json_encode($statement->fetchAll(PDO::FETCH_ASSOC));
P.S. I share @DarkBee's concern regarding unique names in your db table. Typically you should use ids to avoid any chance of data collisions.