get category list problem

[jiji]

Hi everyone i got a problem with my code see i got a directory of links and is into categories my problem is it is only showing the categories wich have articles and the others not lets say there is a category with 0 articles instead of showing it saying 0 articles it doesnt here is the code plz if someone may help will really apreciate it!

thanks in advance

function cat_list() {
 global $query_ext;
 if (SHOW_TOTAL_ARTICLES == 1) {
 $count_ext = ", COUNT(*) AS total_per_cat";
 }
 // get list of categories
 $sql = "
 SELECT * $count_ext 
 FROM pqdb_categories, pqdb_articles
 WHERE category_id > 0 
 AND category_id = article_cat
 $query_ext
 GROUP BY category_id
 ORDER BY category_name ASC";
 $result = mysql_query($sql);
 // are there any rows?
 if (mysql_num_rows($result) < 1) {
 
 $categories = "Oops! Something went wrong"; 
   
 } else {
 // loop through categories and make links
 while ($row = mysql_fetch_array($result)) { 
 
  // format category names (get rid of "Information ...")
  $take_out = array("& Information", "Information on", "Information");
  $category_desc = str_replace($take_out, "", $row['category_desc']);
 
  if (SHOW_TOTAL_ARTICLES == 1) {
  $totals = "(" . $row['total_per_cat'] . " Articles)";
  }
 
  $categories .= "<li><a href=\"" . make_url('showarticles', $row['category_id'], $row['category_name']) . "\">$category_desc</a>$totals</li>";
 
 }
 }
 return $categories;
}

[gcarn]

Its probably something to do with your selection from sql

I would approach the query like this:

SELECT * FROM pqdb_categories as c LEFT JOIN pqdb_articles as a
ON a.article_cat=c.category_id
....
rest of query here.

The Key being the LEFT JOIN, as apposed to a normal JOIN ... this will allow you to also select the categories that have no articles.

[jiji]

thanks gcarn for ur prompt answer i tried what u said but it didnt appear the articles so i put them the code as followed

function cat_list() {

global $query_ext;

if (SHOW_TOTAL_ARTICLES == 1) {
$count_ext = ", COUNT(*) AS total_per_cat";
}

// get list of categories
$sql = "
SELECT * $count_ext FROM pqdb_categories as c LEFT JOIN pqdb_articles as a
ON a.article_cat=c.category_id
$query_ext
GROUP BY category_id
ORDER BY category_name ASC";

with the count_ext variable now it shows the categories but the ones that have no articles instead of putting 0 articles beside it says 1 article and when u click it shows none .