Email Address Exporter (slight problem)

[DrByte]

Okay, the upgrade added one record to the query_builder table.
If you delete that (newest) record (usually query_id = 7 ), does anything change?

[nrg77]

Okay, the upgrade added one record to the query_builder table.
If you delete that (newest) record (usually query_id = 7 ), does anything change?

Yes, that did the trick!!

Now for the inevitable question. How can I fix it this?

Thanks..

[DrByte]

Now try putting it back again:[FONT="Courier New"]

INSERT INTO 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');

[/FONT]

[nrg77]

Now try putting it back again:[FONT=Courier New]

INSERT INTO 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');

[/FONT]

After putting it back, the problem came back....However please note the ID skipped 7 and went to 8 with the SQL string you pasted. So its 1 to 6 and then 8.

[nrg77]

After playing with it a little more, I realized that if I edit the SQL syntax to say something other than "NULL" at the line "o.date_purchased", it works. For example, now I have it like this...

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 > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC

The above will work...so my conclusion now is it has something to do with date purchased being zero or null.....Getting so close.....!!

[DrByte]

"IS NULL" is important to that query, because it specifically selects which customers have no orders.

For now, you'll have to skip that entry.

[nrg77]

I guess I have no choice. But being paranoid, I am not sure the table for customers will hold up in 1.38 in regards to welcome emails/order emails being sent out. I am praying nothing goes wrong when I put it live...

[DrByte]

But being paranoid, I am not sure the table for customers will hold up in 1.38 in regards to welcome emails/order emails being sent out.

Why ?

[nrg77]

Maybe just afraid of unexplainable problems....

A thought just popped up in my head thinking maybe I can empty out the customers table and find a way to input all 3500 customers again without importing from SQL (tried that, didn't work) strings.

[DrByte]

What DB_PREFIX are you using in your configure.php file?