There are two tables.

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

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

I choose the list of rubrics 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; } }

it works, it's simple.

Now it is necessary to add a counter to each derived category, how many ads there are. And here, as I understand it, we need JOINs, in which I haven’t been booming, although I’ve tried to figure it out using examples.

That is, the logic is as follows - in board_items we are looking for announcements that have item_cat_id = cat_id , count them, and write the amount into an array. But how to do it?

Thank you in advance.

1 answer 1

Yes, that's right, for this you need to join the tables. Since not all categories can have declarations, I recommend using left join instead of normal join (you can read about connection types here and here ):

 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 bc.cat_title that you can use the aggregate function count .

An example on sqlfiddle .

  • Thank you so much, after count (bi.item_id) I added as items_count , and that’s what I need! - Alexander Vladimirovich
  • @ Alexander Vladimirovich is always welcome. - Denis
  • Goodnight. I have a little more complicated task on this issue, and again I will ask you for help)) I will try to explain now. - Alexander Vladimirovich
  • In general, count (bi.item_id) now counts the number of records in each separate category. As it turned out, I will also have nested headings, and now I need to make sure that the count at the parent heading counts the total number of records in it, and in all the nested headings. An additional column, parent_id, appears in the board_cats table, indicating the id of the parent heading. - Alexander Vladimirovich