Need help querying specific categories for a new infobox...

[brandoneilers]

Hi,

 

I want to create a couple more category type boxes that list specific categories. I know how to create a new infobox, but I am running into trouble when I am trying to specify certain categories displayed in that box.

 

Here is an excerpt of what I came up with from a copy of categories.php

 

$categories2_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '0' and c.categories_id = cd.categories_id and (cd.categories_name = 'Cook Books & More' or cd.categories_name = 'Kitchen & Houseware Products') and cd.language_id='" . $languages_id ."' order by sort_order, cd.categories_name");
 while ($categories = tep_db_fetch_array($categories2_query))  {
$foo[$categories['categories_id']] = array(
									'name' => $categories['categories_name'],
									'parent' => $categories['parent_id'],
									'level' => 0,
									'path' => $categories['categories_id'],
									'next_id' => false
								   );

Notice that I am telling it to only pull up categories with a certain name. This seems like it would work, but I am getting other categories that occur before these two alphabetically. I was hoping someone could tell me why it is pulling the others into the array based on my query. I'm just a little confused.

[Guest]

I don't see anything wrong with it. Your problem is likely somewhere else.

[brandoneilers]

I don't see anything wrong with it. Your problem is likely somewhere else.

Do you know of anywhere else in the default boxes/categories.php file that might need to be setup this way. There is another categories query in that file, but when I included the above argument there it didn't seem to do anything.

 

The query is somehow pulling up categories that occur before it alphabetically, but then when it gets to the 'Cook Books & More' category, it sees that I only want that and 'Kitchen & Houseware Products'. Not sure why it would be doing that.

[Guest]

can you post the entire categories.php file you have? Including the changes you did to display those specific categories.

[brandoneilers]

Here it is, just a modified copy of my boxes/categories.php file....

 

<?php
/*
 $Id: categories.php,v 1.23 2002/11/12 14:09:30 dgw_ Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License
*/

 function tep_show_category_products($counter) {
global $foo, $categories_string, $id, $aa;

for ($a=0; $a<$foo[$counter]['level']; $a++) {
  if ($a == $foo[$counter]['level']-1)
	  {
	$categories_string .= "<font color='#0000CC'>???</font>";
 	} else
		  {
		  $categories_string .= "<font color='#0000CC'>???</font>";
 		}

	}
if ($foo[$counter]['level'] == 0)
{
	if ($aa == 1)
	{
	$categories_string .= tep_draw_separator('pixel_brown_trans.gif', '100%', '5');
	}
	else
	{$aa=1;}

}

if ($foo[$counter]['level'] == 0)
{
	$categories_string .= '<nobr><a nowrap href="';
} else {
	$categories_string .= '<nobr>' . tep_image(DIR_WS_IMAGES . 'arrow_bullet.gif', '') . '?' . '<a nowrap href="';
}

if ($foo[$counter]['parent'] == 0) {
  $cPath_new = 'cPath=' . $counter;
} else {
  $cPath_new = 'cPath=' . $foo[$counter]['path'];
}

$categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new);
$categories_string .= '">';

if ( ($id) && (in_array($counter, $id)) ) {
  $categories_string .= "<b><font color='#0000CC'>";
}

// display category name
$categories_string .= $foo[$counter]['name'];

if ( ($id) && (in_array($counter, $id)) ) {
  $categories_string .= '</font></b>';
}

  // if (tep_has_category_subcategories($counter)) {
  //   $categories_string .= '->';
  // }

$categories_string .= '</nobr></a>';

if (SHOW_COUNTS == 'true') {
  if ($foo[$counter]['parent'] == 0) {
	$products_in_category = tep_count_products_in_category($counter);
	if ($products_in_category > 0) {
	  $categories_string .= '?(' . $products_in_category . ')';
	}	
  }
}

$categories_string .= '<br>';

if ($foo[$counter]['next_id']) {
  tep_show_category_products($foo[$counter]['next_id']);
}
 }
?>
<!-- categories_products //-->
	  <tr>
		<td>
<?php
 $aa = 0;
 $info_box_contents = array();
 $info_box_contents[] = array('align' => 'left',
						   'text'  => BOX_HEADING_CATEGORIES
						  );
 new InfoBoxHeading($info_box_contents, false, false);

 $categories_string = '';

// add links to products with no category
 $product_query = tep_db_query("select p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = 0 and p.products_id = pd.products_id and pd.language_id ='"  . (int)$languages_id . "' order by pd.products_name " );
 while ($no_category = tep_db_fetch_array($product_query))  {
	$no_cat_product_id = $no_category['products_id'];
	$no_cat_products_name = $no_category['products_name'];
$myref = "<a href=" . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $no_cat_product_id) . '>' . $no_cat_products_name . '</a><br><br>';
$categories_string .= $myref;
}
 // end links to products with no category

 $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '0' and c.categories_id = cd.categories_id and (cd.categories_name = 'Cook Books & More' or cd.categories_name = 'Kitchen & Houseware Products') and cd.language_id='" . $languages_id ."' order by sort_order, cd.categories_name");
 while ($categories = tep_db_fetch_array($categories_query))  {
$foo[$categories['categories_id']] = array(
									'name' => $categories['categories_name'],
									'parent' => $categories['parent_id'],
									'level' => 0,
									'path' => $categories['categories_id'],
									'next_id' => false
								   );

if (isset($prev_id)) {
  $foo[$prev_id]['next_id'] = $categories['categories_id'];
}

$prev_id = $categories['categories_id'];

if (!isset($first_element)) {
  $first_element = $categories['categories_id'];
}
 }

 //------------------------
 if ($cPath) {
$new_path = '';
$id = split('_', $cPath);
reset($id);
while (list($key, $value) = each($id)) {
  unset($prev_id);
  unset($first_id);
  $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . $value . "' and c.categories_id = cd.categories_id and cd.language_id='" . $languages_id ."' order by sort_order, cd.categories_name");
  $category_check = tep_db_num_rows($categories_query);
  if ($category_check > 0) {
	$new_path .= $value;
	while ($row = tep_db_fetch_array($categories_query)) {
	  $foo[$row['categories_id']] = array(
										  'name' => $row['categories_name'],
										  'parent' => $row['parent_id'],
										  'level' => $key+1,
										  'path' => $new_path . '_' . $row['categories_id'],
										  'next_id' => false
										 );

	  if (isset($prev_id)) {
		$foo[$prev_id]['next_id'] = $row['categories_id'];
	  }

	  $prev_id = $row['categories_id'];

	  if (!isset($first_id)) {
		$first_id = $row['categories_id'];
	  }

	  $last_id = $row['categories_id'];
	}
	$foo[$last_id]['next_id'] = $foo[$value]['next_id'];
	$foo[$value]['next_id'] = $first_id;
	$new_path .= '_';
  } else {
	break;
  }
}
 }
 tep_show_category_products($first_element);

 $info_box_contents = array();
 $info_box_contents[] = array('align' => 'left',
						   'text'  => $categories_string
						  );

// WebMakers.com Added: All products or All Products Multiple Purchase
//  new infoBox($info_box_contents);

// WebMakers.com Added: Show All Products Listing
if (SHOW_ALL_PRODUCTS_BOX=='1') {
 $info_box_contents[] = array('align' => 'center" valign="top',
						   'text'  => '<hr><a href="' . tep_href_link(FILENAME_ALLPRODS, '', 'NONSSL') . '">' . BOX_INFORMATION_ALLPRODS . '</a>');
}

// WebMakers.com Added: Show multiple product listing add for all products
if (SHOW_PRODUCT_LISTINGS_MULTI_ADD_BOX=='1') {
// All Products Multiple Purchase

 $info_box_contents[] = array('align' => 'center" valign="top',
						   'text'  => '<hr><a href="' . tep_href_link(FILENAME_PRODUCTS_ALL, '', 'NONSSL') . '">' . BOX_INFORMATION_PRODUCT_ALL . '</a>');
}

 new InfoBox($info_box_contents);

?>
</td>
</tr>
<!-- categories_products_eof //-->

[brandoneilers]

I am also including an image of the problem. It should only be displaying 'Cook Books & More' and 'Kitchen & Houseware Products'.

 

 

Thanks!

[Guest]

I tried this code right on a plain osc, changed the categories names to hardware software and works fine. So it must be something else maybe comes from the subcategories? Comment out this code and try it:

 

  //------------------------
 if ($cPath) {
$new_path = '';
$id = split('_', $cPath);
reset($id);
while (list($key, $value) = each($id)) {
  unset($prev_id);
  unset($first_id);
  $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . $value . "' and c.categories_id = cd.categories_id and cd.language_id='" . $languages_id ."' order by sort_order, cd.categories_name");
  $category_check = tep_db_num_rows($categories_query);
  if ($category_check > 0) {
	$new_path .= $value;
	while ($row = tep_db_fetch_array($categories_query)) {
	  $foo[$row['categories_id']] = array(
										  'name' => $row['categories_name'],
										  'parent' => $row['parent_id'],
										  'level' => $key+1,
										  'path' => $new_path . '_' . $row['categories_id'],
										  'next_id' => false
										 );

	  if (isset($prev_id)) {
		$foo[$prev_id]['next_id'] = $row['categories_id'];
	  }

	  $prev_id = $row['categories_id'];

	  if (!isset($first_id)) {
		$first_id = $row['categories_id'];
	  }

	  $last_id = $row['categories_id'];
	}
	$foo[$last_id]['next_id'] = $foo[$value]['next_id'];
	$foo[$value]['next_id'] = $first_id;
	$new_path .= '_';
  } else {
	break;
  }
}
 }

[brandoneilers]

Mark,

 

I am still getting the same problem when I commented out the code you told me to. I am using this infobox along with the default categories box. Do you think there may be some kind of conflict? I did change the name of the function in the code, but that was it.

[Guest]

yea probably that's the reason. You have some global variables that need re-initializing. Like this

$foo

variable. So just before the function definition do a

 

$foo = '';

to clear it. Maybe there other variables, but try that first.

[brandoneilers]

I tried this to no avail. I think we are headed in the right direction though. When I set $foo to '', it makes everything dissapear from the output leaving only the count of the first category. I am just confused about the way this category file works. Any other ideas?

[Guest]

ok so try changing this

 

function tep_show_category_products($counter) {

global $foo, $categories_string, $id, $aa;

 

to

	$foo=$categories_string=$id=$aa='';

 function tep_show_category_products($counter) {
global $foo, $categories_string, $id, $aa;