LIMIT amount of option values displayed in Option Value Manager

I'm using Zen Cart 1.5.6b. I would like to limit the total number of Option Values pulled from the database to be accessed by this section. We have 10s of thousands of option values and it's slowed the option value manager page down way too much. I've seen in the option_values_manager.php file where there is a mySQL statement of SELECT * ..... in order to check the values for corruption but I was wondering if anyone knew where I could add a LIMIT to a mySQL statement to have it only pull the first 500 values or so. I'm not trying to limit how many appear on each page, I know that is done in the configuration. I'm trying to modify the php file's database call in order to add a LIMIT clause so it doesn't have to pull every single option value in order to properly build the page. Thanks in advance for your help!

I see a line containing
$products_values = $db->Execute("SELECT p.products_id, pd.products_name, po.products_options_name, pa.options_id
FROM " . TABLE_PRODUCTS . " p,
" . TABLE_PRODUCTS_ATTRIBUTES . " pa,
" . TABLE_PRODUCTS_OPTIONS . " po,
" . TABLE_PRODUCTS_DESCRIPTION . " pd
WHERE pd.products_id = p.products_id
AND pd.language_id = " . (int)$_SESSION['languages_id'] . "
AND po.language_id = " . (int)$_SESSION['languages_id'] . "
AND pa.products_id = p.products_id
AND pa.options_values_id = " . (int)$_GET['value_id'] . "
AND po.products_options_id = pa.options_id
ORDER BY pd.products_name");

Would this perhaps be where I would add the LIMIT after ORDER BY?

That query is only applicable to when deleting an option value.

A query related to display of the current items is associated with $values_query_raw, but that query is sent through the splitPageResults class so that it only looks at the "portion" of items to be displayed... What value do you have for configuration->Maximum->Products Attributes - Option Names and Values Display?

The number presented there should apply the limit that you are seeking when preparing to show the information. Otherwise, there is a possible "delay" in loading earlier on in the file where it is addressing "damaged database, caused by users indiscriminately deleting table data".

A search for $values_query_raw in the developer toolkit brings up nothing across all PHP files in the catalog and admin. I'm working a non-zencart solution for the option managament but for now I really just need to limit the amount of options values it's listing to 500. I know this would keep any backend users from accessing pages of values beyond the first 500 but is exactly as I intend. I tried limiting the statement $values = "SELECT pov.products_options_values_id........ on lines 665 - 673 to 500 and that had the pagination reduce as intended but no values appear in the table. It just states "an error occurred" with no log. What am I missing?

clam_man:

A search for $values_query_raw in the developer toolkit brings up nothing across all PHP files in the catalog and admin. I'm working a non-zencart solution for the option managament but for now I really just need to limit the amount of options values it's listing to 500. I know this would keep any backend users from accessing pages of values beyond the first 500 but is exactly as I intend. I tried limiting the statement $values = "SELECT pov.products_options_values_id........ on lines 665 - 673 to 500 and that had the pagination reduce as intended but no values appear in the table. It just states "an error occurred" with no log. What am I missing?

I'm not finding reference to the information provided above: I was looking in the admin attributes_controller and not finding a similar sql statement. As to not receiving a log, well, there are any of likely reasons for not "seeing" it. Look in the docs website about logs. Basically, you may have to many debug logs for a new one to be generated or possibly be retrieved. Possibly logging may be disabled for any number of reasons, etc.

Providing the entire sql statement in a post may help suss the issue, further providing the code on that area may help as well. It may be necessary to change the Execute portion of the query to there provide the limit instead of in the query itself.

mc12345678:

I'm not finding reference to the information provided above: I was looking in the admin attributes_controller and not finding a similar sql statement. As to not receiving a log, well, there are any of likely reasons for not "seeing" it. Look in the docs website about logs. Basically, you may have to many debug logs for a new one to be generated or possibly be retrieved. Possibly logging may be disabled for any number of reasons, etc.

Providing the entire sql statement in a post may help suss the issue, further providing the code on that area may help as well. It may be necessary to change the Execute portion of the query to there provide the limit instead of in the query itself.

Sorry, I see now... admin/options_values_manager.php. apparently my previous response was not in direct reference to Zen Cart 1.5.6, but instead later versions where that query was modified and better supports the kind of effect desired. Not sure if just using the Zen Cart 1.5.7 of the file would work or if additional effort would be needed such as actually upgrading or just incorporating some of the associated features in that area of code.

I'm currently on 1.5.6b but I could try moving things over to 1.5.7 if that will give me more functionality in the option value manager php file.

clam_man:

I'm currently on 1.5.6b but I could try moving things over to 1.5.7 if that will give me more functionality in the option value manager php file.

Ideally would move to 1.5.8 (currently released version), but yes should always use the latest or at least more recent. Also, yes such newer versions would better support your desired result.

Ah, I finally figured out how to do what I wanted. I was able to work a solution that didn't have to modify the query_factory.php. Thanks for your help though!

clam_man:

Ah, I finally figured out how to do what I wanted. I was able to work a solution that didn't have to modify the query_factory.php. Thanks for your help though!This is where you let the world know what it took to get it going. Karnac has left the building.:P

So sorry I forgot to update you all. I had to comment line 703 "$values = $values . " LIMIT $value_page_start, $per_page";" so I could manually edit a LIMIT for the query generated on line 668 '$values = "SELECT .......'. I had properly add the LIMIT to the large query string but the reason I was getting the white screen of death was because it was concatenating the LIMIT to the query on 703 which threw an error since it had more than 1 LIMIT in the statement. I just trimmed a bunch of fat removing unused option values so I was able to revert this code back to the original functionality as I no longer need this manual limitation.