Email Efficiency Tips from ReplyTool

I want to offer an ebook to my customer and it requires unique username and password to access it. I already have a list of unique combinations (username and password) generated from my ebook software and it is stored in a .csv file on my website. After order is processed successfully by any order processing system, I want to trigger a process.
Here is the total process:
– After the order is processed successfully, it will capture that customer's data (like name, email, etc) from the order processing form or it can use any other method to capture customer's data.
– It will retrieve the first unused combination of unique username and password from the .csv file.
– It will deliver the retrieved information (unique username and password) to that customer by generating a mail or a autoresponder.
– The whole process works automatically so that once it is up it will work again and again without my intervention for each of my new customers.

How do I code the total process?
I’ll not allow the customers to edit or delete the record or change the field information (user name) and (password), which is kept in .csv file – The reason is - it is a list of say 1000 unique combinations of username and password and that have already generated by a third party ebook software. The unique username and password is required by a unique user to access this ebook. Anyone or I are not permitted to change it. I just need to read the latest unused record from the file and send it by a mail to a new customer, who purchases my ebook.

For better performance, MySQL database can be used.

A cgi script made with perl can solve my purpose.

The following reply is by : commorancy :
Opening and managing a .CSV file (flat file) from your application will be a lot more of a problem than using a database. Especially if you want to allow your customers to change/modify the password information. And, it makes it far easier to add, update and delete rows/columns in the table (like adding and deleting customer records or a password change). I would suggest you pick a database of some kind (like MySQL, MSSQL or even Access in a pinch) and create a table out of that CSV file.

Also, as your CSV file grows, the performance to fetch data will suffer. This doesn't degrade nearly as much when using a database. With a database, it's easier (and faster) to fetch a row containing your customer's login/password information. Then, you can use ODBC calls to fetch the data.

Even more than this, you can create relationships between tables to allow for linking your login table to customer data table, billing tables, etc. So, if your customer goes delinquent in payment, you can easily lock the login through an accounting system (with the right table columns and coding).

Since I don't know what programming environment you intend to use, I really can't easily give you code examples. But, I would definitely start by moving from the CSV file to a database to allow for growth and easier management.

— Update 1:40 AM

I would still use the database system because you're going to use the old ones up and have to add new ones (making the database grow). I would add a column called 'used_flag' or similar and mark it with a 1 after that one has been used and a 0 if unused. I might even add a 'used_by' column to mark which user received that username/password combination so you have a history in that table.

So you would do something like this:

SELECT record_number, password, username FROM
ebook_table WHERE used_flag = 0 LIMIT 1;

This will give you the first row that contains an unused username/password combo and let you store the record_number of that combo.

Then…

UPDATE ebook_table SET used_by = 'user@somehost.com',used_flag = 1 WHERE record_number = 'XXXXXX';

Where XXXXXX is the number you stored from the earlier SELECT.

This update marks the password/login combo as used and who used it. You can even add additional information if you like like date, time, order numbers, etc with additional columns.

You'll want to do these two SQL statements in pretty quick succession to prevent racing issues (two web accesses at the same exact moment in time) both getting the same record. There are other considerations as well. A database can help prevent locking concerns. For example, a CSV file might be locked (at the file level) preventing other requests from happening at the same moment in time. MySQL, Access and MSSQL can be configured to lock at the row level to allow other users access to the table while one user has a specific row locked.

All things you'll want to be concerned with. With CGI, I would suggest PHP because it has PHP-MySQL interfaces that allow easy access to getting data from databases. I can send you example code to do this if you need it. But, I believe when you install PHP (either in Apache or IIS), the MySQL PHP portions come with very good examples.

You can email me if you need more detail.