I am trying to make a page that displays articles in a database but it is to be organised in by category.
The categories are stored in a table categories with id and category as its fields/ The articles are stored in a table articles with its different fields for various information about the article.
The code I have:
<?php
$sql = "SELECT * FROM `categories`";
$query = mysql_query($sql) or die("Could not get CATEGORIES ".mysql_error());
while($category = mysql_fetch_array($query)){
$cat = $category['category'];
$sql = "SELECT * FROM `articles` WHERE `category` = '$cat'";
$query = mysql_query($sql) or die(mysql_error());
$articles = mysql_fetch_array($query);
$size = count($articles);
echo $size;
}
?>
Results that I expect are a list of each category, then underneath each category, the number of articles with that category.
Some help would be much appreciated.
Thanks :D
Try this one... may be this is what you are looking for. We can also find the same result using single mysql query.
$sql = "SELECT * FROM `categories`";
$query = mysql_query($sql) or die("Could not get CATEGORIES ".mysql_error());
$result = array();
while($category = mysql_fetch_array($query)){
$cat = $category['category'];
$sql = "SELECT count(*) as numberOfArticles FROM `articles` WHERE `category` = '$cat'";
$query2 = mysql_query($sql) or die(mysql_error());
$articles = mysql_fetch_array($query2);
$category['numberOfArticles'] = $articles['numberOfArticles'];
$result[] = $category;
}
print_r($result);
I think what you want to do can be done much easier and more performant:
<?php
$sql = "SELECT categories.name, COUNT(articles.id) AS cnt
FROM categories
LEFT JOIN articles ON articles.category=categories.category";
$query = mysql_query($sql);
while ($category = mysql_fetch_assoc($query)) {
echo $category['name'].' - '.$category['cnt'];
}
?>
(of course I don't know your table structure and had to guess the field names, but it should give you an idea of how it works). Regarding JOINs you should read the MYSQL docs.