[SOLVED] logic question?

[ballhogjoni]

I have x categories and I have x articles in those categories. The logic I am trying to come up with is that I want to display the categories and the articles under the articles' respective category.

 

The articles and categories are stored in the database. The trouble I am having is coming up with a sql query to put into a while loop to print the categories and the articles.

 

example of what I want:

category 1

-article 1 (under category 1)

-article 2 (under category 1)

 

category 2

-article 1 (under category 2)

-article 2 (under category 2)

 

category 3

-article 1 (under category 3)

-article 2 (under category 3)

 

etc...

[cooldude832]

this is a chopped down version of the left navi in my siggy which is sorta what you want 

 

The growth portion of it is the active_lib/active_lang/active_func portion of the query

<?php
		$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
		$q = "Select 
				".LANG_TABLE.".Name as Lang,
				GROUP_CONCAT(DISTINCT(".LIB_TABLE.".Name) ORDER BY ".LIB_TABLE.".Name) as Libs,
				GROUP_CONCAT(DISTINCT(".FUNC_TABLE.".Name) ORDER BY ".FUNC_TABLE.".Name) as Funcs,
				ALib.LibraryID as Active_Lib,
				ALang.Name as Active_Lang,
				AFunc.FunctionID as Active_Func
			FROM `".LANG_TABLE."`
				LEFT JOIN ".LIB_TABLE." ON (".LIB_TABLE.".LanguageID = ".LANG_TABLE.".LanguageID)
				LEFT JOIN ".FUNC_TABLE." ON (".FUNC_TABLE.".LibraryID = '".$active_lib."' AND ".FUNC_TABLE.".LanguageID = ".LANG_TABLE.".LanguageID)
				LEFT JOIN `".LANG_TABLE."` as ALang on (ALang.LanguageID = '".$active_lang."')
				LEFT JOIN `".LIB_TABLE."` as ALib ON(ALib.LibraryID = '".$active_lib."')
				LEFT JOIN `".FUNC_TABLE."` as AFunc ON(AFunc.FunctionID = '".$active_func."')
			GROUP BY ".LANG_TABLE.".LanguageID
			ORDER BY ".LANG_TABLE.".Sort";
		#echo "<br /><br />".$q."<br /><br />";
		$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
		$base = "http://www.scriptbetter.net/";
		while($row = mysql_fetch_assoc($r)){
			#print_r($row);
			if(($row['Active_Lang']) == $row['Lang']){
				echo "<li style='padding-left: 6px;' class='lang_navi'><a href='".$base.$row['Lang']."/' style='color: #cc0000; font-weight: bold; font-size:26px'>".$row['Lang']."</a>\\n";
			}
			else{
				echo "<li style='padding-left: 6px;' class='lib_navi'><a href='".$base.$row['Lang']."/' style='font-size:26px'>".$row['Lang']."</a>\\n";
			}
			if(($row['Active_Lang']) == $row['Lang']){
				echo "<ul>\\n";
				foreach(explode(",",$row['Libs']) as $value){
					if($value == $active_lib_name){
						echo "<li  class='func_navi'><a href='".$base.$row['Lang']."/".$value."/' style='color: #cc0000; font-weight: bold; font-size:22px'>".$value."</a>\\n";
					}
					else{
						echo "<li  class='func_navi'><a href='".$base.$row['Lang']."/".$value."/' style='font-size:22px'>".$value."</a>\\n";
					}
					if($value == $active_lib_name){
						echo "<ul>\\n";
							foreach(explode(",",$row['Funcs']) as $v){
								if($v == $active_func_name){
									echo "<li><a href='".$base.$row['Lang']."/".$value."/".$v."/'  style='color: #cc0000; font-weight: bold; font-size:12px;'>".$v."</a></li>\\n";
								}
								else{
									echo "<li><a href='".$base.$row['Lang']."/".$value."/".$v."/' style='font-size:12px'>".$v."</a></li>\\n";
								}
							}
						echo "</ul>\\n";
						echo "<br />";
					}

					echo "</li>\\n";
				}
				echo "</ul>\\n";
				echo "<br />";
			}
			echo "</li>\\n";
		}
		echo "</ul>\\n";
?>

[unrelenting]

I have x categories and I have x articles in those categories. The logic I am trying to come up with is that I want to display the categories and the articles under the articles' respective category.

 

The articles and categories are stored in the database. The trouble I am having is coming up with a sql query to put into a while loop to print the categories and the articles.

 

example of what I want:

category 1

-article 1 (under category 1)

-article 2 (under category 1)

 

category 2

-article 1 (under category 2)

-article 2 (under category 2)

 

category 3

-article 1 (under category 3)

-article 2 (under category 3)

 

etc...

 

Do a query and while loop for the category then inside of the while loop do another query and loop for the articles.

[ballhogjoni]

Do a query and while loop for the category then inside of the while loop do another query and loop for the articles.

 

Isn't it best practice to not have queries in loops? This could be very ineffiecient if you are pulling a lot of info.

[sasa]

can you post structure of your db table(s)

[ballhogjoni]

Sasa:

 

TABLE `Articles` (

`article_id` int(11) NOT NULL auto_increment,

`title` varchar(255) NOT NULL default '',

`tinymcedata` text NOT NULL,

`author_id` int(11) NOT NULL default '0',

`topic_id` int(11) NOT NULL default '0',

`display_on_home_page` int(1) NOT NULL default '0',

`publish_date` int(20) NOT NULL default '0',

PRIMARY KEY (`article_id`)

)

 

TABLE `Topics` (

`topic_id` int(11) NOT NULL auto_increment,

`topic` varchar(255) NOT NULL default '',

PRIMARY KEY (`topic_id`)

)

[sasa]

use guery

SELECT `Topics`.topic, `Articles`.title, Topics.topic_id FROM `Topics` LEFT JOIN `ARTICLES` ON Articles.topic_id=Topics.topic_id ORDER BY Topics.topic_id

and

<?php
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$id = $row['topic_id'];
echo $rov['topic']. "<br />\n";
do {
if ($id != $row['topic_id']) {
	$id = $row['topic_id'];
	echo $rov['topic']. "<br />\n";
}
echo ' - '.$row['title']."<br />\n";
}while ($row = mysql_fetch_assoc($result));
echo "</table>\n";
?>

[jordanwb]

What I like to do is get all the categories and store them in an array. Then I get all the articles then out them in a 2d array:

 

while ($article = mysql_fetch_array ($result))
{
$category = (int)$article['topic_id'];
$articles[$category][] = $article;
}

 

Then iterate through the categories using a foreach loop, then iterate through the $articles array with a foreach loop like so:

 

foreach ($categories as $category_index => $category)
{
print "[Category]";

foreach ($articles[$category_index] as $article_index => $article)
{
print "[Article]";
}
}

[ballhogjoni]

sasa that was awesome. thanks,