Collect Customer Input – Extracting the input by creating a View in the database

[icm]

This is less of an eStore Tweak than a database tweak, but it does solve a problem I was having. I just wanted to share a solution.

To do this, you will need to have access to your database via phpMyAdmin or something else where you can enter a query. I’m really new to working with MySQL, so it took a while to figure out what I needed to do.

My situation is that I wanted the purchaser of the product to be able to enter a username for another another person to give them access to another site. The other site can use external databases to authenticate users.

I used the “Collect Customer Input” field in order to collect the new username, but that data is not stored in a separate field, it is stored in the product name within parentheses (i.e. “Product Name(Collected Customer Input)”) in the wp_wp_eStore_customer_tbl table int he database. That would make for a long username.

In order to get around that I learned that I could make a “View” of the eStore Customer table, rename the fields and, for the product name, remove everything except what is inside the parenthesis.

Basically, you go to where you enter mySQL queries and enter something like: (without the “<” or “>” and the back tick mark where [backtick] is… and possibly around each field and table name)

Code:

CREATE ALGORITHM=UNDEFINED DEFINER=<databaseName>@ [backtick]%[backtick] SQL SECURITY DEFINER VIEW <newTableYouWantToCreate>
AS SELECT
wp_wp_eStore_customer_tbl.id AS id,
wp_wp_eStore_customer_tbl.email_address AS email_address,
wp_wp_eStore_customer_tbl.first_name AS first_name,
wp_wp_eStore_customer_tbl.last_name AS last_name,
wp_wp_eStore_customer_tbl.purchased_product_id AS course_id,
wp_wp_eStore_customer_tbl.txn_id AS password,
wp_wp_eStore_customer_tbl.date AS date,
substring_index(substr(wp_wp_eStore_customer_tbl.product_name,(locate(‘(‘,wp_wp_eStore_customer_tbl.product_name) + 1)),’)’,1) AS username,
wp_wp_eStore_customer_tbl.serial_number AS serial_number

FROM wp_wp_eStore_customer_tbl

This creates a new table in the database that just shows some of fields from the customer table, relabels some of them, and, in the case of the product_name field, removes the product name and the parentheses leaving only the data the customer put in. There are some limitations that may need to be addressed by limiting what the customer can enter, but that’s a case for another thread.

Oh, and by adding “where (wp_wp_eStore_customer_tbl.date between (now() – interval 30 day) and now());” at the end you can limit the number of records shown to those of purchases made within the last 30 days.

[admin]

Thank you for sharing this.

I just wanted to mention that there is an “Export to CSV” button in the manage customers menu. If you export the customer data, it will also output the custom variations and customer input in a different column in that exported CSV file.

[Author]

Posts