It would seem necessary to realize a simple thing - to display on the page a list of categories, next to each category the number of articles related to it. Here is my attempt, but in vain (this is a function that lists all categories):

//Показать все категории function categories() { $db = new SQLite3(DB_NAME); $results = $db->query("SELECT DISTINCT category FROM articles"); $articles = $db->querySingle("SELECT COUNT(*) as count FROM articles WHERE category LIKE '%$category%'"); while ($row = $results->fetchArray()) { echo 'Категория: <a href="?category=' . $row['category'] . '">' . $row['category'] . '</a> Статей: (' . $articles . ')'; } $db->close(); }

Please tell me where my error is and how to fix it.

  • var_dump($articles); to the studio - Manitikyl 7:55 pm
  • int (7) like this)) - Linne
  • I understood what the joint was, schA - Manitikyl
  • And in one request, using GROUP BY not destiny? - ArchDemon

2 answers 2

Something like that:

 //Показать все категории function categories() { $db = new SQLite3(DB_NAME); $results = $db->query("SELECT DISTINCT category FROM articles"); while ($row = $results->fetchArray()) { $articles = $db->querySingle("SELECT COUNT(*) as count FROM articles WHERE category LIKE '%".$row['category']."%'"); echo 'Категория: <a href="?category=' . $row['category'] . '">' . $row['category'] . '</a> Статей: (' . $articles . ')'; } $db->close(); }

    PHP: displaying the number of entries in each category

    Save on calls to the database, a better option:

     //Показать все категории function categories() { $db = new SQLite3(DB_NAME); // Один вызов со всем что нужно $results = $db->query("SELECT DISTINCT category, COUNT(category) as count FROM articles Group by category"); while ($row = $results->fetchArray()) { echo 'Категория: <a href="?category=' . $row['category'] . '">' . $row['category'] . '</a> Статей: (' . $row['count'] . ')'; } $db->close(); }

    http://sqlfiddle.com/#!9/c14b9a/2