Categories Crash if too many records

[Manta2000]

Help Please? :blink:

 

I have run a full gambit trying to find the cause of this and hopefully someone has dealt with this before and knows a solution..

 

We run a VERY large database of products (movie posters), and created our OSCommerce 2.2 RC2 catalog to display the movie titles by category. However, after loading in the full database of products I discovered to anomalies ...

 

1) I can no longer view the categories section in OSCommerce 2.2 RC2, the page just hang after the header displays. If I watch closely it looks like it refreshes after a period of time and tries to load all over again. I have let this run over night to see if it would eventually finish (nothing stayed in this apparent loop).

 

2) I have modified the categories display so that it will only display the first child.

Parent = "Search By Title"

First Child = (Alpha-Numeric characters 0-9 and A-Z)

Clicking on First child will display all movie titles in the center portion of catalog/Index.php

 

All of this works exactly as expected except for the Letters "B" and "S" which will result in one of two things happening, either it will jump immediately to "The page cannot be displayed" or it will hang indefinitely!

 

I have completely dissected the problem but can find no rhyme or reason for this error.

 

My Facts:

The children all use an almost exact URL/Link index.php?cPath=1_<2-37 DEPENDS ON THE CHILD IT WILL ACCESS>. Since only the cPath variable changes it makes no sense for the index page to not be found.

 

All of the code on the index page that displays the resulting movie titles are created from the same QUERY so again the failure of only two letters makes no sense!

 

The Letters "B" and "S" are the only two categories that have in excess of 1000 children, but the actual cutoff number of children where the crash occurs differs (B breaks if more than 1092 children | S breaks if more than 1464 children). Tested this by truncating the categories table (Not the actual products), the products still being there do not seem to matter, but the number of children being read does appear to be a problem. The difference in the number of each suggests if may be the total memory used by the actual movie titles, but I have adjusted all settings on Apache and PHP.

 

If I QUERY these records with an external script all records are displayed without problems.

 

There are no anomalies in the categories_name at the cutoff record, nor the records immediately preceding or following that contain any reserved words or special characters that could would cause an issue.

 

 

<HERE IS THE ACTUAL CODE>

######################## MODIFIED FOR PAGING ####################################

$number_of_categories = tep_db_num_rows($categories_query);

 

$allcat = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name ");

$maxcat = tep_db_num_rows($allcat);

$max = round($maxcat/$limit);

if($chk!='1'){

$x=1;

while( $x < $max)

{$bx=''; $bz=''; $lo=($x-1)*$limit; if($lo<0){$lo=0;} if($lo==$a){$bx='<font color="#FF0000"><strong><u>'; $bz='</u></strong></font>';}

if ($zz==24){echo "<BR>"; $zz=0; }

echo '<a href="' . tep_href_link(FILENAME_DEFAULT).'?cPath='.$chk.'&a='.$lo.'">'.$bx.$x.$bz.'</a>  ';

$x++; $zz++;

}

echo "<br><br>";

}

$rows = 0; $mct=0;

while ($categories = tep_db_fetch_array($categories_query)) {

$rows++; $mct++;

$cPath_new = tep_get_path($categories['categories_id']);

$catn=str_replace("'","'",$categories['categories_name']); $catn=str_replace('"','"',$categories['categories_name']);

if ($categories['categories_id'] > 37) {$mcpr=3; $width = (int)(100 / $mcpr) . '%';

$zimage=explode('/',$categories['categories_image']); $zimage=$zimage[0].'/tn/'.$zimage[1];

echo ' <td align="left" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_MANTA_INDEX, 'products=' . urlencode($categories['categories_name'])) .'"><br>' . $categories['categories_name'] . '</a></td>' . "\n";

}else{ $mcpr=6; $width = (int)(100 / $mcpr) . '%'; echo ' <td align="left" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT) . '</a></td>' . "\n";}

if ((($rows / $mcpr) == floor($rows / $mcpr)) && ($rows != $number_of_categories)) {

echo ' </tr>' . "\n";

echo ' <tr>' . "\n";

}

}

##############################################################################

 

< END CODE>

 

Anyway those are the fact/results of my testing and at this point I am completely stumped as to why this is happening.

 

Is there anything in the OSCommerce 2.2 RC2 code that has some sort of memory cap?

Number of children in a subcategory?

Actual length of the field?

Or anything else that could cause this issue?

 

I am convinced the trouble lies buried within the OSCommerce 2.2 RC2 code because I can write scripts that access all these records 6 ways to sunday and none of them have any issues displaying completely.

 

Thanks in advance for any help or light you can shed on this issue. :blush:

[Jan Zonjee]

Is there anything in the OSCommerce 2.2 RC2 code that has some sort of memory cap?

Number of children in a subcategory?

Actual length of the field?

Or anything else that could cause this issue?

 

I am convinced the trouble lies buried within the OSCommerce 2.2 RC2 code because I can write scripts that access all these records 6 ways to sunday and none of them have any issues displaying completely.

The categories box code in osC is infamous for firing of an insane amount of queries when you have a lot categories and/or subcategories. Last night someone had a site that had 1327 queries on the front page.

 

Using the Optimize categories box v1.2 contribution he cut that to 26 (so to 2% of what it used to be).

 

Since you use a special categories box you will likely need some custom coding to be able to use that.

 

The problem in the admin is also due to the amount of categories. The drop-down menu in the top right corner is one of the culprits. If you download the Optimize categories box contribution I think it also mentions which contribution can be used for that.

[Manta2000]

The categories box code in osC is infamous for firing of an insane amount of queries when you have a lot categories and/or subcategories. Last night someone had a site that had 1327 queries on the front page.

 

Using the Optimize categories box v1.2 contribution he cut that to 26 (so to 2% of what it used to be).

 

Since you use a special categories box you will likely need some custom coding to be able to use that.

 

The problem in the admin is also due to the amount of categories. The drop-down menu in the top right corner is one of the culprits. If you download the Optimize categories box contribution I think it also mentions which contribution can be used for that.

 

 

Thanks, I'll add it in and see if it helps. :mellow:

 

I've been writing for 23yrs so I am pretty sure I can make it work. Once I get the hang of this OSCommerce I will be able to give back to the community. For instance I wrote an import utility to work off CSV, but it is for having the movie titles as the categories and so would require some modification to be useful for most. Still it is only 190 lines of code and if anyone would like it they are welcome to it. (be aware you will need to be comfortable with PHP to modify this to work for other types of catalogs)

 

Well, here goes, hopefully the Optimize categories will stop the problem with B's and S's not being displayed. :(

[Manta2000]

:wacko: Well that didn't help, but the mod works ok.

 

Thanks for the advice on the mod...

 

Anyone have any other suggestions?

 

BTW item 1) "I can no longer view the categories section in OSCommerce 2.2 RC2," Refers to Admin. :blush:

[Jan Zonjee]

Anyone have any other suggestions?

Better install Output Queries Debug on the admin side (not described in there but doesn't take a rocket scientist to find out how to install in the admin). Then you can visualize the problem (queries).