Conditionally use fallback value if value in resultset does not match currently logged in user

Question

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"}
]

how do you store your booking client info in db, It may be possible to reach a conclusion through query — jiali, Aug 19 '21 at 11:04
The user clicks on an available slot in FullCalendar. I take the logged in username and send it to another script as 'title' variable, along with the start and end time. This submits a pdo to the database. The ID is auto-increment in mysql database. To display the database, I get the data back using the script above. It works fine, but I want to replace all instances of title that don't match the current user. Something like 'booked' or 'unavailable'. I want all users to see what's booked and available, but not by who. — Liam, Aug 19 '21 at 11:13
You can remove the entire `foreach()` block of code if you simply declare the columns that you desire in your SELECT clause -- this is the whole point of `->fetchAll()`. — mickmackusa, Aug 19 '21 at 12:14

score 1 · Answer 1 · answered Aug 19 '21 at 12:19

If you want to access session data you'd first need to start the session. Then you can just use the session variables in the script

<?php
    session_start();
    
    $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' => isset($_SESSION['username']) && $row['title'] == $_SESSION['username'] ? $row['title'] : 'booked',
            'start' => $row['start_event'],
            'end'   => $row['end_event']
        );
    }
    echo json_encode($data);

sidenote, this will only work properly if all the usernames are unique though

mickmackusa · Accepted Answer · 2021-08-19T16:02:57.420

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.

Sorry, I just noticed this after accepting the other answer. I haven't tried your solution. I'm sure it works, but I'm happy with the any solution that works, so I'm good. Thank you, I appreciate your effort. — Liam, Aug 19 '21 at 12:38
Correct, you should not be extending your requirements on this page. Please remove any obsolete comments. — mickmackusa, Aug 19 '21 at 16:06
Wow, this is freaking awesome! It works perfectly and is highly customizable. Thank you so much for your efforts here. Apologies to @DarkBee, I have to mark this as the solution. Outstanding. — Liam, Aug 19 '21 at 16:18

Conditionally use fallback value if value in resultset does not match currently logged in user

2 Answers2