Email / Newsletter / Email Address Exporter 155 issue

[chibipaw]

Obligatory entries:
ZC version 155f
DB: MariaDB 10.2.11
PHP: 7.1.17

I was using the Email Address Exporter, which makes use of the query of the newsletter/email users selection.
This is one of the rare times which I've used the Dormant Users (>3 Months) (Subscribers) query instead of the other selections to generate a list. Doing so with this option returns the error: "WARNING: An Error occurred, please refresh the page and try again."

Looking into the logs I've found something about an Empty Query:

[19-Jul-2018 12:43:05 America/Los_Angeles] Request URI: /shopping/admin/email_export.php?action=save, IP address: 127.0.0.1
#1 mysqli_query() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:45]
#2 queryFactory->query() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:261]
#3 queryFactory->Execute() called at [/home/shopping/admin/email_export.php:89]

[19-Jul-2018 12:43:05 America/Los_Angeles] PHP Warning: mysqli_query(): Empty query in /home/shopping/includes/classes/db/mysql/query_factory.php on line 45
[19-Jul-2018 12:43:05 America/Los_Angeles] Request URI: /shopping/admin/email_export.php?action=save, IP address: 127.0.0.1
#1 trigger_error() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/home/shopping/admin/email_export.php:89]

[19-Jul-2018 12:43:05 America/Los_Angeles] PHP Fatal error: 0: :: ==> (as called by) /home/shopping/admin/email_export.php on line 89 <== in
/home/shopping/includes/classes/db/mysql/query_factory.php on line 171

Since the exporter uses entries from the query_builder table, this is whats under the query string of query_id 3, for dormant users:

select max(o.date_purchased) as date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address, c.customers_lastname, c.customers_firstname HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC


It appear all the other export/sections works, except this one.
On a side note that is semi related to this list generated. Are there any codes / plugins that can make use of a generated like like this to purge off old users?

Any help would be greatly appreciated.

[carlwhat]

#1 - are you sure you have any dormant users?
#2 - the mysql query you provided works fine for me. i see no problems with it.

this error may be just saying you have no dormant users. unless you are sure you have some, in which case we can dig further.

best.

[chibipaw]

#1 - are you sure you have any dormant users?
#2 - the mysql query you provided works fine for me. i see no problems with it.

this error may be just saying you have no dormant users. unless you are sure you have some, in which case we can dig further.

best.

Pretty sure Ive got to have a few. Beside, don't the number on the right side of every select also tell you how many
entries would be called by the query? According to my set up, I would expect 2664 entries to be exported.
Here's a screen shot!
Thanks for replying.

[carlwhat]

why does it say BROKEN??

i’ve never used this plugin. perhaps ill download and look at it tomorrow. but it seems to already know something we do not.

[chibipaw]

why does it say BROKEN??

i’ve never used this plugin. perhaps ill download and look at it tomorrow. but it seems to already know something we do not.

Since we have multiple people using the system back end all the time.
I put the "Broken" tab into the entry description mostly for shop staffers to know the the option , well it's broken. :)

[carlwhat]

Since we have multiple people using the system back end all the time.
I put the "Broken" tab into the entry description mostly for shop staffers to know the the option , well it's broken. :)

@chibipaw

as it seems you already know how to edit the query builder description, remove the greater than sign as in:

>

from "...(> 3 months)".

then it should work.

best.

[chibipaw]

@chibipaw

as it seems you already know how to edit the query builder description, remove the greater than sign as in:

>

from "...(> 3 months)".

then it should work.

best.

Whoa..... That actually worked.

I thought everything in that field is purely cosmetic, and wouldn't have anything to do with the parsing.
Now Im thinking to myself, is this a feature or a bug? Hard to tell.

Thank you very much!

[carlwhat]

Whoa..... That actually worked.

I thought everything in that field is purely cosmetic, and wouldn't have anything to do with the parsing.
Now Im thinking to myself, is this a feature or a bug? Hard to tell.

Thank you very much!

really? shocked that we resolved this? please....

as to feature or bug, i would go with bug. my educated guess (actually i'm pretty sure) is that the sanitizer cleansed the $_POST['audience_selected'] variable so that there was no match against the query_builder table. potential solutions would include not cleansing that variable, or to use the query_id as opposed to the query_name as the option value for the audience_selected. you can see the code here using my query_builder table:

<select rel="dropdown" name="audience_selected">
<option value="" selected="selected">Please Select</option>
<option value="All Newsletter Subscribers">All Newsletter Subscribers (2871)</option>
<option value="Dormant Customers (>3months) (Subscribers)">Dormant Customers (&gt;3months) (Subscribers) (2520)</option>
<option value="Active customers in past 3 months (Subscribers)">Active customers in past 3 months (Subscribers) (0)</option>
<option value="Active customers in past 3 months (Regardless of subscription status)">Active customers in past 3 months (Regardless of subscription status) (0)</option>
<option value="Administrator">Administrator (1)</option>
<option value="Customers who have never completed a purchase">Customers who have never completed a purchase (478)</option>
<option value="Permanent Account Holders Only">Permanent Account Holders Only (4015)</option>
</select>

if the option value used the query_id, we would not have this problem. else you want to be very careful about what you use as your query_name.

glad that we resolved it.

best.

[twitchtoo]

Dormant Newsletter Subscribers - Zen Cart 1.5x Bug with fix.


This error...
PHP Warning: mysqli_query(): Empty query in /home/public_html/shop/includes/classes/db/mysql/query_factory.php on line 45
Request URI: /shop/YOUR_ADMIN/newsletters.php?page=1&nID=114&action=confirm...


Is the result of this being installed by the stock Zen Installer - mysql_zencart.sql like this:


INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '3', 'email,newsletters', 'Dormant Customers (>3months) (Subscribers)', 'Subscribers who HAVE purchased something, but have NOT purchased for at least three months.', 'select max(o.date_purchased) as date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address, c.customers_lastname, c.customers_firstname HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC', '');


AND mysql_upgrade_zencart_155.sql


UPDATE query_builder set query_string = 'select max(o.date_purchased) as date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address, c.customers_lastname, c.customers_firstname HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC' where query_name='Dormant Customers (>3months) (Subscribers)';


As mentioned in this thread - confirmed to be causing problems in Zen Cart 1.55f, 1.56c and 1.57 so far.


The Problem:
Dormant Customers (>3months) (Subscribers)

gets hung up on the > (greater than character)


The Fix:
Edit your query_builder - query_name to read:

Dormant Customers (Greater than 3 months) (Subscribers)