While loop calling function containing query

[h20boynz]

I've created a function getsub(). The idea is that this function contains a query that grabs records from the categories table, using the $row['cat_id'] that I pass to it from within an existing while ($row=mysql_fetch_assoc($res)) loop.

Heres the function:

function getsub($rowid,$catsort)
{
global $system, $LANGUAGES, $subres;
$subquery = "SELECT * FROM webid_categories
	  		WHERE parent_id = " . $rowid . "
	  		" . $catsort;
$subres = mysql_query($subquery);
$system->check_mysql($subres, $subquery, __LINE__, __FILE__);
return $subres;
}

And heres the code that calls this function and passes the $row['cat_id'] value to it:

// prepare categories list for templates/template
// Prepare categories sorting
if ($system->SETTINGS['catsorting'] == 'alpha')
{
$catsorting = ' ORDER BY cat_name ASC';
}
else
{
$catsorting = ' ORDER BY sub_counter DESC';
}

$query = "SELECT cat_id FROM " . $DBPrefix . "categories WHERE parent_id = -1";
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);

$query = "SELECT * FROM " . $DBPrefix . "categories
	  WHERE parent_id = " . mysql_result($res, 0) . "
	  " . $catsorting . "
	  LIMIT " . $system->SETTINGS['catstoshow'];
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);

while ($row = mysql_fetch_assoc($res))
{
$subcats = getsub($row['cat_id'],$catsorting);	
while($subrow = mysql_fetch_assoc($subcats){

$template->assign_block_vars('sublist', array(
			'SID' => $subrow['cat_id'],
			'SNAME' => $category_names[$getrow['cat_id']]
			));
}		

$template->assign_block_vars('cat_list', array(
		'CATAUCNUM' => ($row['sub_counter'] != 0) ? '(' . $row['sub_counter'] . ')' : '',
		'ID' => $row['cat_id'],
		'IMAGE' => (!empty($row['cat_image'])) ? '<img src="' . $row['cat_image'] . '" border=0>' : '',
		'COLOUR' => (empty($row['cat_colour'])) ? '#FFFFFF' : $row['cat_colour'],
		'NAME' => $category_names[$row['cat_id']]
		));		
}

then a .tpl file just references the {sublist.SID} and {sublist.SNAME} variables.

 

BUT....

 

It ain't working. My page just goes blank.

 

Any help would be massively appreciated.

[trq]

You need to turn error reporting on and make sure display_errors is also on. Your missing a brace on this line....

 

while($subrow = mysql_fetch_assoc($subcats){

 

You should also look into mysql JOINS (there is a tutorial on this site), your code is very un-efficient.

[h20boynz]

Thanks Thorpe

 

I found that tutorial and read it but can't figure out how to apply that to my example.

The table has a cat_id field which is unique and a parent_id field that relates a submenu item to its parent.

The first query gets the record with the parent_id of -1. Only one record has this and that is the category 'ALL', which has a cat_id of '1'.

Each first level category in the table then has a parent_id value of 1 and these records get queried in the second query.

Now I apply each result row to an array which builds my menu of 1st level categories.

Now I've got clever with my CSS (Not really) and generated flyout menus which I'd like to populate with the 2nd level categories. I figured I could do this within the loop, for each row of the 1st level category query.

 

I only went for the function because putting a second loop inside the existing while wasn't working for me either.

 

I'm certainly open to any other suggestions? ...

[h20boynz]

Solved! My big problem was in not understanding how a tpl file works.  Solved the problem as follows:

// Prepare categories sorting
if ($system->SETTINGS['catsorting'] == 'alpha')
{
   $catsorting = ' ORDER BY cat_name ASC';
}
else
{
   $catsorting = ' ORDER BY sub_counter DESC';
}

$query = "SELECT cat_id FROM " . $DBPrefix . "categories WHERE parent_id = -1";
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);

$query = "SELECT * FROM " . $DBPrefix . "categories
        WHERE parent_id = " . mysql_result($res, 0) . "
        " . $catsorting . "
        LIMIT " . $system->SETTINGS['catstoshow'];
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);

while ($row = mysql_fetch_assoc($res))
{
   $template->assign_block_vars('cat_list', array(
         'CATAUCNUM' => ($row['sub_counter'] != 0) ? '(' . $row['sub_counter'] . ')' : '',
         'ID' => $row['cat_id'],
         'IMAGE' => (!empty($row['cat_image'])) ? '<img src="' . $row['cat_image'] . '" border=0>' : '',
         'COLOUR' => (empty($row['cat_colour'])) ? '#FFFFFF' : $row['cat_colour'],
         'NAME' => $category_names[$row['cat_id']]
         ));

      $subcats = getsub($row['cat_id'],$catsorting);   
      while($subrow = mysql_fetch_assoc($subcats))
      {
      $template->assign_block_vars('cat_list.sub_list', array(
          'SNAME' => $subrow['cat_name'],
          'SID' => $subrow['cat_id']
          ));
      }         
}

This creates a nested block_vars, then I just needed to get my html right in the tpl file, as follows:

    <div id="menu"> 
        <ul>
         <li id="top"><a href="browse.php?id=0">{L_276}</a></li>
<!-- BEGIN cat_list -->
            <li>
                <a href="browse.php?id={cat_list.ID}">{cat_list.IMAGE}{cat_list.NAME}</a>
                <ul>
                 <!-- BEGIN sub_list -->
                 <li><a href="browse.php?id={cat_list.sub_list.SID}">{cat_list.sub_list.SNAME}</a></li>
                 <!-- END sub_list -->
                </ul>
            </li>
<!-- END cat_list -->
        <li id="bottom"><a href="{SITEURL}browse.php?id=0">{L_277}</a></li>
        </ul>
    </div>

Seems to be doing the job now...though as suggested above its probably not the most efficient method.

Any further advice would still be appreciated.