Jump to content
n3rdnl

I need some help with the category part on a old project

Recommended Posts

I need some help with the category part on a old project .

The problem what I have at this moment that, when I click on the category all categories are showing up on the category.php page itself.

What whI wanne have when I click Uncategorised that in the category.php page only post are showing that have been made in Uncategorised category

function get_posts() {
    $sql = "SELECT 
              `blog`.`post_id` AS `id`,
              `blog`.`post_title` AS `title`,
              LEFT(`blog`.`post_body`, 512) AS `preview`,
              `blog`.`post_user` AS `user`,
              `blog_categories`.`id` AS `category_id`,
              `blog_categories`.`name` AS `category_name`,
              DATE_FORMAT(`blog`.`post_date`,  '%d-%m-%Y %H:%i') AS `date`,
              `blog_comments`.`total_comments`,           
              DATE_FORMAT(`blog_comments`.`last_comment`, '%d-%m-%Y %H:%i') AS `last_comment`
            FROM `blog`
               INNER JOIN `blog_categories` ON `blog_categories`.`id` = `blog`.`cat_id`
            LEFT JOIN ( 
                SELECT 
                   `post_id`,
                   COUNT(`comment_id`) AS `total_comments`,
                   MAX(`comment_date`) AS `last_comment`
                FROM `blog_comments`
                GROUP BY `post_id`
              ) AS `blog_comments`
              ON `blog`.`post_id` = `blog_comments`.`post_id`
              ORDER BY `blog`.`post_date` DESC";

    $posts = mysql_query($sql);

    $rows = array();
    while (($row = mysql_fetch_assoc($posts)) !== false) {
        $rows[]= array(
            'id'             => $row['id'],
            'title'          => $row['title'],
            'preview'        => $row['preview'],
            'user'           => $row['user'],   
            'date'           => $row['date'],           
            'category_id'    => $row['category_id'],    
            'category_name'  => $row['category_name'],
            'total_comments' => ($row['total_comments'] === null) ? 0 : $row['total_comments'],
            'last_comments'  => ($row['last_comment'] === null) ? 'never' : $row['last_comment']                                                            
        );
    }
    
    return $rows;
}

function get_post($pid) {
    $pid = (int)$pid;

    $sql =   "SELECT
                `post_title` AS `title`,
                `post_body` AS `body`,
                `post_user` AS `user`,
                `blog_categories`.`id` AS `category_id`,
                `blog_categories`.`name` AS `category_name`,
                DATE_FORMAT(`post_date`, '%d-%m-%Y %H:%i') AS `date`
            FROM `blog` 
                INNER JOIN `blog_categories` ON `blog_categories`.`id` = `blog`.`cat_id`
            WHERE `post_id` = '$pid'";

    $post = mysql_query($sql);
    $post = mysql_fetch_assoc($post);

    $post['blog_comments'] = get_comments($pid);

    return $post;      
}

 

The old way was like this 

   if ( isset($cat_id)) {
      $cat_id = (int) $cat_id;
      $query .= " WHERE `blog_categories`.`id` = $cat_id";
   }  

Database 

blog_categories

id | name |

----------- 

4 Uncategorised

1 Youtube

blog 

post_id | cat_id | post_title | post_body | post_user | post_date 

-----------------------------------------------------------------------------------

4 | 1 | Youtube | Youtube link | Admin | 2020-09-13 16:10:53
Web server
Apache/2.2.21 (Win64) PHP/5.3.10
MySQL-client versie: mysqlnd 5.0.8-dev - 20102224 - $Revision: 321634 $
PHP uitbreiding: mysqli Documentatie

phpMyAdmin
Versie informatie: 3.4.10.1, meest recente versie: 5.0.2

I hope someone can help me with this problem 

Share this post


Link to post
Share on other sites

Have you tried putting a WHERE clause in your query, for example

WHERE blog.cat_id = 4

  • Thanks 1

Share this post


Link to post
Share on other sites
12 hours ago, Barand said:

Have you tried putting a WHERE clause in your query, for example

WHERE blog.cat_id = 4

Thanks @Barand but where do i need to add this in my 

function get_posts() { $sql =

comments forgot to add this 

	#	Kolom	Type	Collatie	Attributen	Null	Standaardwaarde	Extra	Actie
	1	comment_id	int(6)			Nee	Geen	AUTO_INCREMENT	Veranderen Veranderen	Verwijderen Verwijderen	Meer Geef meer acties weer
	2	post_id	int(6)			Nee	Geen		Veranderen Veranderen	Verwijderen Verwijderen	Meer Geef meer acties weer
	3	comment_body	text	latin1_swedish_ci		Nee	Geen		Veranderen Veranderen	Verwijderen Verwijderen	Meer Geef meer acties weer
	4	comment_user	varchar(32)	latin1_swedish_ci		Nee	Geen		Veranderen Veranderen	Verwijderen Verwijderen	Meer Geef meer acties weer
	5	comment_date	datetime			Nee	Geen		Veranderen Veranderen	Verwijderen Verwijderen	Meer Geef meer acties weer
comment_id	post_id	comment_body	comment_user	comment_date
    24	        4	testing         Admin	        2020-09-13 16:59:15
    25	        7	testing1	    Admin	        2020-09-13 17:00:06

 

Edited by n3rdnl
forgot something

Share this post


Link to post
Share on other sites

You do it in the same way you have in your get_post($pid) function, only this time pass the category id

get_posts($cat_id)

  • Thanks 1

Share this post


Link to post
Share on other sites

This is what I have done but still it dont works 

When I click category.php?id=4 it still show me other categories on that page as well 

 

function cat_id($cat_id) {
    $posts = array();

    
    $query =  "SELECT `blog_categories`.`id` AS `blog_category_id`
	        FROM `blog`
            INNER JOIN `blog_categories` ON `blog_categories`.`id` = `blog`.`cat_id`";

		  if ( isset($cat_id)) {
		   	  $cat_id = (int) $cat_id;
		   	  $query .= " WHERE `blog_categories`.`id` = $cat_id";
		   }  

    $query = mysql_query($query);

   	while ( $row = mysql_fetch_assoc($query) ) {
		$posts[] = $row;
	}

	return $posts;     

}

 

Share this post


Link to post
Share on other sites

If $cat_id contains "" then the query will fail with a syntax error.

But we don't know what's in it, nor do we know what's in your table - and we certainly have no idea what "don't work" means unless you tell us.

Check if your query gave an error message.

  • Thanks 1

Share this post


Link to post
Share on other sites

 

Oke as u can see in the tables in the post what I have posted before 

What I am trying to do is 

 

I have made categories as u can see in this image .

image1.png.e0c51e62af050860f622e2d28b6140eb.png

 but here is the problem what I have when I click on the that category other categories are showing up as well  

See this image 

image2.png.b216812d183119e4f165471b12027019.png

What Iam trying to do is that on category page only post are showing up what hav e been posted in that category 

 

As u can see now that is not working at this moment .

 

 

 

Share this post


Link to post
Share on other sites

When I run this in phpmyadmin I get this 

SELECT `blog`.`post_id` AS `id` , `blog`.`post_title` AS `title` , `post_body` AS `body` , `blog_categories`.`id` AS `blog_category_id`
FROM `blog`
INNER JOIN `blog_categories` ON `blog_categories`.`id` = `blog`.`cat_id`
WHERE `blog_categories`.`id` = '1'

image3.png.50624865e0650347f0c86bb535cef48d.png

 

Share this post


Link to post
Share on other sites

Not knowing what data you have, I'm going to guess that is correct. Now do the same in your php code.

Share this post


Link to post
Share on other sites

I have fixed the problem finaly @Barand thanks for the help as well

 

function get_cat($cat_id = null) {
	$cat_id = (int)$cat_id;

    $sql = "SELECT 
    `blog`.`post_id` AS `id` , 
	`blog`.`post_title` AS `title`,
	`post_body` AS `body`,	
    `blog_categories`.`id` AS `blog_category_id`
FROM `blog`
INNER JOIN `blog_categories` ON `blog_categories`.`id` = `blog`.`cat_id`
WHERE `cat_id` = " . mysql_real_escape_string($_GET['id']);

	$cat = mysql_query($sql);

	$return = array();
	while (($row = mysql_fetch_assoc($cat)) ) {
		$return[] = $row;
	}

	return $return;		
}

 

 

Share this post


Link to post
Share on other sites

1 ) You don't need the mysql_real_escape_string. That is for strings (the clue is in the name). $cat_id is an int.

2 ) The mysql_xxxx functions are obsolete. If you ever get around to upgrading you version of PHP to someting less than 5 years old, they won't work. Switch to using PDO class.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.