Jump to content

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


n3rdnl

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 

Link to comment
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
Link to comment
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;     

}

 

Link to comment
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 .

 

 

 

Link to comment
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

 

Link to comment
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;		
}

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.