Trouble getting a category when including a topic in a page in PHP-CodePudding

I'm stuck on a query.

My goal is to get the information of that data and all the information belonging to the category of that data in a single query.

I have a usable code but the function is faulty. The error is that how many categories there are, the same data as the number of categories. rotate

My fault: https://prnt.sc/OzPLaGHFMGP5

My table where I store my data: https://prnt.sc/PEqSq2mM5NKM

My table where I keep my categories: https://prnt.sc/WK7n8kAGoUWD

here is my code to pull my data into the page:

if (!isset($_GET['url']) || empty($_GET['url'])){
    header('Location:404.php');
}

$datas = $db->prepare('SELECT * FROM post WHERE url = ?');
$datas->execute([
    $_GET['url']
]);
$data = $data->fetch(PDO::FETCH_ASSOC);

if (!$data){
    header('Location:404.php');
    exit;
}

here is my code where I pull the data category:

<?php $categories = $db->query('SELECT Categories.*, COUNT(posts.id) as totalCategory FROM categories
LEFT JOIN posts ON FIND_IN_SET(categories.id, posts.category_id)
GROUP BY Category.id DESC')->fetchAll(PDO::FETCH_ASSOC); ?>
<?php foreach ($category as $category): ?>
<div >
    <div >
        <div >
            <div >
                <div >
                    <i ></i> Category: <a href="<?= 'category/' .seo($data['url']) ?>"><?=$category[ ' category_name']?></a>
                    <a ><?=$data['date']?> <i ></i></a>
                </div>
                <div >
                    <?=htmlspecialchars_decode($data['content'])?>
                </div>
            </div>
        </div>
    </div>
<?php endforeach; ?>

So what is wrong with my query, what would you suggest?

CodePudding user response：

So assuming your tables are called "categories" and "posts".

Based upon what you've currently given me within your question, i made the following query:

SELECT 
 categories.*,
 COUNT(`posts`.`id`) AS `totalPosts`
FROM `categories`
LEFT JOIN `posts` ON `posts`.`category_id` = `categories`.`id` 
ORDER BY `categories`.`id` DESC

It basically gets everything from categories and a count of how many posts are within that category. Then we do a left join to get the information that corresponds to the category id, and then we descend it based upon category id DESC (so going downwards).

Correct me if i'm mistaken in what you were asking