How to count duplicated values in a table and display it?

[imbruceter]

I'm really sorry for the poor explanation, I am absolutely not familiar with PHP.
I would like to get data from my phpmyadmins 2 table, so I can display how many posts have been submitted with the same category type.  One is called "posts" and the other one is called "categories".  I have attached a picture to see the how it looks like. What I want is to make the category tables "id" equal to the posts tables "catagory_id", and then display how many posts have bben uploaded of each category.

Here is how far I went with the coding, which is nowhere close to my expectations:

<div class="stats__div">
    <p class="stats__text">Categories</p>
    <p>
        <?php
            $all_categories_query = "SELECT * FROM categories ORDER BY title ASC";
            $all_categories = mysqli_query($connection, $all_categories_query);
            while ($category = mysqli_fetch_assoc($all_categories)) : ?>
                <p><?= $category['title'] ?> <?= $category['id'] ?></p>
            <?php endwhile ?>
            <?php
            $all_posts_query = "SELECT * FROM posts GROUP BY category_id";
            $all_posts = mysqli_query($connection, $all_posts_query);
            while ($posts = mysqli_fetch_assoc($all_posts)) : ?>
                <p><?= $posts['category_id'] ?></p>
            <?php endwhile ?>
        </p>
    </p>
</div>

I know, the coding is at the edge of being horrible, I am really sorry for that. I have tried my best.
Does anyone know how to do that? I have seen that the right solution is using "array_count_values", but all the examples I have seen were lists.

Any kind of help is highly appreciated!

[Barand]

  It would look something like this

SELECT c.title
     , COUNT(p.category_id) as total
FROM category c
     LEFT JOIN 
     post p ON c.id = p.category_id
GROUP BY c.id

 

[imbruceter]

12 minutes ago, Barand said:

  It would look something like this

SELECT c.title
     , COUNT(p.category_id) as total
FROM category c
     LEFT JOIN 
     post p ON c.id = p.category_id
GROUP BY c.id

 

I am thankful for your answer!

I am sorry for the noob question but can you tell me how should I display the result?
I have tried extending your suggestion with these lines:

$allselect_query = mysqli_query($connection, $allselect);
$allselect_assoc = mysqli_fetch_assoc($allselect_query);
$num_rows_allselect = $allselect_assoc['total'];

At least this is how I displayed every data from a row but that's not the case for now.
I am truly confused, this is so complicated.

[Barand]

try

$result = $connection->query("SELECT c.title
                                 , COUNT(p.category_id) as total
                            FROM category c
                                 LEFT JOIN 
                                 post p ON c.id = p.category_id
                            GROUP BY c.id
                            ");
foreach ($result as $row) {
    echo "{$row['title']} : {$row['total']}<br>";
}

 

[imbruceter]

43 minutes ago, Barand said:

try

$result = $connection->query("SELECT c.title
                                 , COUNT(p.category_id) as total
                            FROM category c
                                 LEFT JOIN 
                                 post p ON c.id = p.category_id
                            GROUP BY c.id
                            ");
foreach ($result as $row) {
    echo "{$row['title']} : {$row['total']}<br>";
}

 

This is how it looks like now:

$res = $connection->query("SELECT c.title, COUNT(p.category_id) as total FROM category c LEFT JOIN post p ON c.id = p.category_id GROUP BY c.id");
if (is_array($res)) {
    foreach ($res as $row) { ?>
        <p><?= "{$row['title']} : {$row['total']}<br>"; ?></p>
        // <?php echo $row['title']; //just for testing
    }
}

I had to add an if statement because without having it, it gives this warning:

Quote

Warning: Invalid argument supplied for foreach()

The issue is, that nothing on the website gets displayed about this code. No error but not even the paragraph can be seen in the console relating to this code snippet (it should be listed under "Categories" on the page).
I'm so lost:D

Edited July 31, 2022 by imbruceter
forgot to attach the image

[Barand]

I use the standard convention of naming tables in the singular (category, post etc). You don't.

Put this code immediately before you create your connect...

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

and make sure your error reporting is on.

[imbruceter]

19 minutes ago, Barand said:

I use the standard convention of naming tables in the singular (category, post etc). You don't.

Put this code immediately before you create your connect...

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

and make sure your error reporting is on.

I have this already at the top of my code file but there is still no error message. i have put this right above this code snippet so you can see that I am not flashing:D
I am starting to humiliate myself but I have really no idea what is going on...

[Barand]

5 minutes ago, imbruceter said:

Put this code immediately before you create your connect...

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$connection = mysqli_connect('localhost', '****', '****', '****');



$result = $connection->query("SELECT c.title
                                 , COUNT(p.category_id) as total
                            FROM categories c
                                 LEFT JOIN 
                                 posts p ON c.id = p.category_id
                            GROUP BY c.id
                            ");
foreach ($result as $row) {
    echo "{$row['title']} : {$row['title']}<br>";
}

Have you got error display on? If not, check your error log.

[imbruceter]

9 minutes ago, Barand said:

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$connection = mysqli_connect('localhost', '****', '****', '****');



$result = $connection->query("SELECT c.title
                                 , COUNT(p.category_id) as total
                            FROM categories c
                                 LEFT JOIN 
                                 posts p ON c.id = p.category_id
                            GROUP BY c.id
                            ");
foreach ($result as $row) {
    echo "{$row['title']} : {$row['title']}<br>";
}

Have you got error display on? If not, check your error log.

Yes, I have checked the error and warning log a couple of times, nothing in there. 

The foreach loop never gets executed since the <p> tag doesn't show up (the tags you see on the image are different ones).

Edited July 31, 2022 by imbruceter

[Barand]

$res is NOT an array, it is a result object. Therefore your loop never executes.

Don't post pictures of code, post code.

[imbruceter]

1 minute ago, Barand said:

$res is NOT an array, it is a result object. Therefore your loop never executes.

Don't post pictures of code, post code.

God! You did it! Sorry for being this terribly dumb, I am very far from web development (came from game development).
Yes, I haven't recognised that I didn't delete the if statement. It works now like a charm!

Thank you a lot for your time and your help!

[mac_gyver]

since it is likely to come up in your following threads, just because you can use a foreach() loop on something doesn't mean that it is an array. the  mysqli query call returns a mysqli result object (on success), in $res. this is not the actual data. a mysqli result object is Traversable, meaning it can be iterated over using a foreach() loop.