Tag Archive for 'multiple mysql updates'

Easy PHP Multiple MySql Update: Template Method to the Rescue

Published on November 19, 2013 by Bill Sanders in MySql Operation and Template Method. 0 Comments Tags: , , .

oopgirlThink Object Solutions

The other day I was working on an app to automatically update a site to a mobile device on a daily basis. The prototype uses a MySql table with three fields and only seven records, one for each day of the week. Each week, using an HTML UI, the administrator adds new headers, graphic urls and write-ups for each day of the week. These are stored in the MySql database, and the graphics are placed into an “images” folder.

For each day of the week, I needed to update three fields in the MySql table. In looking at the MySql docs I found that updates are carried out one field at a time even though I found some workarounds, none seemed too reliable, and the docs suggested as much. So I’d have to make three separate operations. That should be easy enough—a single update class with methods for each of the three fields I needed to update. Before we get started, you can see the update process and download the code using the two buttons below:
PlayDownload

One Class to Handle Multiple Updates

What makes SQL updates easy is that the SQL statements usually only involve a single field and table. So one way to look at multiple updates is to take three simple statements and provide each one its own method. There is a sequence to follow; so why not put them into a Template Method? (See previous posts on the Template Method design pattern in PHP for more details on its purpose. An overview can be found in the Design Pattern Table).

To start off, we’ll see what the skeleton of the operations look like:

  1. update header
  2. update picture url
  3. update write-up

Next, we’ll create an abstract class to reflect that order but not providing any content. Also, we’ll add properties likely to be needed:

?View Code PHP 
< ?php
abstract class IUpdate
{
    //Update methods
    abstract protected function doHeader();
    abstract protected function doPix();
    abstract protected function doWriteup();
 
    //Update Properties
    protected $pixhead;
    protected $pix;
    protected $writeup;
    protected $day;
 
    //MySql Properties
    protected $tableMaster;
    protected $sql;
    protected $hookup;
 
    //Template method
    protected function updateAll()
    {
        $this->doHeader();
        $this->doPix();
        $this->doWriteup();
    }
}
?>

With the template method in place (updateAll()), build a class to implement the abstract methods. The data for the methods is contained in the HTML variables passed from the UI; and we’ll need a day-day-of-the week object to plant the date in the correct location. First, look at the Update class and then the HTML UI that passed on the data:(Click the link to see the rest of the post and how the template method is implemented.) Continue reading ‘Easy PHP Multiple MySql Update: Template Method to the Rescue’