[SOLVED] Simple PHP script, article script ???

[metvsmet]

Hi, i have one problem, this is my example:

DROP TABLE IF EXISTS category;
CREATE TABLE IF NOT EXISTS category (
  category_categoryid INT(11) NOT NULL auto_increment,
  category_title VARCHAR(30),
  PRIMARY KEY (category_categoryid)
);

INSERT INTO category (category_categoryid, category_title) VALUES
(1, 'Example Category 1'),
(2, 'Example Category 2'),
(3, 'Example Category 3');

DROP TABLE IF EXISTS subcategory;
CREATE TABLE IF NOT EXISTS subcategory (
  subcategory_subcategoryid INT(11) NOT NULL auto_increment,
  subcategory_title VARCHAR(30),
  subcategory_categoryid INT NOT NULL,
  PRIMARY KEY  (subcategory_subcategoryid),
  FOREIGN KEY(subcategory_categoryid) REFERENCES category (category_categoryid)
);

INSERT INTO subcategory (subcategory_subcategoryid, subcategory_title, subcategory_categoryid) VALUES
(1, 'Example subcategory 1', 1),
(2, 'Example subcategory 2', 1),
(3, 'Example subcategory 3', 2),
(4, 'Example subcategory 4', 2);

DROP TABLE IF EXISTS articles;
CREATE TABLE IF NOT EXISTS articles (
  articles_postid INT(11) NOT NULL auto_increment,
  articles_title VARCHAR(50),
  articles_body TEXT,
  articles_subcategoryid INT,
  PRIMARY KEY  (articles_postid),
  FOREIGN KEY(articles_subcategoryid) REFERENCES subcategory (subcategory_subcategoryid)
);

INSERT INTO articles (articles_postid, articles_title, articles_body, articles_subcategoryid) VALUES
(1, 'This is my first article', 'Small description', 1),
(2, 'This is my second article', 'Small description', 1),
(3, 'This is my 3th article', 'Small description', 2),
(4, 'This is my 4th article', 'Small description', 3);

 

and now ... i want to display results from mysql database just like this:

Example Category 1 (3)

Example subcategory 1 (2)

Example subcategory 2 (1)

Example Category 2 (1)

Example subcategory 3 (1)

Example subcategory 4 (0)

Example Category 3 (0)

 

... category1 (count all articles in subcategories) ...

... subcategory1 of category1 (and count all articles in this category) ...

 

HELP ???

 

... and sorry for my bad english ...

 

...thanks ...

[ohdang888]

whats the problem?

[alco19357]

<?php # alco19357
mysql_connect(HOST, USER, PASS);
mysql_select_db(DB);

$query_cats = mysql_query("select * from category");

$cats = array();

while($cat = mysql_fetch_array($query_cats)){
$query_subcats = mysql_query("select * from subcategory where subcategory_categoryid='".$cat["category_categoryid"]."'");
$subcats = array();
$articles = 0;
while($subcat = mysql_fetch_array($query_subcats)){
	$query_articles = mysql_query("select * from articles where articles_subcategoryid='".$subcat["subcategory_subcategoryid"]."'");
	$subcats[$subcat["subcategory_subcategoryid"]] = array("id" => $subcat["subcategory_subcategoryid"], "title" => $subcat["subcategory_title"], "articles" => mysql_num_rows($query_articles));
	$articles = mysql_num_rows($query_articles)+$articles;
}
$cats[$cat["category_categoryid"]] = array("id" => $cat["category_categoryid"], "title" => $cat["category_title"], "subcats" => $subcats, "articles" => $articles);	
}
$echo = '';
$row = 0;
foreach($cats as $_cat){
$row++;
if($row > 1){ $echo .= '<br>'; }
$echo .= $_cat["title"].' ('.$_cat["articles"].')';
foreach($_cat["subcats"] as $_sub){
	$echo .= '<br>  '.$_sub["title"].' ('.$_sub["articles"].')';
}
}
?>

[metvsmet]

<?php # alco19357
mysql_connect(HOST, USER, PASS);
mysql_select_db(DB);

$query_cats = mysql_query("select * from category");

$cats = array();

while($cat = mysql_fetch_array($query_cats)){
$query_subcats = mysql_query("select * from subcategory where subcategory_categoryid='".$cat["category_categoryid"]."'");
$subcats = array();
$articles = 0;
while($subcat = mysql_fetch_array($query_subcats)){
	$query_articles = mysql_query("select * from articles where articles_subcategoryid='".$subcat["subcategory_subcategoryid"]."'");
	$subcats[$subcat["subcategory_subcategoryid"]] = array("id" => $subcat["subcategory_subcategoryid"], "title" => $subcat["subcategory_title"], "articles" => mysql_num_rows($query_articles));
	$articles = mysql_num_rows($query_articles)+$articles;
}
$cats[$cat["category_categoryid"]] = array("id" => $cat["category_categoryid"], "title" => $cat["category_title"], "subcats" => $subcats, "articles" => $articles);	
}
$echo = '';
$row = 0;
foreach($cats as $_cat){
$row++;
if($row > 1){ $echo .= '<br>'; }
$echo .= $_cat["title"].' ('.$_cat["articles"].')';
foreach($_cat["subcats"] as $_sub){
	$echo .= '<br>  '.$_sub["title"].' ('.$_sub["articles"].')';
}
}
?>

 

thank you )))))))))))

[alco19357]

thank you )))))))))))

 

no problem