Solutions with PHP and MySQL ~ Strathmore Artist Papers database (demo)

During a job interview, a project was discussed that involved moving all of the hard-coded data of 21 pages from the Strathmore Artist website (strathmoreartist.com) to a MySQL database. A template would be needed to populate the data back into those pages. In addition, an administration area protected by login would be required so that Strathmore could manage their own data.

When I got home from the interview, I was excited about the project that was mentioned and immediately began working on a prototype. I focused on a single category, Drawing Papers, created the functions (shown below), and had a working demo in 3 days, including the beginnings of the Adminstration area. When I went back for the 2nd interview, we discussed my demo. I didn't get the web design position, but I was hired to complete the Strathmore database project.

The Drawing Papers category had multiple combinations of series (e.g. Windpower Drawing) and formats (e.g. pads, rolls) with 44 rows of data to insert into a MySQL database (5 rows for a single format are shown above).

Because I was only working with a single category from a single product group, and because the group and category would not change in this demo, only 2 tables were created for the data. Another table (not shown) was also created for users who would be logging in to the Administration area.

The combinations of series and formats were considered: some had data and an image, some were without an image. Looking at the format items, there were consistently 4 columns, but the column titles varied and needed to be included in the format data.

The main function, create_items_table, was designed to handle both viewing and editing requirements from any page needing format items data. It could be called from a page that displays the product's information for the consumer or from a page designed to modify the data in the format_items table from the Administration area.

The following PHP functions can either display the data as text in tables or populate a form's textboxes for editing depending on the value of $mode. Here is the function call:

create_items_table($group,$category,$series,$format,$mode);

The create_items_table function connects to MySQL, gets the series data if the package exists, gets the items data and then calls either write_itemstable_empty, if the combination is non-existent, or write_itemstable_data. Their function calls looks like this:

write_itemstable_empty($message);
write_itemstable_data($col_titles,$format_image,$items_result,$asterick_note,$mode);

The function write_itemstable_data has all the data and knows how to present it. Below is one product series and format combination shown in both view and edit modes from the Administer Items page: