Custom Product add routine fails to find recorc after $db->execute

[Doveman]

This is a simplified version of code that read through a csv file and adds manufactures and product to the database. It is suppose to only add manufactures that do not alread exist in the table, however it can't find a duplicate record that was just added previously in this sequence. I don't understand why after successfully inserting a record it can't find it when it reads the next record that has the same manufacturers name and thereby skipping the insert of a duplicate record. In the actual program I use the zen_db_perform but I did this way as it is a little easier to debug.
I would appreciate any input you could give.

>>>>>>>>> HERE IS THE CODE <<<<<<<<<<

while (($data = fgetcsv($handle, 0, "    ",chr(0))) !== FALSE) {

   echo chr(13).$data[$manufacturer_sn].chr(13);
   $mfsql = "SELECT count(*) as total, manufacturers_id, manufacturers_name FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_name=\"" . $data[$manufacturer_sn] . "\"";
   echo $mfsql.chr(13);

   $manufacturers = $db->Execute($mfsql);
   echo 'counts '.$manufacturers->fields['total'].chr(13);
   echo 'search name '.$data[$manufacturer_sn].chr(13);
   echo 'id '.$manufacturers->fields['manufacturers_id'].chr(13);
   echo 'name '.$manufacturers->fields['manufacturers_name'].chr(13);

   if ($manufacturers->fields['manufacturers_name'] != $data[$manufacturer_sn]) {
      echo 'No Match'.chr(13);
       $today = date("Y-m-d G:i:s");
       $query = "INSERT INTO manufacturers (manufacturers_name, date_added) VALUES (\"".$data[$manufacturer_sn]."\",\"".$today."\")";
       echo '$query '.$query.chr(13);
       $db->Execute($query);

       $manufacturers_id = $db->insert_ID();
       echo $manufacturers_id.chr(13);
   }
} 

>>>>>>> RESULTS OF ABOVE EXECUTED CODE <<<<<<<

****** This first entry is correct as there is no existing manfacturer record for "STANLEY TOOLS". *****

STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 17:13:29")
4

****** These next 2 records are incorrect as it just sucessfully inserted the same manufacturer previously. *******

STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 17:13:29")
5

STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 17:13:29")
6

[DrByte]

Just reading thru the code I don't see why it's failing to find the previously inserted record.

Your output shows that it added records 4, 5, 6. When you view the database with phpMyAdmin, or console or whatever, what's actually in the table? Does it have 3 STANLEY TOOLS entries?

What happens when you run the whole thing again? Are you getting another 3 new duplicates in the db? Unless there's some unexpected caching going on, your code looks fine.

Is there any point in doing trim() around your $data[$manufacturer_sn] var?

While grasping at more straws, where you echo out $manufacturers->fields['total'], how about echoing out $manufacturers->RecordCount() too?

[mc12345678]

I might suggest when performing the execute that you include the remaining "attributes" so that you can end the execute with the appropriate flag to clear the cache. In ZC 1.5.4, select queries do get cached, and having seen such a "simple" series of statements give the same problem before, the solution was to clear the cache as part of the query.

The basic command structure of the Execute is:

Execute($zf_sql, $zf_limit = false, $zf_cache = false, $zf_cachetime=0, $remove_from_queryCache = false)

Providing the default values for each of the middle three with the last of true should resolve your issue.

[mc12345678]

Well, then there is the matter of code consistency. In the select statement the table is referenced by TABLE_MANUFACTURER, but in the insert the table is referenced by name... Fine if there is no DB_PREFIX in the includes/configure.php, but also could be a problem.

>>>>>>>>> HERE IS THE CODE <<<<<<<<<<

while (($data = fgetcsv($handle, 0, "    ",chr(0))) !== FALSE) {

   echo chr(13).$data[$manufacturer_sn].chr(13);
   $mfsql = "SELECT count(*) as total, manufacturers_id, manufacturers_name FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_name=\"" . $data[$manufacturer_sn] . "\"";
   echo $mfsql.chr(13);

   $manufacturers = $db->Execute($mfsql);
   echo 'counts '.$manufacturers->fields['total'].chr(13);
   echo 'search name '.$data[$manufacturer_sn].chr(13);
   echo 'id '.$manufacturers->fields['manufacturers_id'].chr(13);
   echo 'name '.$manufacturers->fields['manufacturers_name'].chr(13);

   if ($manufacturers->fields['manufacturers_name'] != $data[$manufacturer_sn]) {
      echo 'No Match'.chr(13);
       $today = date("Y-m-d G:i:s");
       $query = "INSERT INTO " . TABLE_MANUFACTURERS . " (manufacturers_name, date_added) VALUES (\"".$data[$manufacturer_sn]."\",\"".$today."\")";
       echo '$query '.$query.chr(13);
       $db->Execute($query);

       $manufacturers_id = $db->insert_ID();
       echo $manufacturers_id.chr(13);
   }
}

[DrByte]

Yes, chad's right ... if you've got a "foo_manufacturers" table and also a "manufacturers" table, then you're reading from one and inserting to the other. Changing to TABLE_MANUFACTURERS in all places will solve that (because TABLE_MANUFACTURERS accounts for the prefix).

[Doveman]

Thanks to all of you for your suggestions:
I went through each one and here is what I found.

"Your output shows that it added records 4, 5, 6. When you view the database with phpMyAdmin, or console or whatever, what's actually in the table? Does it have 3 STANLEY TOOLS entries?"

When I look at the table in phpMyAdmin all the records are there.

"What happens when you run the whole thing again? Are you getting another 3 new duplicates in the db? Unless there's some unexpected caching going on, your code looks fine."

When I run ther application a 2nd time (w/o clearing the table) it actually finds the records and skips the insert.


"Is there any point in doing trim() around your $data[$manufacturer_sn] var?"

As you can see in the revised code I did that but it made no difference.

"Providing the default values for each of the middle three with the last of true should resolve your issue."

Again as you can see in the revised code I did that but it made no difference.


In the full version of this program I also use the same logic to add products to the products table and products descriptions and that code works perfectly.
In the actual test run there are 100 records. All give the same results. I did notice that when I added the RecordCount echo that all the attempts return a RecordCount of 1 even though it doesn't find the record.

Again THANKS for any help you can give.

>>>>>>>>> HERE IS THE REVISED CODE <<<<<<<<<<

while (($data = fgetcsv($handle, 0, " ",chr(0))) !== FALSE) {

echo chr(13).$data[$manufacturer_sn].chr(13);
$mfsql = "SELECT count(*) as total, manufacturers_id, manufacturers_name FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_name=\"" . trim($data[$manufacturer_sn]) . "\"";
echo $mfsql.chr(13);

$manufacturers = $db->Execute($mfsql);
echo 'counts '.$manufacturers->fields['total'].chr(13);
echo 'RecordCount '.$manufacturers->RecordCount().chr(13);
echo 'search name '.$data[$manufacturer_sn].chr(13);
echo 'id '.$manufacturers->fields['manufacturers_id'].chr(13);
echo 'name '.$manufacturers->fields['manufacturers_name'].chr(13);
if ($manufacturers->fields['manufacturers_name'] != $data[$manufacturer_sn]) {
echo 'No Match'.chr(13);
$today = date("Y-m-d G:i:s");
$query = "INSERT INTO ". TABLE_MANUFACTURERS ." (manufacturers_name, date_added) VALUES (\"".trim($data[$manufacturer_sn])."\",\"".$today."\")";
echo '$query '.$query.chr(13);
$db->Execute($query, $zf_limit = false, $zf_cache = false, $zf_cachetime=0, $remove_from_queryCache = true);

$manufacturers_id = $db->insert_ID();
echo $manufacturers_id.chr(13);
}
}

>>>>>>> REVISED RESULTS OF ABOVE EXECUTED CODE <<<<<<<


STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
RecordCount 1
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 22:28:23")
4

STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
RecordCount 1
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 22:28:23")
5

STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
RecordCount 1
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 22:28:23")
6

[mc12345678]

Thanks to all of you for your suggestions:
I went through each one and here is what I found.

"Your output shows that it added records 4, 5, 6. When you view the database with phpMyAdmin, or console or whatever, what's actually in the table? Does it have 3 STANLEY TOOLS entries?"

When I look at the table in phpMyAdmin all the records are there.

"What happens when you run the whole thing again? Are you getting another 3 new duplicates in the db? Unless there's some unexpected caching going on, your code looks fine."

When I run ther application a 2nd time (w/o clearing the table) it actually finds the records and skips the insert.


"Is there any point in doing trim() around your $data[$manufacturer_sn] var?"

As you can see in the revised code I did that but it made no difference.

"Providing the default values for each of the middle three with the last of true should resolve your issue."

Again as you can see in the revised code I did that but it made no difference.


In the full version of this program I also use the same logic to add products to the products table and products descriptions and that code works perfectly.
In the actual test run there are 100 records. All give the same results. I did notice that when I added the RecordCount echo that all the attempts return a RecordCount of 1 even though it doesn't find the record.

Again THANKS for any help you can give.

>>>>>>>>> HERE IS THE REVISED CODE <<<<<<<<<<

while (($data = fgetcsv($handle, 0, " ",chr(0))) !== FALSE) {

echo chr(13).$data[$manufacturer_sn].chr(13);
$mfsql = "SELECT count(*) as total, manufacturers_id, manufacturers_name FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_name=\"" . trim($data[$manufacturer_sn]) . "\"";
echo $mfsql.chr(13);

$manufacturers = $db->Execute($mfsql);
echo 'counts '.$manufacturers->fields['total'].chr(13);
echo 'RecordCount '.$manufacturers->RecordCount().chr(13);
echo 'search name '.$data[$manufacturer_sn].chr(13);
echo 'id '.$manufacturers->fields['manufacturers_id'].chr(13);
echo 'name '.$manufacturers->fields['manufacturers_name'].chr(13);
if ($manufacturers->fields['manufacturers_name'] != $data[$manufacturer_sn]) {
echo 'No Match'.chr(13);
$today = date("Y-m-d G:i:s");
$query = "INSERT INTO ". TABLE_MANUFACTURERS ." (manufacturers_name, date_added) VALUES (\"".trim($data[$manufacturer_sn])."\",\"".$today."\")";
echo '$query '.$query.chr(13);
$db->Execute($query, $zf_limit = false, $zf_cache = false, $zf_cachetime=0, $remove_from_queryCache = true);

$manufacturers_id = $db->insert_ID();
echo $manufacturers_id.chr(13);
}
}

>>>>>>> REVISED RESULTS OF ABOVE EXECUTED CODE <<<<<<<


STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
RecordCount 1
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 22:28:23")
4

STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
RecordCount 1
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 22:28:23")
5

STANLEY TOOLS
SELECT count(*) as total, manufacturers_id, manufacturers_name FROM manufacturers WHERE manufacturers_name="STANLEY TOOLS"
counts 0
RecordCount 1
search name STANLEY TOOLS
id
name
No Match
$query INSERT INTO manufacturers (manufacturers_name, date_added) VALUES ("STANLEY TOOLS","2016-01-02 22:28:23")
6

My providing incomplete discussion, strikes again. Try the below revised:

while (($data = fgetcsv($handle, 0, "",chr(0))) !== FALSE) {

   echo chr(13).$data[$manufacturer_sn].chr(13);
   $mfsql = "SELECT count(*) as total, manufacturers_id, manufacturers_name FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_name=\"" . trim($data[$manufacturer_sn]) . "\"";
     echo $mfsql.chr(13);

     $manufacturers = $db->Execute($mfsql, false, false, 0, true);
     echo 'counts '.$manufacturers->fields['total'].chr(13);
     echo 'RecordCount '.$manufacturers->RecordCount().chr(13);
     echo 'search name '.$data[$manufacturer_sn].chr(13);
     echo 'id '.$manufacturers->fields['manufacturers_id'].chr(13);
     echo 'name '.$manufacturers->fields['manufacturers_name'].chr(13);
   if ($manufacturers->fields['manufacturers_name'] != trim($data[$manufacturer_sn])) {
      echo 'No Match'.chr(13);
       $today = date("Y-m-d G:i:s");
       $query = "INSERT INTO ". TABLE_MANUFACTURERS ." (manufacturers_name, date_added) VALUES (\"".trim($data[$manufacturer_sn])."\",\"".$today."\")";
       echo '$query '.$query.chr(13);
       $db->Execute($query);

       $manufacturers_id = $db->insert_ID();
       echo $manufacturers_id.chr(13);
   }
}

Corrected usages of $db>Execute and the comparison of text for the case of when should add...

[Doveman]

Thank you! Thank you! Thank you! You made my morning. It works perfectly.
I have been working on this for days. Such a simple fix. One question. Why is the flushing of cache done when reading instead of writing. Just trying to get better at this.

[mc12345678]

Thank you! Thank you! Thank you! You made my morning. It works perfectly.
I have been working on this for days. Such a simple fix. One question. Why is the flushing of cache done when reading instead of writing. Just trying to get better at this.

Have to think about the purpose of the cache... Every time that a query is sent to the database, the code/database has to determine: should I provide the answer I provided previously or do I go and figure it out again. Well there is a lot of "statistics/computation" whatever algorithm that goes into making that decision. But it is all based on the query sent to the process... An insert query provides a result, but the result is what was given to it or some form of a false/failure error. Anyways, I can't see a good reason to cache an insert query... Now a select query... Well, in most cases nothing has changed with the data that is being accessed and so the determination is made to reuse the previous data. But in your case, whatever is needed to a requery instead of a reuse is not met and therefore have to tell the database, thanks for trying to be helpful, but I'm actually looking for new data because I just may have changed it a moment before.

It's only something that I've seen needed within such a "small" or "short" loop. Yes you can go back and look at your settings for caching, but this "effect" is actually something that was incorporated into ZC regardless of the setting and if desired to be different requires some code modification. Just easier to "program" around it instead of in effect reducing the speed of the site.. (Afterall, if the data can be reused without concern, then the system doesn't have to work as hard to provide the same results each and every time.)

Also, not sure what the ultimate intended purpose is, but there are plugins that do population of such data already.. EasyPopulate v4 for example handles adding Manufacturers as well as a number of other pieces of data... But if you enjoy development and time involved with reinventing work, have at it.