[SOLVED] I can't get a foreach loop to work

[86Stang]

I have this code:

//init output var
$output = "";

//pull categories from database
$sql_cat = "SELECT parent_cat, COUNT(description) as classifieds FROM table GROUP BY parent_cat ORDER BY cat_id";
$cat_results = mysql_query($sql_cat) or die(mysql_error());
$cat_array = mysql_fetch_assoc($cat_results);

//loop through categories, and create links to sub cats
foreach($cat_array as $row)
{
//pull parent categories from db
$sql_sub = "SELECT DISTINCT category FROM table WHERE parent_cat = '" . $row['parent_cat'] . "'";
$parent_cats = mysql_query($sql_sub) or die(mysql_error());
$parent_array = mysql_fetch_assoc($parent_cats);

//write category link
$output .= $row['category'] . "(" . $row['classifieds'] . ")" . "\r\n";

	//write sub cat links
	foreach($parent_array as $sub)
	{
		//count classifieds
		$sql_classifieds = "SELECT COUNT(description) as classifieds FROM table WHERE parent_cat = '" . $row['parent_cat'] . "' AND category = '" . $sub['category'] . "'";
		$classifieds = mysql_query($sql_classifieds) or die(mysql_error());

		$output .= ('<div class="subcat"> . $sub['category'] . ') (' . $classifieds->row['classifieds'] . ')</div>' . "\r\n");
	}

	//close parent_cat div
	$output .= ('</div>' . "\r\n");	
}

echo $output;

 

and the only thing it outputs is:

0(0)
1(1)

 

I have a couple of hundred entries in the database so it should be returning WAY more lines as well as text instead of just numbers.  The database has the following columns:

 

parent_cat - the name of the parent category of the article.

category - the name of the category that the article belongs to.

cat_id  - the number assigned to the category that the article belongs to.

description - the description of the article.

 

Any help would be much appreciated.  I *HAVE* to get this running asap so if it means donating a few bucks somebody's way, so be it!

[Bauer418]

Foreaches aren't used to cycle through MySQL results.

 

$cat_array = mysql_fetch_assoc($cat_results);

//loop through categories, and create links to sub cats
foreach($cat_array as $row)

 

Should be:

while ($row = mysql_fetch_assoc($cat_results))

 

[tibberous]

Found it

 

mysql_fetch_assoc does return an array as rows, it returns the first row as an associate array.

 

Paypal all donations to Trent Tompkins at gmail

[86Stang]

I should've stated what I need to have happen.

 

I need this to list all parent categories along with all the sub categories underneath them along with the number of records per category.  I.E.:

Ford (13)
  Mustang (6)
  Taurus (3)
  F-150 (4)
Chevy (
  Tahoe (3)
  Impala (5)

 

When I try the WHILE statement, all I'm getting is:

 

(5)
()
()
()
(5)
()
()
()

[Bauer418]

This code is extremely inefficient and products a ton of unnecessary SQL load.  You may want to consider revising your double-nested SQL statements.

[86Stang]

I've already stated that I'm willing to pay for this to be solved by someone that has more skill then me, not to be given vague directions as answers.

[teng84]

you're using too much loop and query

give us your tables as well asthe fields

[86Stang]

It's all pulling from a single table.  I gave the fields in the original post but in case it helps, I've attached an export of the table.

 

[attachment deleted by admin]

[86Stang]

Anybody willing to tackle this or should I just post it in the freelance board?

[sasa]

try

<?php
$out = array();
mysql_connect('localhost','root');
mysql_select_db('test');
$sql = 'SELECT category, parent_cat FROM test';
$r = mysql_query($sql);
while($row = mysql_fetch_array($r)){
$c = $row['category'];
$p = $row['parent_cat'];
if (!isset($out[$p][$c])) $out[$p][$c] = 0;
$out[$p][$c]++;
}
foreach ($out as $p => $v){
$u = array_sum($v);
echo "$p ($u)<br />\n";
foreach ($v as $c => $x) echo "    $c ($x)<br />\n";
}
?>

[86Stang]

Worked like a champ, thanks!!  I'll PM you to see where I can send you some $$.