Newsletter error TABLE_ORDERS doesn't exist

Hi

We have a cart (version 1.5.1, PHP Version: 5.4.34) which has an error when trying to send newsletters. The debug file shows this error

PHP Fatal error: 1146:Table 'user_zencart.TABLE_ORDERS' doesn't exist :: select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from zen_customers c,

TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING

max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC in /home/user/public_html/shop/includes/classes/db/mysql/query_factory.php on line 120

Not sure what to make of this one. The orders table does exist in the database. Could someone please point me in the right direction?

May seem like a step backwards, but there is some code that could be added to your store which will identify where that query is being run that is wrongly formatted. For some reason it is trying to find the actual text that is to be substituted by the table information.

Lat9 created it, the plugin has the name mydebug in it... I think... Basically it tells you which file sent the query.

Thank you mc12345678

I installed the module you mentioned. Below are the new debug files. I checked all files mentioned to be sure they are 1.5.1 original. So far I am stumped. This cart was upgraded from 1.3.7 in 2012. I'm waiting to hear back as to how long this problem has been going on and what possible work or changes had been done just prior.

[11-Dec-2014 21:49:55 America/Los_Angeles] #1 trigger_error() called at [/home/user/public_html/cart/includes/classes/db/mysql/query_factory.php:120]
#2 queryFactory->show_error() called at [/home/user/public_html/cart/includes/classes/db/mysql/query_factory.php:105]
#3 queryFactory->set_error() called at [/home/user/public_html/cart/includes/classes/db/mysql/query_factory.php:227]
#4 queryFactory->Execute() called at [/home/user/public_html/cart/includes/functions/audience.php:42]
#5 get_audiences_list() called at [/home/user/public_html/cart/user-admin/email_export.php:276]

[11-Dec-2014 21:49:55 America/Los_Angeles] PHP Fatal error: 1146:Table 'user_zencart.TABLE_ORDERS' doesn't exist :: select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from zen_customers c,

TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING

max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC in /home/user/public_html/cart/includes/classes/db/mysql/query_factory.php on line 120

[11-Dec-2014 21:49:38 America/Los_Angeles] #1 trigger_error() called at [/home/user/public_html/cart/includes/classes/db/mysql/query_factory.php:120]
#2 queryFactory->show_error() called at [/home/user/public_html/cart/includes/classes/db/mysql/query_factory.php:105]
#3 queryFactory->set_error() called at [/home/user/public_html/cart/includes/classes/db/mysql/query_factory.php:227]
#4 queryFactory->Execute() called at [/home/user/public_html/cart/includes/functions/audience.php:42]
#5 get_audiences_list() called at [/home/user/public_html/cart/user-admin/includes/modules/newsletters/newsletter.php:32]
#6 newsletter->choose_audience() called at [/home/user/public_html/cart/user-admin/newsletters.php:308]

[11-Dec-2014 21:49:38 America/Los_Angeles] PHP Fatal error: 1146:Table 'user_zencart.TABLE_ORDERS' doesn't exist :: select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from zen_customers c,

TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING

max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC in /home/user/public_html/cart/includes/classes/db/mysql/query_factory.php on line 120

In the future, code chunks like that should be posted inside of code tags that are created by selecting the hashtag/pound sign (#) above.

That said, check before line 42 of the following file:

cart/includes/functions/audience.php:42]

And look at the sql statement for errors or post here. It may be something that has carried over that was to be deleted or didn't get updated properly/fully.

This is the chunk of code. I've noted which line is #42. Thanks for taking some time with this.

 reset($queries_list);
  while (!$queries_list->EOF) {
    // if requested, show recordcounts at end of descriptions of each entry
    // This could slow things down considerably, so use sparingly !!!!
    if ($display_count=='true' || $display_count ==true ) {  // if it's literal 'true' or logical true
    $count_array = $db->Execute(parsed_query_string($queries_list->fields['query_string']) );  //---------- line 42
    $count = $count_array->RecordCount();
    }

buildingblocks:

This is the chunk of code. I've noted which line is #42. Thanks for taking some time with this.

reset($queries_list);
while (!$queries_list->EOF) {
// if requested, show recordcounts at end of descriptions of each entry
// This could slow things down considerably, so use sparingly !!!!
if ($display_count=='true' || $display_count ==true ) { // if it's literal 'true' or logical true
$count_array = $db->Execute(parsed_query_string($queries_list->fields['query_string']) ); //---------- line 42
$count = $count_array->RecordCount();
}

Based on that, need to work backwards to what is assigned to:

parsed_query_string($queries_list->fields['query_string'])

How are you with phpMyAdmin? Could you post the contents of the query_string field that's in your zen_query_builder table and associated with the Dormant Customers (>3months) (Subscribers)? Remember to post the information in a code-block (#).

lat9:

How are you with phpMyAdmin? Could you post the contents of the query_string field that's in your zen_query_builder table and associated with the Dormant Customers (>3months) (Subscribers)? Remember to post the information in a code-block (#).

Hi lat9

Thanks, Here it is

select o.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 HAVING 

max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC

There appear to be extra CRLF pairs in the data you posted, try replacing that database field with the following (see how it's all one line):

select o.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 HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC

lat9:

There appear to be extra CRLF pairs in the data you posted, try replacing that database field with the following (see how it's all one line):

select o.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 HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC

Thanks lat9 but it didn't make any difference. 

I did import the database into a fresh 1.5.1 zen cart and the same thing is happening so as far as I can tell the problem is not in the fileset on the live site. Not knowing what else to look for I did a repair on the whole database which also did not make any difference.

It can't hurt to try running the upgrade routine again on my test site so that's what I am trying next.

Ok, I think I have had success.

I tried to run the upgrade routine but at first was receiving a warning and wouldn't process. A debug file said

[12-Dec-2014 14:41:59 America/Chicago] PHP Fatal error:  1062:Duplicate entry 'Customers who have never completed a purchase' for key 'query_name' :: INSERT INTO zen_query_builder (query_category, query_name, query_description , query_string) VALUES ('email,newsletters', 'Customers who have never completed a purchase', 'For sending newsletter to all customers who registered but have never completed a purchase', 'SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN  TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL'); in /home2/user/public_html/mysite.com/truesol/includes/classes/db/mysql/query_factory.php on line 120

There wasn't a double entry so I just dropped the query_builder table. The database upgrade was then able to run. I let it upgrade starting from 1.3.7 to 1.3.8 and up to 1.5.1. It skipped a bunch of things but also ran a bunch of things. One thing it did not insert though was the query_builder table. So I went to another 1.5.1 database, exported a query_builder table and imported it into the database I am working with.

The email and newsletter functions are working again. Admin looks to be ok and the storefront is loading and looks ok.

Before I put the live site in maintenance mode and do this process one last time with a fresh database copy can you see any potential problems with what I have done?

So the query builder table I thought would have already been in the system design back in 1.3.7, so doing the upgrade (one time is all that is necessary to go from the older version to the current version), would not insert the table if it wasn't something newly added between that older version and the version currently being added. So dropping the table would not get it recreated. This goes back to the fact that there is/was an error in the content of the information within the table and that if that content issue was corrected then the upgrade would move quickly forward.

One issue with dropping the table is that obviously will lose any queries that may have been added independently of an installation. So would want to capture what those queries are before deleting the table. Secondly, though it may be of benefit to rename the table instead of deleting it entirely so that the data can be compared. (little suggestion offered by RodG in a recent post). If though the issue is to recreate a query and that query happens to be the same one that has been causing trouble, then really seems that something is not being performed correctly if the issue has persisted when trying to follow lat9's recommendation... It does seem like there is an extra "return" (CRLF) in the original content, so not sure how the replacement query was inserted, but it appears that it should be one continuous string, even though it will naturally wrap around to a new line in the text entry screen. The enter key should not be pressed as part of the entry in phpmyadmin.

renaming the table instead of dropping it is a good idea. When doing the upgrade I wasn't sure if the query_builder table would be re-inserted but I didn't have anything to lose by trying it.

When doing the upgrade I checked all the boxes beginning with 1.3.7 to 1.3.8 and up to 1.5.1 and let it run in one proccess. I think if the upgrade had been done correctly the first time then when I did the re-upgrade all of the processes would have been skipped but many of them were processed. So I don't know if it was just the just the query_builder table that was the problem.

Yes there are extra returns in the code I had posted. That's just how it appears in the original database.

When I added the code that lat9 provided I copied and pasted it into the query_string field (so it was all one line) and saved it. Would that have been an ok way to add it?

buildingblocks:

renaming the table instead of dropping it is a good idea. When doing the upgrade I wasn't sure if the query_builder table would be re-inserted but I didn't have anything to lose by trying it.

When doing the upgrade I checked all the boxes beginning with 1.3.7 to 1.3.8 and up to 1.5.1 and let it run in one proccess. I think if the upgrade had been done correctly the first time then when I did the re-upgrade all of the processes would have been skipped but many of them were processed. So I don't know if it was just the just the query_builder table that was the problem.

When I added the code that lat9 provided I copied and pasted it into the query_string field (so it was all one line) and saved it. Would that have been an ok way to add it?

To ensure that it was all "one line" I would typically also go to the end of any row, remove the extra space(s) and then reinsert at least one space allowing the "text editor" to do with it what it may. Generally speaking I would say that it is an "ok" way to add it/modify it... Again the intent here would be to replace the existing query string with the new one as there is/was a problem with the old one.

Okay, wasn't sure from the previous description if each upgrade was painstakingly performed one at a time or all at once... There may also be value to the messages that were indicated, unless they all said something like skipped because already present or nothing to do because doesn't exist or some "informative" result like that.

As for the earlier description of doing the upgrade. There is no need to place the configure.php file(s) as rewriteable per se when performing a database only upgrade. During a database only upgrade the configure.php files are not modified. In fact only the store's configure.php file needs to be accessible for reading which means that the admin directory can already be renamed to your secret admin directory name and not reverted to admin. Just a little helpful hint for the live upgrade. Not to mention when the admin directory is renamed to admin, a message appears on the store side indicating that if the store is not still in maintenance mode. The message does not appear on the store side as long as the admin directory is not named admin....

mc12345678:

To ensure that it was all "one line" I would typically also go to the end of any row, remove the extra space(s) and then reinsert at least one space allowing the "text editor" to do with it what it may. Generally speaking I would say that it is an "ok" way to add it/modify it... Again the intent here would be to replace the existing query string with the new one as there is/was a problem with the old one.

Okay, wasn't sure from the previous description if each upgrade was painstakingly performed one at a time or all at once... There may also be value to the messages that were indicated, unless they all said something like skipped because already present or nothing to do because doesn't exist or some "informative" result like that.

As for the earlier description of doing the upgrade. There is no need to place the configure.php file(s) as rewriteable per se when performing a database only upgrade. During a database only upgrade the configure.php files are not modified. In fact only the store's configure.php file needs to be accessible for reading which means that the admin directory can already be renamed to your secret admin directory name and not reverted to admin. Just a little helpful hint for the live upgrade. Not to mention when the admin directory is renamed to admin, a message appears on the store side indicating that if the store is not still in maintenance mode. The message does not appear on the store side as long as the admin directory is not named admin....

I know to watch for spaces when working in the files but it didn't occur to me when in phpmyadmin. Thanks for the tip.

I did forget I didn't need to rename admin and adjust permissions. I could have saved myself a few steps. It gets done out of habit.

Thanks very much for helping mc12345678 and lat9. It's really appreciated.

buildingblocks:

I did forget I didn't need to rename admin and adjust permissions. I could have saved myself a few steps. It gets done out of habit.

Same here, but all it takes is one time of going through those motions and then selecting one of the other install methods and if not taking ones time, the database is gone and has to be restored from the backup that was just made... Fortunately hasn't happened to me, but now that I know that the zc_install process of upgrading the database doesn't require the admin directory to be renamed, I don't want to become a statistic of how many have "inadvertently" deleted/corrupted their database. :)

I'm glad you got it sorted out, but I'm still trying to figure out how the extra line-breaks were inserted. I've got a client that's being updated from Zen Cart v1.3.5 and that query is a single, unbroken line.

lat9:

I'm glad you got it sorted out, but I'm still trying to figure out how the extra line-breaks were inserted. I've got a client that's being updated from Zen Cart v1.3.5 and that query is a single, unbroken line.

I wish I could answer that one for you but I didn't do the original upgrade so I really don't know.