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

[n3rdnl]

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 

[Barand]

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

WHERE blog.cat_id = 4

[n3rdnl]

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 September 15, 2020 by n3rdnl
forgot something

[Barand]

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)

[n3rdnl]

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;     

}

 

[Barand]

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.

[n3rdnl]

 

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 .

 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 

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 .

 

 

 

[n3rdnl]

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'

 

[Barand]

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

[n3rdnl]

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;		
}

 

 

[Barand]

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.