MySQL SELECT WHERE id IN（）获取num_rows

Question

I use this code to show category from database

$select_newscats = $mysqli->query("SELECT * FROM news_cats order by ord_show asc");

while ($rows_newscats = $select_newscats->fetch_array(MYSQL_ASSOC)){

$id_newscats          = $rows_newscats ['id'];
$title_newscats       = $rows_newscats ['title'];
$ord_show_newscats    = $rows_newscats ['ord_show'];
$icon_newscats        = $rows_newscats ['icon'];
$kind_newscats        = $rows_newscats ['kind'];
$description_newscats = $rows_newscats ['description'];

//here is my data

}

i have in news table row for categoies it's name is cats and i insert data inside it like that 1,5,6,8

and i use this code to count news inside each cat

$select_newsnum = $mysqli->query("SELECT id FROM news where $id_newscats  IN (cats)");
$rows_newsnum   = $select_newsnum->fetch_array(MYSQL_ASSOC);
$num_newsnum    = $select_newsnum->num_rows;

it gets only first value for example if i have this values 1,5,6,8 it gets only 1 the first value

Answer 1

I would do the counting in a single sql call, not with php logic and separate sql calls. For this I would join the 2 tables using left join and use a join condition instead of an in clause:

SELECT nc.id, nc.title, nc.ord_show, nc.icon, nc.king, nc.description, count(n.id) as newsnum
FROM news_cats nc
LEFT JOIN news n ON nc.id=n.cats
GROUP BY nc.id, nc.title, nc.ord_show, nc.icon, nc.king, nc.description
ORDER BY nc.ord_show asc

Obviously, make sure that the join condition is the right one. When you loop through the resultset, the number of news per category will be in the newsnum field.