get and compare rows from three tables

[kaiman]

Hi Everyone,

 

I am working on developing a blog/comment system for my website. I have 3 tables, the first a blog post table with a row/primary key of id that is auto incremented as each new article is created via a form. The second table is a comments table with a row of article_id and the third table is a category table with an article_id row as well.

 

From the main blog page I have links that will take users to each individual blog post using $_GET like so:

 

http://www.mydomain.com/blog/article/?id={$row['id']}

 

From here I would like to be able to display the comment for that particular article below the article and also have a sidebar with links to blog categories which will then display each post according to whether they are in that category or not.

 

My question is two-fold:

 

First, what is the best way to pull the id field from the blog/post table, form, or url and enter it into the article_id rows of the second two tables? Should I use a hidden field, the URL id via $_GET, using a join, or what?

 

Secondly:

 

What is the best way to compare and display those rows so that the appropriate comments are being shown for the correct article, etc.

 

This is my first time doing this so I am grateful for any help or suggestions.

 

Thanks,

 

kaiman

[kaiman]

* BUMP *

 

Please let me know if I'm not being clear... Thanks!

[Psycho]

...the third table is a category table with an article_id row as well.

 

Can articles belong to multiple categories? If so, then you should use two tables for categories. One table will contain the list of available categories (category_id and category_name). And a second table would be used to associate posts to categories (article_id, category_id). If articles cannot belon to multiple categories, then I would still use the first table to list the available categories and then add a column to the articles table to specify the category_id.

 

First, what is the best way to pull the id field from the blog/post table, form, or url and enter it into the article_id rows of the second two tables? Should I use a hidden field, the URL id via $_GET, using a join, or what?

 

I'm not following you here. It seems you are mixing up different things in the same question. First, as to whether you should use a form (which can use GET or POST) or to simply have links with the ID appended to them, there is no right or wrong. Each method has its benefits and drawbacks. For the purpose you have explained I would simply append the ID to the URL as you showed above. But, you should always think about validating the data. If any posts are restricted you need to validate that the user has access to the post id being passed in the URL.

 

Once you have the post/article id everything else is fairly straitforward. In many instances you would use JOINs, but for what you have explained I would do three different queries: one for the post, one for the comments, and one for the categories.

 

For the categories, when listing them I would create them as links in the form

<a href="http://www.mydomain.com/blog/category/?id={$rcategory['id']}">[Category Name]

That page would then use the category ID to query all the relevant posts</a>

 

The one thing I am not sure of is if yuo want only the comments applicable to the currently displayed post to be diaplyed or all the posts. I have assumed the former in the code below.

 

So, the queries on the posts page would go something like this:

$postID = mysql_real_escape_string($_GET['id']);
$query = "SELECT field1, field2, etc. FROM articles WHERE id=$postID";
//Run query and use results to generate output of the post

$query = "SELECT field1, field2, etc. FROM comments WHERE article_id=$postID";
//Run query and use results to generate output for the comments

$query = "SELECT c.category_id, c.category_name, etc.
          FROM categories c
          JOIN post_catergories as pc --table to assoc posts to categories
          WHERE pc.article_id=$postID";
//Run query and use results to generate categories ouput

[kaiman]

@mjdamato

 

Thanks for the reply and helping me spell out more clearly what I am looking for.

 

Your advice on using two tables for categories is helpful. I was thinking of just having articles belong to one category, but your suggestion may make me rethink that scenario...

 

What I am ultimately trying to do with the categories part is build a link list in the sidebar (like wordpress does) that lists the categories for users to choose from and then when they click on one of the category links it will take them to a page that lists all articles/posts in that category.

 

Sorry about the confusion on the second part related to get, post and joins. What I am trying to determine is what is the best way to get the id of the post into the other two tables in the db so that when a user views a particular post that the comments and category related to that post show up. Your code examples more or less answered the query part, but how would I capture the id of the post from the first table and use it to display the correct comments from the outset? Should I use a hidden field in the comments form something like this:

 

$post_id = mysql_real_escape_string($_GET['id']);

 

<input type="hidden" value="<?php $post_id ?>" name="article_id" />

 

And then insert it into the comments table so that the id (or post_id) and article_id are the same?

 

Like I said this is my first time doing this, so I am wondering if this is the best/safest way to do this?

 

Thanks again for your help,

 

kaiman

 

T

 

 

[Psycho]

What I am trying to determine is what is the best way to get the id of the post into the other two tables in the db so that when a user views a particular post that the comments and category related to that post show up

 

I'm still not sure I fully understand what you are saying. I woud assume that the category(ies) are determined at the time the suer creates the initial post. Either as a selection by the user or selected automatically based upon the contect of where the user chooses to initiate the post creation (use a hidden field). So, I would expect you are asking how to associate new comments with the appropriate post. I would assume that on the page where a user views the post there is a link or a button to add a comment. If it is a link, then I would add the post ID as a parameter. If a button, then it should be a form with a hidden field for the post ID. Then, on the page where the comment is actually added, use the post ID (passed via GET or POST) and populate a hidden field so it will be available with the form submission for the comment.

 

Also, you would use mysql_real_escape_string() on the data received from the user. There is no benefit on running it against data the code puts into a form and could actually cause the script to fail for reasons I don't want to take the time to explain right now.

 

What I am ultimately trying to do with the categories part is build a link list in the sidebar (like wordpress does) that lists the categories for users to choose from and then when they click on one of the category links it will take them to a page that lists all articles/posts in that category.

 

 

I meant to add aditional information about that. So, as stated previously, the links for the categories would look something like this:

<a href="http://www.mydomain.com/blog/category/?id={$rcategory['id']}">[Category Name]</a>[code=php:0]

The page that is loaded from that link would have a query to find all posts associated with that categoryID. Based upon how the tables are constructed the query might look like this:
[code=php:0]$categoryID = mysql_real_escape_string($_GET['id']);
$query = "SELECT p.post_id, p.post_text, etc.
          FROM posts p
          JOIN post_catergories as pc
          WHERE pc.category_id=$categoryID";
//Run query and use results to generate output of posts for selected category

[kaiman]

@ mjdamato

 

Thanks again for the suggestions and input. That is the answer I was looking for.

 

I will use hidden input fields to get the post id and add it to the comments and categories tables.

 

Thanks for the advice on using mysql_real_escape_string and url's. I typically use it on any form fields going into a db, but will also use it when I pass url's to prevent sql injection there as well.

 

I am not totally sure by looking at your code that I understand how to do the join, but let me get the rest of this sorted out and I will post back if I have issues related to the last part.

 

Thanks again,

 

kaiman