Welcome to WebmasterWorld Guest from 54.161.118.57

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Print all post under it's category

     
8:31 pm on Oct 23, 2011 (gmt 0)

New User

5+ Year Member

joined:Sept 8, 2010
posts: 27
votes: 0


Hello guys! I just cant figure how to do this...

I have three database tables one for post, category and the bride called post group.

Now, I want to be able to print all post under it's category by requesting in browser index.php?category=Uncategorized.

Uncategorized
|---------------- First Article
|---------------- Second Article


Tutorials
|---------------- Wordpress
|---------------- PHP
|---------------- Video Games


I have this database schema:

Post DB:
post_id
post_title


Post Cat:
post_cat_id
post_cat_title


Post Group:
post_group_id
post_group_post_id
post_group_cat_id


Code:
$get_ID = $_GET['category'];
$group = mysql_query( "SELECT * FROM " . POST_GROUP_TBL . " WHERE post_group_post_id = $get_ID" ) or die( mysql_error() );
$row_group = mysql_fetch_object( $group );

$cat = mysql_query( "SELECT * FROM " . POST_CAT_TBL . " WHERE post_cat_id = $row_group->post_group_cat_id" ) or die( mysql_error() );
$row_cat = mysql_fetch_object( $cat );

$result = mysql_query( "SELECT * FROM " . POST_TBL ) or die( mysql_error() );
while( $row = mysql_fetch_object( $result ) )
{
echo $row->post_title . '<br />';
}


Thank you so much for your time reading this flea of mine because ive been bashing my keyboard for a while now..
8:16 am on Oct 24, 2011 (gmt 0)

New User

5+ Year Member

joined:Sept 8, 2010
posts: 27
votes: 0


Turns out that I fixed my problem by wrapping all queries in while statement. See the code below...

$get_ID = $_GET['category'];

$cat = mysql_query( "SELECT post_cat_id, post_cat_title, post_cat_slug, post_cat_content FROM " . POST_CAT_TBL . " WHERE post_cat_slug = '$get_ID'" ) or die( mysql_error() );
while( $row_cat = mysql_fetch_object( $cat ) )
{
$group = mysql_query( "SELECT post_group_id, post_group_post_id, post_group_cat_id FROM " . POST_GROUP_TBL . " WHERE post_group_cat_id = $row_cat->post_cat_id" ) or die( mysql_error() );
while( $row_group = mysql_fetch_object( $group ) )
{
$result = mysql_query( "SELECT post_id, post_title FROM " . POST_TBL . " WHERE post_id = $row_group->post_group_post_id" ) or die( mysql_error() );
while( $row = mysql_fetch_object( $result ) )
{
echo $row->post_title . '<br />';
}
}
}
8:15 am on Oct 25, 2011 (gmt 0)

Full Member

10+ Year Member

joined:Jan 4, 2007
posts:221
votes: 0


A little inefficient try something like

(not tested so may contain nuts and/or errors, and I've put in hardwired table names for readability. And assuming mysql)

$sql = "SELECT post.*, post_cat.*
FROM post_cat INNER JOIN post_group ON post_group_cat_id=post_group_id
INNER JOIN post ON post_group_post_id=post_id
WHERE post_cat_slug='" . mysql_real_escape_string($get_ID) . "'"

(If you're not using mysql you'll need to find the appropriate function to sanitise user input instead of mysql_real_escape_string. If you are using PHP and have access to the mysqli functionality it would be better still to use prepared statements [php.net].)

BTW, will you ever need to have a post held under multiple categories? If not, then you don't need the post_group table. Just add a cat reference to the post table instead
9:23 am on Oct 28, 2011 (gmt 0)

New User

5+ Year Member

joined:Sept 8, 2010
posts: 27
votes: 0


@Status_203 - Thank you for the reply. I'll do as you say.