Query Issue

[RocketSites]

Hi,

Working on a script and have encountered a peculiar issue.

A loop performs a query in the standard Zen Cart format X amount of times.

$manufacturer_sql = $db->Execute("SELECT * FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_name = '" . $manu . "'");

However, this always returns 0 results (using RecordCount()), even when a manufacturer with the specified name DOES exist.

When the script is run a second time, the query returns the results as expected.

It's as thought the results of the first instance of the query are cached, even though SQL_CACHE_METHOD is none.

The query is definitely being executed during each iteration of the loop, but the results do NOT change.

What the loop does:

1. Search the manufacturers table for manufacturer name X
2. If this manufacturer exists, do nothing
3. If it does not, then insert it into the manufacturers table

The exact same code works flawlessly on previous Zen Cart versions, and changing the Zen Cart query to a mysqli_query produces the expected results - the Zen Cart query just will not work on 1.5.3

Any ideas?

Thank you!

[RocketSites]

Changing the contents of /includes/classes/db/mysql/query_factory.php to a previous ZC version resolves the issue.

I'm wondering if this change is causing my issue? http://www.zen-cart.com/showthread.p...-Query-Factory

[mc12345678]

It may be possible to provide assistance if the code you are/were writing were provided, otherwise the above two posts don't make any sense. The sql always provides the same results of 0 except when run a second time (not exactly always if a different result is encountered), running a sql query under previous versions and changing to mysqli_query, now adding that changing one of the most central core files to an older version, referencing a discussion about a file that is reported as being applicable to a version not being discussed...

Okay, I get or could possibly make assumptions about things, but it would be much more assistive to remove some of the ambiguity. From the looks of it and how everything is described it is as if no query using $db->Execute() should work... Is there possibly some prep work that hasn't been done on the data/query to ensure that it will be functional? Or some clearing of some cobweb or another? If you put any other query into that statement at that point does it work, or does it respond like the current one does?

[mc12345678]

Also, how many manufacturer's are being typically cycled through? Is this really searching on one manufacturer at a time? Why not pass a group of names and figure out from the results what to do. Wonder if there is some sort of query clash going on with many calls asking the same question. Also, there is/was a modification in sessions.php I thought that might help such a query clash condition. Basically if the cache is the database and continue. To get errors about the primary key already being indexed... On second. Thought it may not be applicable even if caching were on. Not sure... Any who... Some questions above to try to help.

[RocketSites]

referencing a discussion about a file that is reported as being applicable to a version not being discussed...

The changes in the linked thread have been implemented in 1.53.

From the looks of it and how everything is described it is as if no query using $db->Execute() should work...

No, I'm suggesting that this core function DOES of course work, but the results are cached.

If you put any other query into that statement at that point does it work, or does it respond like the current one does

Any query does the same - when the script run, the results from the FIRST iteration of the query appear to be cached and are presented as the results for all subsequent iterations of the query.

The script is parsing an XML file, which contains product data in nodes like the following:

<PRODUCT>
      <PRODUCTS_NAME>Lorem ipsum.</PRODUCTS_NAME>
      <PRODUCTS_DESCRIPTION>Lorem ipsum.</PRODUCTS_DESCRIPTION>
      <PRODUCTS_IMAGE>1019.jpg</PRODUCTS_IMAGE>
      <PRODUCTS_PRICE>1.90</PRODUCTS_PRICE>
      <PRODUCTS_MODEL>1019</PRODUCTS_MODEL>
      <ITEM_BRANDNAME>Panasonic</ITEM_BRANDNAME>
      <ITEM_UPC>073096500235</ITEM_UPC>
      <ITEM_HEIGHT>0.500</ITEM_HEIGHT>
      <ITEM_LENGTH>4.250</ITEM_LENGTH>
      <ITEM_DIAMETER>3.375</ITEM_DIAMETER>
      <PRODUCTS_WEIGHT>0.16</PRODUCTS_WEIGHT>
      <MANUFACTURERS_NAME>Utm distributing</MANUFACTURERS_NAME>
      <ITEM_VENDOR_NUMBER>UM-3NPA/4B</ITEM_VENDOR_NUMBER>
      <DATE_RECIEVED>0000-00-00 00:00:00</DATE_RECIEVED>
      <PROP_PACKAGING>Blister Card</PROP_PACKAGING>
      <PRODUCT_CLASS>BAT</PRODUCT_CLASS>
      <PRODUCTS_TYPE>Batteries</PRODUCTS_TYPE>
    </PRODUCT>

The node, as you can see, contains the product's MANUFACTURERS_NAME. As each product is parsed, the manufacturers table is queried to see if this manufacturer already exists in the table. If it does, then simply select the manufacturers_id for later use. If it does not, then insert the manufacturer into the manufacturers table.

When parsing the NEXT product, the same manufacturers table query happens (as this product may have the SAME manufacturer as the previous, and that manufacturer may have JUST been inserted, so we need to do a new query to see if the manufacturer is now in the table).

There are 'many calls asking the same question' - approximately 16,000 products in the XML. But it all works absolutely fine in versions < 1.53.

[RocketSites]

Fastest way to illustrate my point is to create a file on a clean 1.53 install containing the below code and then run it:

<?php
require('includes/application_top.php');

$i = 0;
$times = 100;
while ($i++ < $times){
    
    // Select ALL records from the manufacturers table
    $all_manufacturers_sql = $db->Execute("SELECT * FROM " . TABLE_MANUFACTURERS . " WHERE 1");
    echo 'Iteration ' . $i . '. The manufacturers table currently contains:<br />';
    echo '-------------------------<br />';
    echo '| ID | Name |<br />';
    while(!$all_manufacturers_sql->EOF){
        // Echo each record
        echo '|  ' . $all_manufacturers_sql->fields['manufacturers_id'] . '  | ' . $all_manufacturers_sql->fields['manufacturers_name'] . ' |<br />';
        echo '-------------------------<br />';
        $all_manufacturers_sql->MoveNext();
    }
    echo '<br />';
    
    // Insert Manufacturer X into the manufacturers table
    $sql_data_array = array(
        'manufacturers_name' => 'Manufacturer ' . $i,
        'date_added' => 'now()'  
    );
    zen_db_perform(TABLE_MANUFACTURERS, $sql_data_array);
    
    echo 'I\'ve just inserted "Manufacturer ' . $i . '", which is manufacturers_id ' . $db->Insert_ID() . '.<br />';
    
    echo '<br /><hr />';
}

require('includes/application_bottom.php');
?>

The on-screen output will show you that each time the manufacturers table is queried, it always returns 0 records, even though the previous iteration has just inserted a manufacturer into the table (and you can see the Insert_ID in the previous iteration).

Run the same code on ZC 1.50 and you will see that each time the table is queried it returns all of the manufacturer records in the table, the number of which increases by 1 after each iteration.

[RocketSites]

Having just added a time delay between each iteration, it is now working on 1.53 as it is on 1.50.

So does the latest ZC version have some kind of caching for queries that take place within X amount of time of a previous same/similar query?

[lat9]

Having just added a time delay between each iteration, it is now working on 1.53 as it is on 1.50.

So does the latest ZC version have some kind of caching for queries that take place within X amount of time of a previous same/similar query?

The query_cache processing (i.e. database caching) was introduced by Zen Cart v1.5.1. I'm not sure of its inner workings (i.e. what value X is).

How much of a time delay did you add?

[RocketSites]

The query_cache processing (i.e. database caching) was introduced by Zen Cart v1.5.1. I'm not sure of its inner workings (i.e. what value X is).

That's incredibly helpful, thank you.

So even if SQL_CACHE_METHOD is set to 'none', some does occur?

[lat9]

I can verify that the script that RocketSites provided shows the same unwanted behavior on Zen Cart v1.5.1.