Database I/O Manager (DbIo) Plugin [Support Thread]

lat9:

I've just submitted v1.6.6 of the Database I/O Manager for the Zen Cart moderators' review and will post back here when it's available for download.

 This release contains changes associated with the following GitHub issues:

#184: PHP 8.1+ preparation.
#185: Correcting PHP Fatal error (Uncaught ArgumentCountError) for DbIoProductsHandler.
#186: Add 'v_products_link' (storefront link) to the DbIoProductsHandler.
Now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2091

Great will test it out!

Is there a comparison of DbIo vs EP4 anywhere? I tried searching and did not find anything significant.

g2ktcf:

Is there a comparison of DbIo vs EP4 anywhere? I tried searching and did not find anything significant.
There *some *information in the DbIo's readme: https://vinosdefrutastropicales.com/product_extra_files/dbio/readme.html

Update: Look specifically in the *Customization *tab.

Hello lat9,

You mentioned that a could change the template to take out unpopulated fields to make the file size smaller..... do I really need to create a template as long as I just take away fields from an existing template?

Thank you!

Shrimp

shrimp-gumbo-mmmhhh:

Hello lat9,

You mentioned that a could change the template to take out unpopulated fields to make the file size smaller..... do I really need to create a template as long as I just take away fields from an existing template?

Thank you!

Shrimp
You don't need to make a special template if you're going to simply remove columns from the import. It's just that having a template that 'supports' the import you're looking to do is a bit of a time-saver.

dear guys, i'm sorry if the answer was already given and i overlooked it. can this be installed besides EP without problems?
it looks great, ( i would if possible use it to export my customers and all, to implement in a new shop)
but my dyslectic puts a break on some reading sometimes. ( do my best honestly)
have a good weekend

kernheimer:

dear guys, i'm sorry if the answer was already given and i overlooked it. can this be installed besides EP without problems?
it looks great, ( i would if possible use it to export my customers and all, to implement in a new shop)
but my dyslectic puts a break on some reading sometimes. ( do my best honestly)
have a good weekend
DbIo is a totally separate entity from EP and can be safely installed together with EP.

lat9:

DbIo is a totally separate entity from EP and can be safely installed together with EP.

thank you so much for your quick answer! very much appreciated! i'm giving it a go :D

Zen Cart 1.5.7c
PHP Version 7.4.16
MySQL 5.7.32

I'm getting a MySQL error with exports for Manufacturers, ProuctsAttribsbasic, and Products.
For a manufacturers export the log is as follows:

[06-Aug-2021 20:33:34 Europe/Paris] Request URI: /testadmin/index.php?cmd=dbio_manager&action=export, IP address: ::1
#1  trigger_error() called at [/Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php:170]
#2  queryFactory->show_error() called at [/Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php:142]
#3  queryFactory->set_error() called at [/Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php:269]
#4  queryFactory->Execute() called at [/Users/steph/Sites/localhost/homebrew/testadmin/includes/classes/dbio/DbIo.php:167]
#5  DbIo->dbioExport() called at [/Users/steph/Sites/localhost/homebrew/testadmin/dbio_manager.php:156]
#6  require(/Users/steph/Sites/localhost/homebrew/testadmin/dbio_manager.php) called at [/Users/steph/Sites/localhost/homebrew/testadmin/index.php:11]
--> PHP Fatal error: 1064: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 'ORDER BY m.manufacturers_id ASC' at line 1 :: SELECT m.manufacturers_id, m.manufacturers_name, m.manufacturers_image, m.date_added, m.last_modified FROM zen_manufacturers AS m, zen_manufacturers_info AS mi WHERE m.manufacturers_id = mi.manufacturers_id AND mi.languages_id =  ORDER BY m.manufacturers_id ASC  ==> (as called by) /Users/steph/Sites/localhost/homebrew/testadmin/includes/classes/dbio/DbIo.php on line 167 <== in /Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php on line 170.

Note the lack of a languages_id just before the ORDER BY.

I went to DbioManufacturersHandler.php and set some error_log outputs after

$export_language = ($this->export_language == 'all') ? $this->languages[$this->first_language_code] : $this->languages[$this->export_language];

error_log("export language: ".$this->export_language);
error_log("first_language_code: ".$this->languages[$this->first_language_code]);
error_log("this export langueage: ".$this->languages[$this->export_language]);
error_log("export language var: ".$export_language);

the results of which are

[06-Aug-2021 20:33:34 Europe/Paris] export language: all
[06-Aug-2021 20:33:34 Europe/Paris] first_language_code: 
[06-Aug-2021 20:33:34 Europe/Paris] this export langueage: 
[06-Aug-2021 20:33:34 Europe/Paris] export language var: 

The error logs for ProuctsAttribsbasic and Products also show a missing language_id in the query.

Any suggestions on the cause, and a solution to this issue?

strelitzia:

Zen Cart 1.5.7c
PHP Version 7.4.16
MySQL 5.7.32

I'm getting a MySQL error with exports for Manufacturers, ProuctsAttribsbasic, and Products.
For a manufacturers export the log is as follows:

[06-Aug-2021 20:33:34 Europe/Paris] Request URI: /testadmin/index.php?cmd=dbio_manager&action=export, IP address: ::1
#1 trigger_error() called at [/Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php:170]
#2 queryFactory->show_error() called at [/Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php:142]
#3 queryFactory->set_error() called at [/Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php:269]
#4 queryFactory->Execute() called at [/Users/steph/Sites/localhost/homebrew/testadmin/includes/classes/dbio/DbIo.php:167]
#5 DbIo->dbioExport() called at [/Users/steph/Sites/localhost/homebrew/testadmin/dbio_manager.php:156]
#6 require(/Users/steph/Sites/localhost/homebrew/testadmin/dbio_manager.php) called at [/Users/steph/Sites/localhost/homebrew/testadmin/index.php:11]
--> PHP Fatal error: 1064: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 'ORDER BY m.manufacturers_id ASC' at line 1 :: SELECT m.manufacturers_id, m.manufacturers_name, m.manufacturers_image, m.date_added, m.last_modified FROM zen_manufacturers AS m, zen_manufacturers_info AS mi WHERE m.manufacturers_id = mi.manufacturers_id AND mi.languages_id = ORDER BY m.manufacturers_id ASC ==> (as called by) /Users/steph/Sites/localhost/homebrew/testadmin/includes/classes/dbio/DbIo.php on line 167 <== in /Users/steph/Sites/localhost/homebrew/includes/classes/db/mysql/query_factory.php on line 170.

> 
> Note the lack of a languages_id just before the ORDER BY.
> 
> I went to DbioManufacturersHandler.php and set some error_log outputs after
> ```
$export_language = ($this->export_language == 'all') ? $this->languages[$this->first_language_code] : $this->languages[$this->export_language];

error_log("export language: ".$this->export_language);
error_log("first_language_code: ".$this->languages[$this->first_language_code]);
error_log("this export langueage: ".$this->languages[$this->export_language]);
error_log("export language var: ".$export_language);

the results of which are

[06-Aug-2021 20:33:34 Europe/Paris] export language: all
[06-Aug-2021 20:33:34 Europe/Paris] first_language_code:
[06-Aug-2021 20:33:34 Europe/Paris] this export langueage:
[06-Aug-2021 20:33:34 Europe/Paris] export language var:

> 
> The error logs for ProuctsAttribsbasic and Products also show a missing language_id in the query.
> 
> Any suggestions on the cause, and a solution to this issue?
Obviously a multiple-language failure is the cause; the solution will be an update to those handlers.  See (<https://github.com/lat9/dbio/issues/193>) for the tracking of these changes.

lat9:

Obviously a multiple-language failure is the cause; the solution will be an update to those handlers. See (https://github.com/lat9/dbio/issues/193) for the tracking of these changes.
OK, I'm not able to reproduce this issue on either a single- or dual-language site.

What languages are present in the site? What version of DbIo? What DB_CHARSET is being used?

Hi,
I have quite a few linked products originally uploaded using easypopulate CSV.

With Easypopulate CSV I had created spreadsheets with linked products listed as another row. This means that I would have multiple rows for one linked product.

All of my Easypopulate spreadsheets need updating to work with DBIO (as the category must be listed in just one column with the ^ delimiter). As DBIO only exports the master category I am unable to easily keep a record of linked categories.

On import the DBIO manager also requires multiple uploads to achieve the desired result – e.g. one import to add a linked category, then another import to set the master category back to what it was.

Would it be possible to import/export v_categories_name as a list of linked categories? I believe this has been mentioned by Torvista in post #361. This would make managing the master category and linked categories much easier.

Alternatively would it be possible to add an additional column such as v_categories_linked to allow linked categories to be clearly defined.

Any help with this would be much appreciated. We have found this add on to be one of the most useful mods available for Zen Cart. I would be happy to make a donation to help cover the costs of implementing these changes.

Many thanks
Richard

richard7315:

Hi,
I have quite a few linked products originally uploaded using easypopulate CSV.

With Easypopulate CSV I had created spreadsheets with linked products listed as another row. This means that I would have multiple rows for one linked product.

All of my Easypopulate spreadsheets need updating to work with DBIO (as the category must be listed in just one column with the ^ delimiter). As DBIO only exports the master category I am unable to easily keep a record of linked categories.

On import the DBIO manager also requires multiple uploads to achieve the desired result – e.g. one import to add a linked category, then another import to set the master category back to what it was.

Would it be possible to import/export v_categories_name as a list of linked categories? I believe this has been mentioned by Torvista in post #361. This would make managing the master category and linked categories much easier.

Alternatively would it be possible to add an additional column such as v_categories_linked to allow linked categories to be clearly defined.

Any help with this would be much appreciated. We have found this add on to be one of the most useful mods available for Zen Cart. I would be happy to make a donation to help cover the costs of implementing these changes.

Many thanks
Richard
Richard, I like that v_categories_linked column idea. There's already an open issue (https://github.com/lat9/dbio/issues/135) requesting changes to the linked-categories' handling that I'll update to identify that implementation possibility.

I've just submitted v1.6.7 of DbIo for the Zen Cart moderators' review; I'll post back here when it's available for download.

This release contains changes associated with the following GitHub issues:

#189: ProductsHandler, select all sub-categories when a higher-level category is selected for export.
#190: Correct initial installation error for databases using a utf8mb4 collation.
#191: Correct PHP warning for product-addition and specified category is non-existant.
#192: Adding longtext as a possible text-type field.
#194: Updating language files to use HTML5 <br>'s
#195: Correctly disallow Products' import of duplicate models if disallowed via configuration.
#196: Disallow Products' import if an empty category/sub-category is specified.

lat9:

I've just submitted v1.6.7 of DbIo for the Zen Cart moderators' review; I'll post back here when it's available for download.

This release contains changes associated with the following GitHub issues:

#189: ProductsHandler, select all sub-categories when a higher-level category is selected for export.
#190: Correct initial installation error for databases using a utf8mb4 collation.
#191: Correct PHP warning for product-addition and specified category is non-existant.
#192: Adding longtext as a possible text-type field.
#194: Updating language files to use HTML5 <br>'s
#195: Correctly disallow Products' import of duplicate models if disallowed via configuration.
#196: Disallow Products' import if an empty category/sub-category is specified.
Now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2091

Forgive me if I missed it in this thread already, but I'm looking for a couple of "best practices" or ideas on how to best implement this plugin to achieve a more seamless inventory management strategy. I started building an excel spreadsheet with formulas to help me standardize item titles, make it a little more user-friendly visually, and ensure I'm consistent with product codes, model numbers, descriptions, etc. This works great for building the inventory from scratch, but where I'm struggling is how to maintain it once the site goes live. For example, if an item sells, presumably I will export the database with updated information, but then am stuck with how to best link it to my master spreadsheet with formulas, etc. so that everything updates without having to repopulate formulas and risk messing up the products. A large portion of my inventory consists of unique/single quantity items, so I'm constantly adding/removing products and I'm finding the I/O database is a little more cumbersome for extensive changes/additions. Any suggestions would be appreciated.

I'm currently using an older version of Dbio (which will be replaced with the newest version when I finished upgrading to 1.5.7). I use it to break down our transactions by state. At this time, I can only get a total sale amount for each transaction. I need it broken down by sale amount and shipping amount. Is this in the new version--sorry, I read through most of the readme doc, but I didn't see that specific element. If not in the new version, could I request that it be added?

HeleneWallis:

I'm currently using an older version of Dbio (which will be replaced with the newest version when I finished upgrading to 1.5.7). I use it to break down our transactions by state. At this time, I can only get a total sale amount for each transaction. I need it broken down by sale amount and shipping amount. Is this in the new version--sorry, I read through most of the readme doc, but I didn't see that specific element. If not in the new version, could I request that it be added?
Request noted: https://github.com/lat9/dbio/issues/197

lat9:

Request noted: https://github.com/lat9/dbio/issues/197

Thanks, that will help with states that impose sales tax on shipping as well as the base sale amount.