Databases

WordPress Database Integrations

I am currently researching the various ways the WordPress mySQL database can be accessed remotely.

One option would be to work with the WooCommerce REST API. Data Buzz produces a solution for FileMaker Pro to access orders placed online through WooCommerce  installed on a WordPress site.

Data Buzz Logo
fmEcommerce solution in FileMaker Pro

fmEcommerce Link (WooCommerce Edition) By Data Buzz

For less complex data collection, ODBC drivers can be configured in FileMaker Pro to access data stored in the WordPress tables by custom post type fields. Queries can be run in MySQLWorkbench to return the data in a presentable format as intermediary views. These views are saved to the database so that FileMaker can use them.

MySQL Workbench SQL query

SQL to return line items.

A long term goal of this research would be the potential to create bespoke web or desktop applications. Clients could use these to access and manipulate data generated by their websites; a way to collect data from a survey for example.

Inventory Database for SO Fine Art Editions

In 2013 I built an inventory database for a newly opened galley on South Anne St. in Dublin.

The Requirements

  • Record all consignments of work from artists (sale or return) with the appropriate documentation for this, printed or emailed to the artist
  • Record sales of work and issue either a sales receipt or an invoice.
  • Facilitate the payment for work purchased in instalments.
  • Calculate the VAT charged on the sales of work
  • Generate payments to artists for work sold reflecting the VAT and galley commission deducted. Documentation for this, printed or emailed.
  • Include non deductibles in payments to artists such as framing costs and bronze casting fees, not subject to gallery commission.
  • Record the return of unsold work to artists. Documentation for this, printed or emailed.
  • Generate sales reports and calculate VAT to be paid to Revenue.
  • Generate Credit Notes and log amounts in a Credit Control account for each customer where credit applies.
  • Generate return items where work is returned. Re-stocked works of art reflected in the stock table.
  • Issue gift vouchers and register them as sales.

 

So Fine Art Editions logo
So Fine Arts, South Anne's St., Dublin
Main Menu & Client/Artist Contact List

Main Menu & Client/Artist Contact List.

Layout for consignment docket and managemant of consinments to the gallery.

Layout for consignment docket and managemant of consinments to the gallery. 

Sales receipt and records of customer's sales.

Sales receipt and records of customer’s sales.

Web App Using PHP/HTML/CSS/JS With mySQL Database

As part of the Higher Diploma in computer science that I completed in 2014 at Carlow Institute of Technology I built an inventory system that was stored in a mySQL database, served through PHP server scripts to, presentation on screen with a web browser. This app required that users, logging in with secure passwords, would be locked out after three failed attempts.

Backend PHP and JavaScript files.

Backend PHP and JavaScript files.

Wire frames for web interface

I used a minimum wire-frame design for the web interface.

FileMaker Pro Integration

Additionally a FileMaker Pro file was created and configured with an ODBC driver. This permitted a desktop user to connect to mySQL on a remote server, an alternative way to access the inventory database.