PHP as a scripting Language

Many Developers use PHP in terms of frameworks like Laravel, CodeIgniter or Zend. There is nothing wrong with this in fact it is the best way to create modern web applications but is it all that PHP can do?

Querying databases and rendering a web page is a great outcome but it is not the only task that Developers and System Administrators have to do. Sometimes we have to play around with big data sets in terms of migration or conversion. If we expose web servers like apache to these tasks then we soon run into limits such as max memory and timeouts.

Nothing sucks more that running a big job and having the server die and you cannot work out where the job got up to (Hint: If you use MySQL transactions then when it dropped the activity wasn’t committed so you are back to square one).

PHP On The Command Line

PHP works fine on the command line provided the initial script and all its children can locate all of the components that it needs.

The example file:

<?php

// trust me on the new line as you will be watching 
// the output scroll up the screen

echo "Hello World\n";

?>

To run the file:

$ php /path/to/file.php

The output:

$ Hello World

Example PHP Command Line Application

The code comments will explain what this application does. At a high level we are trying to extract a large chunk of RTF data and convert it to plain text and then insert it into a table. This example works on the basis that one vendor thought it was a great idea to store notes as RTF and the other uses plain text.

<?php
$servername = "localhost";
$username = "user";
$password = "pass";

try {

    // Connect to the DB
    $conn = new PDO("mysql:host=$servername;dbname=mydb", 
                    $username, 
                    $password);

    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Using unbuffered because we don't want to run out of memory
    $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

    // At least let yourself know that you connected!
    echo "Connected successfully\n\n";


    // This query only get the RTF data and since we are 
    // running batches we want to get the newest data first
    $sql = "SELECT `id`, `rtf_data`
    FROM `notes_imported`
    WHERE `rtf_data` LIKE '{%rtf%'
    ORDER BY `created_date` DESC
    LIMIT 2500";

    // No need for a prepared statement as we hand wrote the SQL
    $stmp = $conn->query($sql);
    $stmp->execute();

    // Cache the results because we want to reuse the connection
    // This is why we never use SELECT * FROM... because we want 
    // to work with the smallest dataset possible
    $results = $stmp->fetchAll(PDO::FETCH_ASSOC);

    // loop through the results
    foreach ($results as $row) {

        // Some feedback so we know that the script is still running
        echo $row['id'] . "\n";

        // Save the RTF file to disk
        file_put_contents($row['id'] . '.rtf', $row['rtf_data']);

        // Convert the file to text 1.rtf > 1.txt
        shell_exec('unoconv -f txt ' . $row['id'] . '.rtf');

        // get the text version to add back to the database
        $tmp = file_get_contents($row['id'] . '.txt');

        // Build our SQL and update the new table
        $sql = "UPDATE `notes` SET `note` = :note WHERE id = :id";
        $stmp = $conn->prepare($sql);
        $stmp->execute(array(
            ":note" => $tmp,
            ":id"   => $row['id']
        ));

        // NOTE: at this point you could unlink 
        // both files to save space

    }

} catch(PDOException $e) {

    // If there is and error show it
    echo "Connection failed: " . $e->getMessage();
}

?>

So there you have it. A power scripting language you can use for cron jobs, migrations, conversions and much more. Sometimes it is easier to use a language you are very familiar with to accomplish tasks you might other wise have to learn bash and a variety of Linux commands to achieve the same outcome.