System Error Log

[Ben224]

Hello I have the following system error logged. Can anyone help resolve?

 

File: [database.class.php] Line: [576] "SELECT COUNT(*) AS Count FROM ``CubeCart_inventory` AS `I` LEFT JOIN `CubeCart_option_matrix` AS `M` on `I`.`product_id` = `M`.`product_id`` WHERE use_stock_level = 1 AND (((I.stock_warning > 0 AND M.stock_level <= I.stock_warning) OR (I.stock_warning <= 0 AND M.stock_level <= 2)) OR ((I.stock_warning > 0 AND I.stock_level <= I.stock_warning) OR (I.stock_warning <= 0 AND I.stock_level <= 2)));" - Incorrect table name ''

 

Many thanks in advance.

[bsmither]

CC525 has code changes that gets a better count of eligible records used for the pagination. Unfortunately, the new code does not handle all database queries without error.

 

However, this error does not crash CubeCart, nor does it cause any data to be corrupted or lost. So you can ignore it for now.

[Ben224]

Ok, thank you for clarifying.

[Al Brookbanks]

I think the issue here is a syntax error on the query. 

``CubeCart_

 instead of;

`CubeCart_

[Al Brookbanks]

Please try this fix. 

 

Open classes/database.class.php and find at line 117:

public function count($table = false, $field = false, $where = false) {

Under this add:

if(!stristr($table,'JOIN')) {
    $wrapper = '`';
} else {
    $wrapper = '';
}

 

The below this find:

$this->_query = "SELECT COUNT($field) AS Count FROM `{$this->_prefix}$table` ".$this->where($table, $where).';';

Replace with:

$this->_query = "SELECT COUNT($field) AS Count FROM $wrapper{$this->_prefix}$table$wrapper ".$this->where($table, $where).';';

That fixes it for me. 

[Ben224]

Hello apologies for not responding sooner.

 

I have implemented the above code changes which has resolved the error.

 

Thanks guys.

[markThomas]

OK, i was receiving the above quoted error, applied the fix as described. The line 576 error is gone and now I have another errot that looks the same, but is referencing a different line. 

 

File: [database.class.php] Line: [584] "SELECT COUNT(*) AS Count FROM cc_`cc_CubeCart_inventory` AS `I` LEFT JOIN `cc_CubeCart_option_matrix` AS `M` on `I`.`product_id` = `M`.`product_id` WHERE use_stock_level = 1 AND (((I.stock_warning > 0 AND M.stock_level <= I.stock_warning) OR (I.stock_warning <= 0 AND M.stock_level <= 5)) OR ((I.stock_warning > 0 AND I.stock_level <= I.stock_warning) OR (I.stock_warning <= 0 AND I.stock_level <= 5)));" - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `I` LEFT JOIN `cc_CubeCart_option_matrix` AS `M` on `I`.`product_id` = `M`.`p' at line 1

 

Will check the code changes as described above, but it was pretty easy copy/paste.Any ideas?

[bsmither]

There are a lot of calls to $GLOBALS['db']->count() that do not include the database prefix. Therefore, it is necessary to add it to the $table variable in count():

"SELECT COUNT($field) AS Count FROM `{$this->_prefix}$table` "

 

However, the database class itself calls count() -- near line 584, in the select() method -- and passes that method's $table contents to count(). But the select() $table can contain a compound JOIN statement as is found in the dashboard.index.inc.php, near line 189, and compound queries generally require that the string already have the database table prefix.:

'`'.$GLOBALS['config']->get('config', 'dbprefix').'CubeCart_inventory` AS `I` LEFT JOIN `'.$GLOBALS['config']->get('config', 'dbprefix').'CubeCart_option_matrix` AS `M` on `I`.`product_id` = `M`.`product_id`';

 

So, we end up with two table prefixes.

 

I, myself have been caught with code that works for me didn't work for others because I don't use a table prefix, but they do.

 

Devellion's DEMO CubeCart installation also does not use a table prefix. Maybe their development installation's do not use one either.

 

This situation doesn't crash the store -- just gives a false count of the number of items that are at or below the stock warning level.

[markThomas]

Thanks again for the discussion. 

I noticed that your last sentence sums up the heart of my clients dilemma - " just gives a false count of the number of items that are at or below the stock warning level."

 

My client's store deals with digital documents only, for a property management company. So the stock level is really a non-issue. I have the stock level warnings turned off. However, all the documents that they provide have to be listed for all the associations, even if they are not needed or have not been created. To address that condition, I changed the "not in stock" text field to "not applicable", identifying docs that were not available, but still listed as per the state regulations. 

The catch is that the "not applicable" text only works with the stock levels turned on, as far as I can tell. So the stock level control has to be turned back on, creating the long list of system errors. I can not have that log filling up like that, even though the site is still working. 

 

My short list of solutions is as follows:

1. Leave stock level controls on - set it to some enormous number. (A bit tedious now, as all the documents would have to be opened and edited.)
2. Turn stock levels off - check the status to disable for those documents that are not available. (Not sure if the state regulations will allow that. Something about all docs must be listed, even if not available.)
3. Leave stock levels off - find code solution to indicate that certain docs are "not applicable", something that a trained assistant could manage as the document status changes. 
4. Other - ???

 

Thanks for reading this far. I am sharing this information with my client to get a better idea of required listing rules. 

The site url:  www.tmadocuments.com

[bsmither]

Allow me to ask about your client's store... Do they put any documents On Sale? Do they Feature any documents? (CubeCart defaults the Featured property to 'Yes'.) Do they pay attention to the Brand, Manufacturer, or Condition of the documents?

 

If any of these properties of a document are not used for their intended purpose, we can add code to the skin templates to enable/disable the Add to Cart button based on that property.

[markThomas]

Thanks bsmither... 

the answer is no to all of the mentioned items. 

 

The documents are in pdf form, there are no sales (legal association business), we have not added any brand or manufacture information. 

[bsmither]

Cool! Let me plan out how to approach and attack this. I'll be back shortly.

[bsmither]

What I would like to do is to test for the value of the product's condition parameter.

 

I used the CC525 Kurouto skin template file content.product.php, lines 67-79, for these edits. The following is a simplification of what to show the customer based on a few conditions:

 

If we are otherwise allowed to purchase, and we are not in catalogue mode, and the product's condition is not "Inapplicable", then show the Add to Basket button.

Else, we cannot buy it and here's why:

>> If we are hiding prices, then say, "Login to view prices."

>> Otherwise, if we are out of stock, then say, "We are out of stock."

>> Otherwise, if the product's condition is 'n/a', then say, "Not Applicable."

>> Done.

Done.

 

The red phrases shown above are new to the template code. Find the relevant lines below and make the edits:

{if ($CTRL_ALLOW_PURCHASE) && (!$CATALOGUE_MODE) && ($PRODUCT.condition ne "n/a")}
 
and
 
    {else if $PRODUCT.condition eq "n/a"}
        <p class="buy_button"><strong>Not Applicable</strong></p>
    {/if}
{/if}

This takes care of the View Product page.

 

There is also similar code in the View Category page (content.category.php, lines 70-74):

    {if $product.ctrl_purchase && !$CATALOGUE_MODE && ($product.condition ne "n/a")}
      <p class="buy_button"><input type="text" name="add[{$product.product_id}][quantity]" value="1" class="quantity" /> <input type="submit" value="{$LANG.catalogue.add_to_basket}" class="button_white" /></p>
    {elseif $product.out}
      <p class="buy_button">{$LANG.catalogue.out_of_stock_short}</p>
    {elseif $product.condition eq "n/a"}
      <p class="buy_button">Not Applicable</p>
    {/if}

And in the Latest Products area of the HomePage (if you have that switched on). I'll let you find that.

 

What we next need to do is discover how CubeCart defaults to using the term new as the condition for all newly added products. In the admin template file products.index.php, lines 148-152, we are given a drop-down selector with these choices: New, Used, and Refurbished. (This property of the product was created to comply with the requirements for inventory feeds to Google Merchant Services.)

 

We need to add another choice: Not Applicable. So add this line above line 152, the closing </select> tag:

<option value="n/a" {if $PRODUCT.condition == 'n/a'}selected="selected"{/if}>Not Applicable</option>

Now you need to edit all the documents that are not applicable to being in your inventory and select this condition for them. Or, if that may be too onerous, you can use phpMyAdmin to access your database directly, browsing the contents of the CubeCart_inventory table. If you can identify by name the documents you want to have the "n/a" condition, then changing the value in the 'condition' column will go a lot faster.

[bsmither]

We may want to ask the moderator to split off this conversation starting at post#9 to it's own subject.

[markThomas]

OK, I understand and am testing as we speak. 

 

??? I have over 250 stock warnings. Can this be reset using phpMyAdmin or is there some other way? I need to kill all these warnings in order to fully test the above changes. 

Thanks

 

M

[markThomas]

 

 

What we next need to do is discover how CubeCart defaults to using the term new as the condition for all newly added products. In the admin template fileproducts.index.php, lines 148-152, we are given a drop-down selector with these choices: New, Used, and Refurbished. 

 

Is this template file found in :   admin/skins/default/templates/products.index.php ?

[bsmither]

In admin, there should be a link that will tell CubeCart to TRUNCATE (delete all the records) in the system error log table. It is located above the list of messages.

[markThomas]

Would you be so kind as to check me on this? Here is the code as I have currently entered. 

 

<div><label for="condition">{$LANG.catalogue.condition}</label>

   <span>

  <select name="condition" id="condition" class="textbox" type="text">

  <option value="new" {if $PRODUCT.condition == 'new'}selected="selected"{/if}>{$LANG.catalogue.condition_new}</option>

<option value="used" {if $PRODUCT.condition == 'used'}selected="selected"{/if}>{$LANG.catalogue.condition_used}</option>

<option value="refurbished" {if $PRODUCT.condition == 'refurbished'}selected="selected"{/if}>{$LANG.catalogue.condition_refurbished}</option>

  <option value="n/a" {if $PRODUCT.condition == 'n/a'}selected="selected"{/if}>Not Applicable</option>

  </select>

  </span>

  </div>

 

Is it possible that the n/a product condition line should be changed?

 

<option value="n/a" {if $PRODUCT.condition == 'n/a'}selected="selected"{/if}>{$LANG.catalogue.condition_not_applicable}</option>

[bsmither]

In CubeCart's language packs, there is no phrase for this newly added condition. The phrases (actually, Smarty variables) do exist for the current conditions:

 

The language pack key and the Smarty variable using that key:

 

  <group name="catalogue">

    <string name="condition_new" introduced="5.0.0"><![CDATA[New]]></string> /// {$LANG.catalogue.condition_new}:

    <string name="condition_used" introduced="5.0.0"><![CDATA[used]]></string> /// {$LANG.catalogue.condition_used}
    <string name="condition_refurbished" introduced="5.0.0"><![CDATA[Refurbished]]></string> /// {$LANG.catalogue.condition_refurbished}
  </group>

 

There is no string name key with the name "n/a", thus we cannot expect that a Smarty variable could use it. So, not having a Smarty variable (variables that are to be replaced by an actual phrase from the currently active language pack), we must hard-code the phrase into the HTML.

[markThomas]

Got-cha. Thanks

 

Well the code as written appears to be working. The products have the new condition option. When set the product is on the list, and the buy button is not there. In fact nothing is there. 

I thought I read in the code that the words "Not Applicable" were supposed to be in that button space (???) So currently it cannot be bought, but it may appear to be a broken design.

Thoughts?

[bsmither]

Reviewing from post#13, the new code is in red:

 

Find the relevant lines below (content.product.php, near lines 67-79) and make the edits:

 

{if ($CTRL_ALLOW_PURCHASE) && (!$CATALOGUE_MODE) && ($PRODUCT.condition ne "n/a")}
 
and then these lines at the end of this group of statements:
 
    {else if $PRODUCT.condition eq "n/a"}
        <p class="buy_button"><strong>Not Applicable</strong></p>
    {/if}
{/if}

 

 

 

There is also similar code in the View Category page (content.category.php, lines 70-74):

 

    {if $product.ctrl_purchase && !$CATALOGUE_MODE && ($product.condition ne "n/a")}
      <p class="buy_button"><input type="text" name="add[{$product.product_id}][quantity]" value="1" class="quantity" /> <input type="submit" value="{$LANG.catalogue.add_to_basket}" class="button_white" /></p>
    {elseif $product.out}
      <p class="buy_button">{$LANG.catalogue.out_of_stock_short}</p>
    {elseif $product.condition eq "n/a"}
      <p class="buy_button">Not Applicable</p>
    {/if}

 

 

Also, if I didn't mention it, you should clear your skin template cache (admin, Maintenance, Rebuild tab, Clear Cache).

[bsmither]

And on viewing your site, I do see Not Applicable for example on the View Category page for 9 on F, products AR and AC.

 

When viewing AC, I also get a Not Applicable.

 

What I don't see is any indication on what Not Applicable means.

 

Maybe a more complete phrase: "Not Applicable and Not Available for this Association".

Also, kinda weird, I got a "Service Unavailable - the site may be too busy" error message.

 

You may want to ask your hosting provider if your site is bumping up against a limit of some kind.

[markThomas]

OK got it. One extra space and one capitalized word  change made it happen. My test product is set to not use stock controls and condition set to not applicable and it shows the bold type Not Applicable. 

Sincere thank you. 

 

As for the other Not Applicable, those are coming from the stock level control text change. I changed the words in the Admin/Language section for items that were out of stock to Not Applicable. Stock controls are still in effect on those items. I have to move into the database and make some wholesale changes to turn off stock controls for every product. Then I have to set the Condition value to Not Applicable for the items that are truly not available. 

 

The client is word-smithing the front end of the store to better instruct the buyers as to the nature of the documents. Some are available, some are not, all have to be shown. 

 

As for the busy site, yes, I hit that a couple times as well. Will have to ask some questions when I get a chance.