Help With db Coding

[OldGrim]

I can't figure out how to code my added db query to get the desired results. This is a blog system written in procedural php without a framework. This is the original code that displays all of my categories:

<?php
    $runq = mysqli_query($connect, "SELECT * FROM `categories` ORDER BY category ASC");
    while ($row = mysqli_fetch_assoc($runq)) {
        $category_id = $row['id'];
        $queryac     = mysqli_query($connect, "SELECT * FROM `posts` WHERE category_id = '$category_id' and active='Yes'");
        echo '
            <a href="category.php?id=' . $row['id'] . '" class="list-group-item list-group-item-action"><i class="fa fa-arrow-right""></i>&nbsp; ' . $row['category'] . '</a>
		';
    }
?>

This produces an ascending list of categories. What I am trying to do is modify the queries to produce that same list but with a post count for each category thusly:

Science [3]

Technology [7]

etc etc etc

Here is my modified coding:

<?php
    $runq = mysqli_query($connect, "SELECT * FROM `categories` ORDER BY category ASC");
    while ($row = mysqli_fetch_assoc($runq)) {
        $category_id = $row['id'];
        $queryac     = mysqli_query($connect, "SELECT * FROM `posts` WHERE category_id = '$category_id' and active='Yes'");
        while ($row = mysqli_fetch_assoc($queryac)) {
        $cat_id = count($row['category_id']);
        echo '
            <a href="category.php?id=' . $row['id'] . '" class="list-group-item list-group-item-action"><i class="fa fa-arrow-right""></i>&nbsp; ' . $row['category'] . ' '.[ $cat_id ].'</a>
		';
            }
    }
?>

This produces one line for each category that has posts thusly: Array. I don't know how to properly code the second query to get a post count. Any help would be appreciated.

[ginerjm]

Why does this topic resemble very strongly another one that is currently being posted in 2 separate pieces?

[Barand]

Don't run queries inside loops. Use a single query with a JOIN.

Don't use SELECT *. Specify the columns you need.

SELECT c.id
     , c.category
     , COUNT(p.id) as numposts
FROM categories c
     LEFT JOIN posts p
          ON c.id = p.category_id
          AND active = 'Yes'
GROUP BY c.id
ORDER BY category;

 

[OldGrim]

Hey Barand long time huh? OK I'll try your suggestion, thanks.

[OldGrim]

Barand I tried your solution and it doesn't work (for me) as I don't knw how to code the result of your code however, I found the solution on my own and it works great! Here is the final result:

<?php
    $runq = mysqli_query($connect, "SELECT * FROM `categories` ORDER BY category ASC");
    while ($row = mysqli_fetch_assoc($runq)) {
        $category_id = $row['id'];
        $queryac     = mysqli_query($connect, "SELECT * FROM `posts` WHERE category_id = '$category_id' and active='Yes'");
        $count = mysqli_num_rows($queryac);
        $cntrow = $count;
	if ($cntrow == '0' OR $cntrow > '1') { $tc = "s"; } else { $tc = ""; }
        echo '
            <a href="category.php?id=' . $row['id'] . '" class="list-group-item list-group-item-action"><i class="fa fa-arrow-right""></i>&nbsp; ' . $row['category'] . ' - '.$cntrow.' article'.$tc.'</a>
		';
    }
?>

If you care to see the outcome here is my url: https://blog.trans-galactic.com

[Barand]

2 hours ago, OldGrim said:

as I don't knw how to code the result of your code

Like this...

$res = $connect->query("SELECT c.id
                             , c.category
                             , COUNT(p.id) as numposts
                        FROM categories c
                             LEFT JOIN posts p
                                  ON c.id = p.category_id
                                  AND active = 'Yes'
                        GROUP BY c.id
                        ORDER BY category
                        ");
foreach ($res as $row)  {
    $tc = ($row['numposts']==1) ? '' : 's';
    echo "a href='category.php?id={$row['id']}' class='list-group-item list-group-item-action'>
             <i class='fa fa-arrow-right'></i>&nbsp;{$row['category']} - {$row['numposts']} article$tc
         </a>";
}

 

[OldGrim]

Thanks Barand; I will keep your coding for future use you see, this is a blog script package that I purchased and I want to maintain the authors coding methods as I am entitled to updates. I appreciate the time you spent on this problem for me.

[gizmola]

On 6/7/2023 at 8:04 PM, OldGrim said:

Thanks Barand; I will keep your coding for future use you see, this is a blog script package that I purchased and I want to maintain the authors coding methods as I am entitled to updates. I appreciate the time you spent on this problem for me.

Unless the "style" of the original programmer is to be inefficient, there is simply no comparison.  The code you are using is extremely inefficient, as is most loops with an outer query that then drives an inner query.  

You are doing a query for every category.  That is slow and adds unnecessary strain on the database server.  Barand provided you the way an experienced developer would handle it:  one query using a join, so that you get one result set with all the data you need to display.

At the end of the day, it's your system, but you aren't maintaining anything for updates -- you've changed the original code and if they provide you an update, the change you just made will disappear, and you will need to figure out how to make it again, which is fairly standard with any customization that modifies the original code provided.