203 replies to this topic

[kymation]

Reviews are good for your business; they help customers make buying decisions, and they help search engines find your site.
The trick is getting your customers to review the products that they purchased.

This contribution sends an email to every customer asking them to review the product(s) they bought.
It can be scheduled to run a fixed number of days after the order has been marked completed. The
email that is sent is based on a template, so it is easy to modify to suit your business.

Support/comment/requests in this thread. The contribution is here: http://www.oscommerce.com/community/contributions,5471

Regards
Jim

Edited by kymation, 17 October 2007 - 03:03 AM.

[candleman]

Just what I was looking for!

I'm running into a Mysql error when attempting to upload the request_review.sql into the database.  The error I'm receiving is

#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 'orders` ADD `reminder_sent` tinyint(1) NOT NULL default '0' AFTER `orders_status' at line 1

Mysql version is: 4.1.22-standard

Thanks in advance for any help.

[john2323]

candleman, on Oct 17 2007, 06:20 AM, said:

Just what I was looking for!

I'm running into a Mysql error when attempting to upload the request_review.sql into the database.  The error I'm receiving is

#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 'orders` ADD `reminder_sent` tinyint(1) NOT NULL default '0' AFTER `orders_status' at line 1

Mysql version is: 4.1.22-standard

Thanks in advance for any help.

Hi,

This is what I was looking for as well.  But I am getting the same error as you.

Any help would be great!
Jamie

[kymation]

candleman, on Oct 17 2007, 06:20 AM, said:

Just what I was looking for!

I'm running into a Mysql error when attempting to upload the request_review.sql into the database.  The error I'm receiving is

#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 'orders` ADD `reminder_sent` tinyint(1) NOT NULL default '0' AFTER `orders_status' at line 1

Mysql version is: 4.1.22-standard

Thanks in advance for any help.

Bah -- I'm going blind. Didn't even see that. The command should be:

ALTER TABLE `orders`  ADD  `reminder_sent` tinyint( 1  )  NOT  NULL default  '0' AFTER  `orders_status`;

Regards
Jim

Edited by kymation, 17 October 2007 - 05:49 PM.

[candleman]

That did the trick...thanks a bunch

[golfman2006]

Just installed this today and all went well with install. In testing, we are not getting the expected results. We just placed an order today, both with newsletter option selected and without, then updated the last_modified column to 10/01/07 and 'orders_status' = '3' via MySQL 5, as is mentioned in install notes. In the configuration, Days Delay For Review email is set to '10' and Review Email Limit set to '1'. When we test the code by using this url (with our domainname) http://www.sample.com/automatic/review_mail.php, the correct message is displayed. The admin is notified via the store email on file, however it indicates '0' emails sent. Our test customer in return gets no email and in looking at the Orders table, the column 'reminder_sent' remains '0'.

Not sure where to look here, any ideas? We do have Register Globals turned off on our server if that could be the cause as has been in past contributions.

[golfman2006]

golfman2006, on Oct 17 2007, 12:01 PM, said:

Just installed this today and all went well with install. In testing, we are not getting the expected results. We just placed an order today, both with newsletter option selected and without, then updated the last_modified column to 10/01/07 and 'orders_status' = '3' via MySQL 5, as is mentioned in install notes. In the configuration, Days Delay For Review email is set to '10' and Review Email Limit set to '1'. When we test the code by using this url (with our domainname) http://www.sample.com/automatic/review_mail.php, the correct message is displayed. The admin is notified via the store email on file, however it indicates '0' emails sent. Our test customer in return gets no email and in looking at the Orders table, the column 'reminder_sent' remains '0'.

Not sure where to look here, any ideas? We do have Register Globals turned off on our server if that could be the cause as has been in past contributions.

Disregard this for now. We were able to get the emails. Maybe there was quite a server delay running the script. Anyhow, will keep you posted if anything else is found. Thanks again and Great contribution!

[kymation]

golfman2006, on Oct 17 2007, 12:01 PM, said:

Just installed this today and all went well with install. In testing, we are not getting the expected results. We just placed an order today, both with newsletter option selected and without, then updated the last_modified column to 10/01/07 and 'orders_status' = '3' via MySQL 5, as is mentioned in install notes. In the configuration, Days Delay For Review email is set to '10' and Review Email Limit set to '1'. When we test the code by using this url (with our domainname) http://www.sample.com/automatic/review_mail.php, the correct message is displayed. The admin is notified via the store email on file, however it indicates '0' emails sent. Our test customer in return gets no email and in looking at the Orders table, the column 'reminder_sent' remains '0'.

Not sure where to look here, any ideas? We do have Register Globals turned off on our server if that could be the cause as has been in past contributions.

This script doesn't use any global variables directly, so it won't have any problems with Register Globals being off. It does use some of the standard osCommerce functions, mostly database and email, and those may use globals. You should have seen error messages if this was a problem, so I doubt it was.

Was your last updated date 2007-10-01 00:00:00 (in that exact format?) The script may have misread the date if the format was different. Also, check that   customers_newsletter in the customers database table is set to 1 for this customer, or set 'Review email limit' in the Admin to 0.

That's all I can think of right now. Please post here if this works or not.

Regards
Jim

[john2323]

Thanks Jim!

Seems to work great now.  I just have to figure out how to setup a cron! haha.  At least I can do it manually everyday until then.

Thanks,
Jamie

[kymation]

I'm not much good at setting CRON jobs myself, so I decided not to try to explain it in the instructions. However, if you are using shared hosting, your control panel may give you an easy way to set it up. It's definitely worth looking for.

Regards
Jim

[john2323]

kymation, on Oct 17 2007, 03:12 PM, said:

I'm not much good at setting CRON jobs myself, so I decided not to try to explain it in the instructions. However, if you are using shared hosting, your control panel may give you an easy way to set it up. It's definitely worth looking for.

Regards
Jim

I didn't have a chance to look in the control panel yet but I email my host and they set it up for me!  I will take a look in the control panel to see if it is possible to do it myself as there are a few other things I would like automated as well.  Anyway thanks for the great contribution I sent out some review emails and have two people add reviews already from them!

Thanks again,
Jamie

[Alexian]

Okey step for step. We have a dedicated server with our own control panel. So i setup the cron job for every day, every hour, every minute.
There is none mails going out.

When i execute the php file i get this:

The www.samplesite.com review reminder script finished execution on 
19 Oct 2007 13:19:51

 email messages were sent.

So i guess thats okey. As next i registered a account with Newsletter. And in the admin the newsletter was 1 and the delay is 10.
Oh,.. i also NOT used the orignal SQL tables. but the second updated:

INSERT INTO `configuration` VALUES (NULL, 'Days delay for review email', 'REVIEW_REMINDER_DELAY', '10', 'Number of days to wait before sending a review reminder email', 1, 998, now(), now(), NULL, NULL);
INSERT INTO `configuration` VALUES (NULL, 'Review email limit', 'REVIEW_REMINDER_NEWSLETTER', '1', 'Send review reminder email only to customers who accept newsletters (set to 1)', 1, 999, now(), now(), NULL, NULL);
ALTER TABLE `orders` ADD `reminder_sent` TINYINT(1) NOT NULL default '0' AFTER `orders_status`;

I don't know if there is any difference between them. As an customer i don't get any mails. Anyone any idea what i'm doing wrong ?

[kymation]

Alexian, on Oct 19 2007, 04:18 AM, said:

Okey step for step. We have a dedicated server with our own control panel. So i setup the cron job for every day, every hour, every minute.
There is none mails going out.

When i execute the php file i get this:

The www.samplesite.com review reminder script finished execution on 
19 Oct 2007 13:19:51

 email messages were sent.

So i guess thats okey. As next i registered a account with Newsletter. And in the admin the newsletter was 1 and the delay is 10.
Oh,.. i also NOT used the orignal SQL tables. but the second updated:

INSERT INTO `configuration` VALUES (NULL, 'Days delay for review email', 'REVIEW_REMINDER_DELAY', '10', 'Number of days to wait before sending a review reminder email', 1, 998, now(), now(), NULL, NULL);
INSERT INTO `configuration` VALUES (NULL, 'Review email limit', 'REVIEW_REMINDER_NEWSLETTER', '1', 'Send review reminder email only to customers who accept newsletters (set to 1)', 1, 999, now(), now(), NULL, NULL);
ALTER TABLE `orders` ADD `reminder_sent` TINYINT(1) NOT NULL default '0' AFTER `orders_status`;

I don't know if there is any difference between them. As an customer i don't get any mails. Anyone any idea what i'm doing wrong ?

You state that you set up a test account. Did you place an order from that account? Did you alter the record of that order in the orders table to have a last_modified date at least 11 days in the past? Was the orders_status of that order set to 3?

Regards
Jim

[Alexian]

kymation, on Oct 19 2007, 05:34 PM, said:

You state that you set up a test account. Did you place an order from that account? Did you alter the record of that order in the orders table to have a last_modified date at least 11 days in the past? Was the orders_status of that order set to 3?

Regards
Jim

Okey, i didnt do any of it. I now checkt the DB i use DB 4.2. Anyhow In de table "orders" the value was 5 and i changed it to 3. Is this right?

And as second one...the cel "last_modified" . I dont get it. Where do i set the date? what do i have to change?

and now in admin. i put it to 10 and 1 for the newsletter on. is this okey ?

[kymation]

Alexian, on Oct 19 2007, 04:53 PM, said:

Okey, i didnt do any of it. I now checkt the DB i use DB 4.2. Anyhow In de table "orders" the value was 5 and i changed it to 3. Is this right?

And as second one...the cel "last_modified" . I dont get it. Where do i set the date? what do i have to change?

and now in admin. i put it to 10 and 1 for the newsletter on. is this okey ?

5 is never a valid number for orders_status, so I'm assuming you are in the wrong place. You need to be looking at the orders table in your database. Look for the columns that I mentioned above, and make the changes in the column values for your test order.

If you don't want to mess with the database, set the status of your order in the Admin to "completed," then set the delay for the "Days delay for review email" to 1, then wait a couple of days.

Regards
Jim

[Alexian]

kymation, on Oct 20 2007, 02:07 AM, said:

5 is never a valid number for orders_status, so I'm assuming you are in the wrong place. You need to be looking at the orders table in your database. Look for the columns that I mentioned above, and make the changes in the column values for your test order.

If you don't want to mess with the database, set the status of your order in the Admin to "completed," then set the delay for the "Days delay for review email" to 1, then wait a couple of days.

Regards
Jim

I set in the admin,.. the status to "completed". In the mean time check this:
I got 2 value options.

[img]http://www.projectorenwinkel.nl/images2/1.gif[/img]
[img]http://www.projectorenwinkel.nl/images2/2.gif[/img]

I'm a real rookie in DB so, go easy on me

[Alexian]

kymation, on Oct 17 2007, 09:34 PM, said:

Was your last updated date 2007-10-01 00:00:00 (in that exact format?) The script may have misread the date if the format was different. Also, check that   customers_newsletter in the customers database table is set to 1 for this customer, or set 'Review email limit' in the Admin to 0.

And where do i place a date? I never saw it before.

[candleman]

Alexian, on Oct 19 2007, 07:53 PM, said:

Okey, i didnt do any of it. I now checkt the DB i use DB 4.2. Anyhow In de table "orders" the value was 5 and i changed it to 3. Is this right?

And as second one...the cel "last_modified" . I dont get it. Where do i set the date? what do i have to change?

and now in admin. i put it to 10 and 1 for the newsletter on. is this okey ?

If you have the default orders status then order status 3 is "Delivered" in the admin panel under Localization>Orders Status.  You need to make sure you have placed a test order and changed the status of the test order to Delivered.

If you don't want to wait for 10 days for testing purposes, change the "Days Delay for Review Mail" in the configuration to "1".

*note if you use a different order status (other than delivered) like I do, then you need to change the "3" in the following line in review_mail.php

where o.orders_status = '3'

to whatever number correspondes to the Order Status you use.  In my case I use "Shipped" which has a number of "23" in my database.

Good Luck.

[kymation]

Alexian, on Oct 20 2007, 02:31 AM, said:

I set in the admin,.. the status to "completed". In the mean time check this:
I got 2 value options.

<snip>

I'm a real rookie in DB so, go easy on me

You're looking at the format of the database. That 5 is the length of the field -- the number of characters allowed in that column. You need to be looking at the contents of that table. Click on the "browse" tab at the top, then find the row that corresponds to your test order. Click on the Edit icon in that row and edit the fields in that form.

Regards
Jim

[Richard Bailey]

This is just what I have been looking for, for years!!!

Thank you!!

When I run:

CODE
UPDATE `orders` SET `reminder_sent` = `1`


I get:

CODE
SQL query:

UPDATE `orders` SET `reminder_sent` = `1`

MySQL said: Documentation
#1054 - Unknown column '1' in 'field list'


Any ideas?

Rich