Combining queries...

[ShibSta]

I have the following PHP code:

$DB->this_query = "SELECT * FROM `categories` WHERE `cStatus` = '1' ORDER BY `cName` ASC";
if ( $DB->run_query() ) // $DB->run_query() return the number of rows.
{
foreach ( $DB->last_result as $row )
{
	$DB->this_query = "SELECT * FROM `articles` WHERE `aStatus` = 1 AND `category` = '" . $catID . "'";
	if ( $DB->run_query() )
	{
		// Add the category to the menu.
	}
}
}

 

As you can see, I have two tables: "categories" and "articles". I am trying to build a category menu but only wish to list a category if it has 1 or more articles in that category. I could alter the database to store the number of articles in each category but I'd prefer not altering the database structure. Is there any way I could combine the queries so that I am only running 1 query instead of 20?

 

Thanks,

- ShibSta

[kickstart]

Hi

 

This should do it:-

 

SELECT DISTINCT catID, etc 
FROM `categories` a 
JOIN `articles` b 
ON a.catID = b.category 
WHERE `cStatus` = '1' 
AND b.aStatus = 1 
ORDER BY `cName` ASC

 

Only select the fields from the category table that you want, not from the articles table.

 

All the best

 

Keith