Only show data that belongs to specific data

I am trying to make my data only visible to the place it belongs. I have three tables called employee_trips , employee_trip_comments and employees. when setting a trip i have the option to place a comment with it. when i place a comment the employee_id and trip_id are being grabbed from external tables and placed into the employee_trip_comments table.

The statement below is fetching all of the data i need from the employee_trip_comments table

$stmt = $pdo->prepare("SELECT comment_id, employee_trip_comments.employee_id, employee_trip_comments.trip_id, comment_datetime, comment_text,
      author_id, employees.full_name as 'author_name' FROM employee_trip_comments 
      INNER JOIN employee_trips ON employee_trips.trip_id = employee_trip_comments.trip_id
      INNER JOIN employees ON employees.employee_id = employee_trip_comments.author_id
      WHERE employee_trip_comments.employee_id = :employeeID AND employee_trip_comments.trip_id = '54' AND employee_trip_comments.week_end_date = :weekEndDate ORDER BY comment_id DESC");
$stmt->execute([':employeeID' => $employeeID, 'weekEndDate' => $weekEndDate]);
$tripComments = $stmt->fetchAll(PDO::FETCH_ASSOC);

i added trip_id ‘54’ in the where clause for testing purposes.

In my database i have three trips where two of them got a comment and 1 doesn’t. trip_id number 54 has a comment, however it’s comment is visible to every trip right now. I want it to be only visible to the trip with id number 54.

Below is an image of my db structure

Can I ask what output you’re looking to get? That might make it easier to visualise how to get it.

Is this your complete database layout, in particular are those all the columns in employee_trips? If this is after the conversation where we were talking about not having week_end_date in multiple tables, I suspect you have removed it from the wrong one. In my view (which may not be correct, if there is such a thing as “correct” here) it should look like:

“employee_trips” table would contain trip_id (unique id for the table), employee_id, date and week_end_date. (This last column is only if there’s some reason you can’t calculate it from the date column). This table is information about the trip itself.

“employee_trip_comments” table would contain comment_id (uid for the table), trip_id (reference into trips table), author_id, comment_datetime and comment_text (which probably wouldn’t be an integer column type, guessing they’re all just for testing).

There’s no need to duplicate the employee_id because that’s in the trip table. Presumably the author_id won’t be the same as the employee_id? If it will be the same, there’s no need to store it.

Thanks for your reply,

The output i am trying to get is, lets say i have three trips with trip_id number 1, 2 and 3 and i only placed a comment with trip_id 1. the image below is the output i want to get:

The comment i placed should only be visible to the trip with trip_id 1. However in my case, the comment is visible to all three trips instead of only the one it has been placed to. I also updated my ERD diagram picture, i accidentally forgot to add in the week_end_date column to the employee_trips table, i didn’t remove any columns just yet.

I suspect that querying on the employee_trips table with a LEFT JOIN into the employee_trip_comments may be the way to go, but I’m not sure how that would work with more than one comment per trip. I could picture a main query to retrieve the trips and a separate query inside the loop to retrieve comments, but that doesn’t seem like a nice way to do it.

No doubt someone will offer some insight.

Ok so to be clear, instead of a INNER JOIN on the employee_trips table i have to use a LEFT JOIN.

I had a quick look at this before posting, for your display I think a left join on the trips table would be better. That would return all the trips that you select, and all comments for those trips where there are some. If you use an inner join, you’ll only get the trips that have comments.

The key thing in your output (sometimes referred to as the “left” table) is that you want all those trips. It seems the difference is that the INNER will only return rows from the “left” table where there is a corresponding row in the “right” table (your comments table), where a LEFT join returns all matching rows from the left table, and whatever it can from the right table.

But, if you have more than one comment per trip, that’s where my knowledge is at an end. And as I mentioned above, I had to check the difference before posting as I’m not using this stuff often enough to be certain.

Your query will end up as something like

select col1, col2, col3 from employee_trips
left join employee_trip_comments on employee_trips.trip_id =
where employee_trip.week_end_date = whatever and employee_trip.employee_id = whatever

You’re now selecting the trips, not the comments, and bringing in related comments (and employee details) as required.

I think.

I actually never thought about it this way, it does make a lot of sense since you only grab related comments if they are set.

you’re absolutely right, it is not