MySQL and PHP - Check if user has access to data
I have a crossreference table owner_meeting_crossref with a column (owner_id) for owner IDs and column (meeting_i开发者_开发技巧d) for meeting IDs that the owner has access to.
My php script is sent an array of meetings, and I know the current user ID is $current_user_id. How can I efficiently check that the user (owner) has access to the meeting IDs sent in?
One option would be to fetch the meetings from the list that the user has access to, then calculate the difference.
$meetingStr = implode(',', array_map('intval', $meetings));
$accessibleMeetingQuery = $db->prepare("
SELECT meeting_id
FROM owner_meeting_crossref
WHERE owner_id=:uid AND meeting_id IN ($meetingStr)
");
$accessibleMeetingQuery->execute(array(':uid' => $current_user_id));
$accessibleMeetings= $accessibleMeetingQuery->fetchAll(PDO::FETCH_COLUMN);
$inaccessibleMeetings = array_diff($meetings, $accessibleMeetings);
Another would be to do it all from SQL
SELECT m.id
FROM meetings AS m
LEFT JOIN owner_meeting_crossref AS om
ON m.id=om.meeting_id AND om.owner_id=:uid
WHERE m.id IN ($meetingStr)
AND om.meeting_id IS NULL
精彩评论