» Data Architecture

For a new client project I was provided with database of users consisting of names, email addresses and managers.

The name column contained both first and last names formatted last name first like:

MySQL’s http://dev.mysql.com/doc/refman/5.0/en/string-functions.html” rel=”external”>TRIM() functions allow us to make quick work of this data manipulation. Using the query below and a new table similar to the first except with separate fields for first and last name we can copy the data to the new table and split the names in the process.

Popularity: 14%

Share This

Need a quick count of repeated occurences of matching rows for statistical analysis?

Popularity: 20%

Share This

MySQL’s RAND() function can be used to set random values or fill a table with random data.

The following SQL snippet will change column1 in all rows to a random number 0-50.

Popularity: 26%

Share This

DHL EasyShip allows you to easily automate shipping processes. EasyShip allows a user to weigh a package, supply destination information and print a pre-paid package slip. The EasyShip tools communicate with DHL's servers over the Internet to rate and purchase the service and can notify them a local pickup is ready. The system can also communicate with a third party database via ODBC to pull the destination address and can writeback simple fields such as a tracking number.

In order to integrate EasyShip into a ZenCart store, I created a table with the following schema:

In EasyShip we created a writeback operation and mapped the package tracking number to the ship_package_tracking field. For every package shipped through this system now, EasyShip INSERTs a new row containing the ZenCart orders_id and DHL's tracking number for the shipment.

Using my PHP crontab system, I created an hourly job to query the orders_shipping table for new shipments. The automated job, updates the order status to SHIPPED, inserts a new comment for the order containing the tracking information and link to DHL's site and generates an email to the customer with the information.

I would recommend a similar system for anyone running a medium to high volume shop. It has proven to be a huge timesaver for the clients I have implemented it for.

Related Link: DHL services from Parcel2Go

Popularity: 35%

Share This

One of the major weaknesses of an open source package like ZenCart is the lack of support for third party applications. Sure you can insert products by hand and process orders, but what if you already have products in another form such a Point of Sale system or inventory management system? What about integrating the sales process into an existing accounting package like QuickBooks?

EZ Populate is a ZenCart contribution that can solve the product importation problem for some uses. I have used this module successfully with a few clients, but for the majority, it lacks support for much outside the basic realm of importing products in a specific CSV format.

What about converting from another application like osCommerce to ZenCart?

Sites like Froogle and Shop.com and most affiliate programs have specific formats they expect data to be submitted in.

What can be done in situations like these?

• Have a custom import or export script created by a professional developer
• Write your own
• Contract a database developer to run a one-off import or export

If you are skilled in PHP, rolling your own import/export shouldn’t be prohibitively complex.

To export products, first query the products and products description tables to get the base product record. Queries suitable for copy/pasting from the original ZenCart source for this use are readily available just check out the product detail view template. Once the product record is available, product options and attributes can be easily sub-queried using the products_id. Now that all of the product data is available, it should simply be a matter of printing out the data rows in the order and format expected by the import application and iterating over each product and product option row.

Exporting orders is a similar excercise in that the basic order information is queried, then the products, product options and any secondary order information (such as payment records and comments) are queried. Then the result sets are iterated over printing out the data in the required format.

One-offs are a viable option only if the data manipulation needs performed only once or rarely (such as year end reports) and the steps are difficult to automate accurately.

Popularity: 24%

Share This