Show only data that belongs to specific data using PHP

Question

I'm building a MYSQL generated PHP application. I have a table called students and a table called notes. The notes table will be used to put in any extra information about the student being advised, so that the advisor can go back and look at the information the next time they advise the student. I'm not sure how to go about this.

I want the advisor just the see the notes of the specific student they're advising and not others. I'm using an inner join to join the two tables along with a SELECT statement. Information from both tables will be posted to the notes page in an HTML form. How would I insert the data so that it can be viewed correctly? Thanks for the help in advance.

This is my select query (not whole page). This page shows the notes.

<?php
$query = "

SELECT
notes.Note_Title,
notes.Note_Desc,
notes.Note_Cont,
notes.Note_Date
from notes
INNER JOIN students 
ON notes.id = students.id
Where students.id = 'id'";

try {

$stmt = $db->prepare($query);
$stmt->execute();   
}
catch(PDOException $ex)

{

die("Opps that Query didn't work out: " . $ex->getMessage());

}
while($row = $stmt->fetch()){

echo'<div>';
echo'<tr>';
   echo '<td><h5><a href="notes_view.php?id='.$row['id'].'">'.$row['Note_Title'].'</a></h5></td>';
   echo '<td><p>Note created on '.date('JS M Y H:i:s', strtotime($row['Note_Date'])).'</p></td>';
   echo '<p>'.$row['Note_Desc'].'</p>';
   echo '<p><a href="notes_view.php?id='.$row['id'].'">Read Note</a></p>';
   echo '<p><a href="delete.php?id='.$row['id'].'">Delete note</a></p>';
   echo '<p><a href="edit_notes.php?id='.$row['id'].'">Edit</a></p>';
   echo '<hr width="100%">';
echo'</div>';
   }


$query = "INSERT INTO notes (
id,
Note_Title,
Note_Desc,
Note_Cont,
Note_Date

) VALUES (
:id,
:Note_Title,
:Note_Desc,
:Note_Cont,
:Note_Date
)
";

$query_go = array(
':id' => $id,
':Note_Title' => $Note_Title,
':Note_Desc' =>  $Note_Desc,
':Note_Cont' =>  $Note_Cont,
':Note_Date' =>  date('Y-m-d H:i:s')
);

try {

$stmt = $db->prepare($query);
$result = $stmt->execute($query_go);
}
catch(PDOException $ex)

{

die("Opps that query didn't work out: " . $ex->getMessage());

}
header('location: index.php?action=added');
exit;
}
}
?>

<form action='' method='POST'>

<input type='hidden' name='id' id='id' value='<?php if(isset($error)){echo $_POST['id'];}?>'>
<p><label>Note Title:</label><br />
<input type='text' name='Note_Title' value='<?php if(isset($error)){echo $_POST['Note_Title'];}?>'></p></td>

<p><label>Note description:</label><br />
<textarea name="Note_Desc" cols="40" rows="11"><?php if(isset($error)){echo $_POST['Note_Desc'];}?></textarea></p></td>

<p><label>Note content:</label><br />
<textarea name="Note_Cont" cols="70" rows="11"><?php if(isset($error)){echo $_POST['Note_Cont'];}?></textarea></p></td>

<p><input type="submit" name="submit" value="submit" ></p>
</form> 

Answer 1

Based on the code you've shown, it seems that your page shows a selected user's notes. This represents a "one to many" relationship; one user can have many notes, and each note can only be associated with one user.

In this situation, rather than using a JOIN, I would just select the notes for the desired user. To do this, include a user_id column in your notes table (a foreign key):

`id`
`user_id`,
`title`,
`description`,
`content`,
`date`

When you're on a user's notes page, select all notes for that user:

SELECT * from `notes` WHERE `user_id`=:user_id;

When editing a note, you don't really need the user_id (unless you're allowing the note to be reassigned to a different user). You can reference each note by its own unique id. Something like this:

<form action='' method='POST'>

    <input type='hidden' name='id' value='<?=$note['id']?>'>

    ....

</form>

Incidentally, you can use a JOIN to display the number of notes per user in a list of users. Something like this:

SELECT u.*,
       COUNT(n.`id`) as `note_count`
FROM `users` u
LEFT JOIN `notes` n ON (n.`user_id`=u.`id`)
GROUP BY u.`id`;

Also see How to store a one to many relation in my sql database?

Answer 2

I'm writing this just to add some clarity to what showdev has already said above. I'm making several assumptions here; that you already understand your database basics especially normalisation and how to use PHP to interact with MySQL. So I won't be writing codes. Just trying to explain the logic.

You have two tables students and notes. The relationship between the two is a one-to-many relationship. This simply means that one student can have many notes associated to him/her. But on the other hand, each single note will always belong to one particular student.

So in your notes table, add an additional field or attribute which you can name as 'student_id'. This new field should be a foreign key that links to (references) the id's of students in the students table.

So now if you want to see all the notes for a particular student, your query will become simpler. No need for joins.

SELECT * FROM notes WHERE student_id = :student_id

Of course, when you are inserting new notes you will have to include the id of the student that particular note belongs to.

Also instead of using PHP's date function to insert date into the notes table, you can allow MySQL to do that for you automatically. Change the Note_Date column in the notes table. Make it Note_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP. MySQL will automatically insert the current date and time for you. This will save you some bytes of network overhead.