Is there a way to assign ownership of a note in the 'notes' table to a user from the 'users' table?
For example, how can I make user with id "1" in the 'users' table the owner of the note with id "2" in the 'notes' table?
UPDATE: I am utilizing a logged-in session for 'users'
The structure of the 'users' table is as follows:
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL DEFAULT '',
`email` varchar(50) NOT NULL DEFAULT '',
`password` varchar(70) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
And this is how the 'notes' table is structured:
CREATE TABLE `notes` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`title` text NOT NULL,
`content` text NOT NULL,
`created` TIMESTAMP DEFAULT now(),
`edited` TIMESTAMP DEFAULT now() ON UPDATE now(),
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Below is my current code for handling notes and I need to include functionality to link 'users' as described above:
public function fetchNotes($id = null) {
$stmt = $this->pdo->prepare('SELECT title, content FROM notes WHERE id = :id');
$stmt->bindParam(':id', $id);
$stmt->execute();
}
public function create($title, $content) {
$stmt = $this->pdo->prepare('INSERT INTO notes (title, content, created) VALUES (:title, :content, null)');
$stmt->bindParam(':title', $title);
$stmt->bindParam(':content', $content);
$stmt->execute();
}
public function delete($id) {
$stmt = $this->pdo->prepare('DELETE FROM notes WHERE id = :id');
$stmt->bindParam(':id', $id);
$stmt->execute();
}
public function edit($id, $title, $content) {
$stmt = $this->pdo->prepare('UPDATE notes SET title = :title, content = :content WHERE id = :id');
$stmt->bindParam(':id', $id);
$stmt->bindParam(':title', $title);
$stmt->bindParam(':content', $content);
$stmt->execute();
}