如果结果集中的值与当前登录的用户不匹配，则有条件地使用回退值

Question

我有下面的php脚本从数据库中获取数据，并将其返回到日历作为预订系统的一部分。标题字段$row["title"]实际上是每个预订的不同人的用户名。

一切都很好，但我想改变一些事情，这样每个用户只能在日历上看到自己的用户名，而不是其他人的。我希望他们看到的是'预订'。

我很新的php，但我的猜测是，我需要迭代创建的$data数组，只改变title字段，如果它不匹配登录的用户。我认为这将来自我的登录脚本:

$_SESSION["username"] = $username;     <=== I think this needs to be incorporated into the script and the php loop.

我要做的是将title字段替换为'预订'，如果它与登录的用户不匹配。

我也需要允许所有用户看到公共条目，比如，unavailable,holiday——所以title的值应该总是显示。

<?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);
?>

假设Mary已经登录了。数据数组看起来像这样:

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

但是我想在发送到日历之前将其更改为:

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

Answer 1

如果要访问会话数据，首先需要启动会话。然后你可以在脚本

中使用会话变量

<?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);

---

旁注，这只会正常工作，如果所有的用户名是唯一的，尽管

Answer 2

如果SESSION中的用户名与行标题相同，则显示行标题，否则显示已预订。

扩展:显示title值，当它匹配登录的用户名或如果它匹配公共/公共字符串，将它们全部堆积到IN()条件。

推荐:

$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));

---

如果您希望这是一个动态条件，您可以提前准备您的白名单数组:

$allowTitles = [
$_SESSION['username'],
'unavailable',
'holiday',
];

然后创建必要数量的占位符，并将数组提供给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));

注:我分享@DarkBee对数据库表中唯一名称的担忧。通常，您应该使用id来避免任何数据冲突的可能性。