MySQL表JOIN建议

Question

I wondering what the best advise would be to approach a couple of points. I have a working database where I am able to register new users, create what I call documents and also categories. The tables are set out as the following:

Users Tables
    `id` int(11) NOT NULL,
      `username` text NOT NULL,
      `password` varchar(64) NOT NULL,
      `psalt` text NOT NULL


Documents Table
`doc_id` int(11) NOT NULL,
  `doc_title` varchar(50) NOT NULL,
  `doc_content` text NOT NULL,
  `doc_created` datetime NOT NULL,
  `doc_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

Categories Table
`cat_id` int(11) NOT NULL,
  `cat_title` varchar(32) NOT NULL

So when I login as my 1 user currently which is Admin - which two tables join and do I need another table to join them? I am little unclear on how these become related as I tried using the tool to relate them in phpMyadmin (Not the best tool but it what I have to work with). the reason behind this is because if I am logged in as 'Admin' or another user and create a doc I want it to say it was published by that user.

Also, in regards to the documents, they will belong to a category. But I am not sure where the association is. The end result ultimately is that there with will be a number of users who can see different categories and depending on the category are the docs they see.

So, going by your advice I have managed to filter through some data and make it show with SQL.

SELECT user_login.id,user_login.username, doc_list.doc_title FROM user_login, doc_list WHERE user_login.id=user_id

I have added a new column in the doc_list table called 'user_id' and this is my relation as to who published it. Now, I manually entered the user_id into the fields to make sure I got some results back, but how I am from my php (see below) going to push the user id into the table so its automated?

<?php

if(isset($_POST["submit"])){
$hostname='localhost';
$username='######';
$password='######';

try {

$dbh = new PDO("mysql:host=$hostname;dbname=######",$username,$password);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line

$sql = "INSERT INTO doc_list (doc_title, doc_content, doc_created) VALUES ('".$_POST["doc_title"]."','".$_POST["doc_content"]."', NOW() )";


if ($dbh->query($sql)) {
    header ('Location: ../docList.php');
}
else{
}

$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

}
?>

Answer 1

You need to put the user-id which belongs to Users Table in Documents Table to provide "it was published by that user" and then join Users Tables with Documents Table.