Hello! I'm learning php, doing a blog, trying to link comments to posts. That is, to make so that each comment was associated with its post. The problem is that I can’t figure out how to properly implement a query in order to show exactly related comments with posts, and not all at once. Here is the query, to which I painfully thought out, but it still does not display anything, is clearly wrong:

SELECT `commentID`, `name`, `content`, `post_id`, `date` FROM `blog_comments` WHERE `post_id` = `commentID`'

I understand that I need to associate postID with post_id in the blog_comments table. I thought of all this, but I cannot implement it correctly in the code, the knowledge of the syntax is too weak :) I would be very grateful for any help and direction, thanks a lot! Also my tables:

blog_posts:

 postID (Primary) int(11) No postTitle varchar(255) Yes NULL postDesc text Yes NULL postCont text Yes NULL postDate datetime Yes NULL

blog_comments

 commentID (Primary) int(11) No name varchar(55) No content varchar(255) No post_id int(11) No blog_posts -> postID date timestamp No CURRENT_TIMESTAMP

    1 answer 1

    In general, you are on the right path, the selection criteria you really should compare blog_comments.post_id and blog_posts.postId , but in the condition you for some reason compare other fields.

    Howbeit. On some page you show the post and its comments. To show the desired post, you probably have in the URL parameters its Id , which in php-code you get, for example, in the form $_GET['post_id'] .
    Next you need to select all the information about the post. To do this, you must write a query like:

     SELECT * FROM blog_posts WHERE postId = :id

    Make it a rule of good tone to never immediately substitute the passed parameters directly to the request, at least without prior verification. If you write a code of the form $sql = "select * from blog_posts where postId = ${_GET['id]}" , then when sending as an id a line like 1; delete from posts; 1; delete from posts; will delete your data. If you want to substitute a value directly into a query, then at least check it with is_numeric() or intval() . Do not forget to read about SQL injection

    To work with the database, use modern extensions, for example, PDO . While reading old books, you can often come across the use of mysql_* functions (not to be confused with mysqli_ ). They are already outdated.

    Given the above, your sample code should look something like this:

     $sth = $dbh->prepare('SELECT * FROM blog_posts WHERE postID = :id'); $sth->bindParam(':id', $id, PDO::PARAM_INT); $sth->execute(); $result = $sth->fetch(PDO::FETCH_ASSOC);

    Now you want to load related comments. Use a second query for this. In general, there is no need to link tables here:

     $sql = "SELECT * FROM blog_comments WHERE post_id = :id ORDER BY date DESC"; $sth = $dbh->prepare($sql); $sth->bindParam(':id', $id, PDO::PARAM_INT); $sth->execute(); $result['comments'] = $sth->fetchAll();

    Now the $result variable will contain post data and comments. It remains to show them to the user. Many people interfere with the work logic (php-code) and data mapping (presentation, html) in one file. This is a bad approach, so it is better to immediately look in the direction of template engines like , , etc.
    So now it remains to issue the html-code and output the data.

    And note on the naming of the fields of your tables. The primary key is usually referred to as id . It makes no sense to call them postId or commentId , so often called foreign key fields ( postId or post_id ). That is, the primary key is simply id , and the foreign key usually contains the name of the external table and the suffix id , _id , _ID , etc.

    Well, lastly, if you have read these lines before, then begin to learn php, take some time to study the databases. Read some small book that will give you an understanding of the basics of their use. And it will be easier for you to continue studying.

    • Thank you very much for such a good and detailed answer! I am learning from a new book in which I was taught to use mysqli, about outdated mysql in the course. I also teach Smarty, as well as code separation according to the MVC principle. It annoys all this terribly, but I try to inculcate good practices, and I’m worried about this as well about the sql queries and security. I still haven't really figured out pdo, the moments are a dark forest, mysqli is easier. I will try your suggestions in practice and still accomplish your goal, thank you very much! - Johnny Catsville