Easy PHP Multiple MySql Update: Template Method to the Rescue

Think 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:

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:

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.)

ini_set("display_errors","1");
ERROR_REPORTING( E_ALL | E_STRICT );
include_once('UniversalConnect.php');
include_once('IUpdate.php');
 
class Update extends IUpdate
{ 
   public function __construct()
   {
        $this->pixhead=$_POST['pixhead'];
        $this->pix=$_POST['pix'];
        $this->writeup=$_POST['writeup'];
        $this->day=intval($_POST['day']);
 
        $this->tableMaster="dailyDeal";
        $this->hookup=UniversalConnect::doConnect();
 
        //Call updates with template method
        $this->updateAll();
 
        //Close once
        $this->hookup->close();
   }    
   protected function doHeader()
   {
        $this->sql ="UPDATE $this->tableMaster SET pixhead='$this->pixhead' WHERE id='$this->day";
        try
        {
            $result = $this->hookup->query($this->sql);
            echo "Header update complete.
";
        }
        catch(Exception $e)
        {
            echo "Here's what went wrong: " . $e->getMessage();
        } 
   }
 
   protected function doPix()
   {
        $this->sql ="UPDATE $this->tableMaster SET pix='$this->pix' WHERE id='$this->day'";
        try
        {
            $result = $this->hookup->query($this->sql);
            echo "Pix URL setting update complete.
";
        }
        catch(Exception $e)
        {
            echo "Here's what went wrong: " . $e->getMessage();
        } 
   }
 
   protected function doWriteup()
   {
        $this->sql ="UPDATE $this->tableMaster SET writeup='$this->writeup' WHERE id='$this->day'";
        try
        {
            $result = $this->hookup->query($this->sql);
            echo "Write-up update complete.
";
        }
        catch(Exception $e)
        {
            echo "Here's what went wrong: " . $e->getMessage();
        } 
   } 
}
 
$worker=new Update();
?>

An important feature to remember about the template method (updateAll()) is that while it is generated in an abstract class, it is an implementation; not an abstract method. The three methods it launches are abstract, but the method itself is not. The implementation of the methods can be just about anything, but they will be fired in the order prescribed by the abstract class (IUpdate) and called through the template method (updateAll()).

The HTML UI is pretty straight forward as you can see in the code:

 

Data Update for Daily Deal

  Enter Day:

  
   Monday
  

  
   Tuesday
  

  
   Wednesday
  

  
   Thursday
  

  
   Friday
  

  
   Saturday
  

  
   Sunday
  

  Update Header Name:

  
  
  

  Update Graphic URL:

  
  
  

  Enter Product information up to 140 characters:

  
  
  

  

By using an iFrame, you can keep the output from PHP in the HTML document to check to see if all of the updates have been executed. I know that with HTML5, frames in general became personas non grata, but the iFrame element is still important and useful. You can even use iFrames with jQuery mobile.

The Unplanned Post

This post was not planned, but it seemed to be a perfect opportunity to illustrate how “thinking OOP” can be helpful in solving a recurrent (albeit relatively simple) problem in PHP and MySql:

How to execute multiple MySql updates in a single call.

It’s not so much how MySql limits what you can to update records, but instead how using object oriented PHP with design patterns can quickly solve a problem. Broken down you have:

• Only one field update allowed at a time
• Classes can encapsulate operations
• Create a class (an object) with an operation for each field needing an update
• Order the sequence with a Template Method solution

Try to keep in mind that OOP and Design Patterns are practical tools for busy developers. In that way, you’ll see them as partners; not cranky obstacles to problem solving.

Copyright © 2013 William Sanders. All Rights Reserved.