php – Query against two MySQL tables

Question:

There are two tables.

In one ad heading: board_cats with the structure: |cat_id|cat_title|...|

In the other, the ads themselves: board_items with the structure |item_id|item_title|...|item_cat_id|

choose a list of headings alphabetically

    $sql = "SELECT * FROM board_cats ORDER BY cat_title ASC";
    $result = $inDB->query($sql) ;
    if ($inDB->num_rows($result)){
        while($item = $inDB->fetch_assoc($result)){
           $massiv[] = $item;
        }
    }

everything works, it's simple.

Now you need to add a counter to each displayed heading, how many ads there are. And here, as I understand it, JOINs are needed, in which I have not yet boom-boom, although I tried to figure it out with examples.

That is, the logic is as follows – in board_items we look for ads with item_cat_id = cat_id , count them, and write the amount into an array. But how to do it?

Thanks in advance.

Answer:

Yes, that's right, for this you need a table join. Since not all categories can have ads, I recommend using left join instead of the usual join (you can read about the types of joins here and elsewhere ):

select bc.cat_title,
       count(bi.item_id)
from board_cats bc left join board_items bi
     on bc.cat_id = bi.item_cat_id
group by bc.cat_title

Here, in addition, there is a grouping by the bc.cat_title field, so that the count aggregate function can be used.

An example at sqlfiddle .