Script automation from the command line (part 2 of 2)

We’re back to finish part 2 of our command line PHP script to run our cron jobs.

We’ll start by creating a new file titled update_prices.php.  This filename should describe the overall goal you are intending to perform.

 

The first step is to include the database connector class, a methods class and our previously created cron_file.
Then we create our usageMessage which is called in our my_cron.php file (validateArgvArgumentsAndDieWithErrorMessageIfInvalid method) when there has been an input error as we need to give the users some guidance.

<?php
// Filename: update_prices.php
// The dirname() function has been used below because
require_once dirname(__FILE__) . '/class.DBCxn.php'; // Database connector file
require_once dirname(__FILE__) . '/class.integration.php'; // Our methods
require_once dirname(__FILE__) . '/class.my_cron.php'; // Our file created in step 1

// Global variables. 

$usageMessage = <<<STR
* Update or insert into the MySQL DB tables using data exported from a       *
* magical system from a far-away land.                                       *
*                                                                            *
* Usage: php update_prices.php [arguments]                                   *
*                                                                            *
*  [Arguments]                                                               *
*  -actual                                                                   *
*  Actually update the database.                                             *
*  -hypothetical                                                             *
*  Describe the updates that would be performed if -actual was used.         *
*  -verbose                                                                  *
*   Output detailed descriptions of each insert/delete/update action.        *
******************************************************************************

STR;

Once the include files are loaded, it’s time to start calling our validate arguments static method.
We know we will retrieve an array of 2 values back from this method, so we use PHP’s list function which will update the values of our variables.

We check if we’re to run in hypothetical mode and if so, we call the method from my_cron.php to inform the user that we will not be committing this data to the database.

We wrap everything in a try/catch so that we can get the error message. This could be updated to write the error message to a log file and email error to user.

// Globally executed code.
try {
  list($boolActuallyUpdateDatabase, $boolVerboseOutput) = (
    my_cron::validateArgvArgumentsAndDieWithErrorMessageIfInvalid($usageMessage)
  );
  my_cron::runInHypotheticalMode($boolActuallyUpdateDatabase);
} catch (Exception $e) {
  echo $e->getMessage(), "\n\n";
}

You put all your business logic in the class. integration.php file and reference the methods in your master file in this case update_prices.php.

We create an instance of our integration class so that we can access it’s methods.
Then we’re going to call one basic method; in this example and it will sit within the try/catch block above, at the bottom of the try section.

$products = new Integration();

$products->updatePrice($product_id, $prev_price, $upd_price, $boolVerboseOutput);

To finish it off, we have to create class.integration.php and add our updatePrice method to it. The updatePrice method takes four parameters.

The method checks if the new price is different to the previous price and if so will perform an SQL update. If the SQL updated successfully, we check if we need to print out debugging information.

class Integration {
    public $db;

    public function __construct() {
        $this->db = DBCxn::get();
    }

/**
   * Updates prices if required
   * @throws Exception
   * @param integer $product_id
   * @param float $prev_price
   * @param float $upd_price
   * @param boolean $verbose
   * @return string
   *
   */
  public function updatePrices($product_id, $prev_price, $upd_price, $verbose)
  {

    //Add specific business rules regarding price updates

    $sql = "[add sql update statement here]";

$result = $this->db->exec($sql);
    if (!$result) {
      $error = $this->db->errorInfo();
      throw new Exception('Adding updating prices: ' . $error[2]);
    } else {
      echo $verbose ? "\nUpdated Prices {$this->db->lastInsertId()} to to Product" : '';
      return $this->db->lastInsertId();
    }
  }
}

At the very bottom of our update_prices.php file still within the try section. We need to add another try/catch block.

We check whether hypothetical mode was requested and if so, we throw an exception which should take us to the outer catch section therefore rolling back the data.

If no exceptions, then we commit the database transactions and exit.

try {
    if (!$boolActuallyUpdateDatabase) {
      throw new exception("\nHypothetical Mode: No records committed");
    }
    $products->db->commit();
  } catch (Exception $e) {
    echo $e->getMessage(), "\n";
    $products->db->rollBack();
    echo "Error: Database has been rolled back.\n";
    die();
  }

So after all that there’s only one thing to do and that’s call the PHP script from the command line.

C:\Program Files\Zend\Apache2\htdocs\php update_prices.php actual verbose

Some Extras to help you get going:

<?php
// Filename: class.DBCxn.php
class DBCxn {
	// What DSN to connect to?
	public static $dsn = 'mysql:host=localhost;dbname=testdb'; // Change host and database name to suit
	public static $user = 'root'; // Change to your username
	public static $pass = 'root'; // Change to your password
	public static $driverOpts = null;

	// Internal variable to hold the connection
	private static $db;

	// No cloning or instantiating allowed
	final private function __construct() { }
	final private function __clone() { }

	public static function get() {
		// Connect if not already connected
		if (is_null(self::$db)) {
			self::$db = new PDO(self::$dsn, self::$user, self::$pass,
				self::$driverOpts);
		}
		// Return the connection
		return self::$db;
	}
}

That’s it for now, please comment if you have any questions.

Good luck