Getting down and dirty

Before we continue with reading and writing from standard streams, I want to give you an example as to how you can integrate a PHP shell script with other applications to form a useful tool.

Use these SQL commands to create some data for us to query from a script:

CREATE TABLE staff (Name VARCHAR(255), Age INT, Job VARCHAR(255), Pay INT);
INSERT INTO staff VALUES ('Joseph Smith', 29, 'Security Man', 29000);
INSERT INTO staff VALUES ('Harold Barnes', 32, 'Security Man', 29000);
INSERT INTO staff VALUES ('Carmen Hobbes', 30, 'Security Man', 30000);

Now, here's a PHP script that will demonstrate a little of the power of using PHP for shell scripting:

#!/usr/local/bin/php
<?php
    if ($argc < 3) {
        print "Usage: phpcli2.php [where clause] [match clause]\n\n";
        exit;
    }

    $whereclause = $argv[1];
    $matchclause = $argv[2];

    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");

    $result = mysqli_query($db, "SELECT Name, Age, Job, Pay FROM staff WHERE $whereclause = '$matchclause';");
    $nummatch = mysqli_num_rows($result);

    if (!$nummatch) {
        print "No rows matched!\n\n";
        exit;
    }

    print "Found $nummatch rows:\n\n";

    while ($row = mysqli_fetch_assoc($result)) {
        extract($row);
        $Pay = '£' . number_format($Pay);
        print " Name: $Name\n";
        print " Age: $Age\n";
        print " Job: $Job\n";
        print " Pay: $Pay\n\n";
    }
?>

Save that as phpcli2.php, and chmod it +x. You'll need to modify the mysqli_connect() line to provide valid credentials for your server.

The script might look a little complicated, but all it does is query the database and extract people with the data specified in the arguments provided.

To start with, we make sure that the user has provided sufficient arguments for the script by checking whether $argc is under a certain amount. We need at least three arguments provided (although everything above the third is ignored), and these are: the script name (argument 0, always passed), argument 1 (becomes assigned to $whereclause) and argument 2 (becomes assigned to $matchclause).

If you are using the staff data given above, you could run phpcli2.php like this:

Our SQL query is built up by using the argument 2 and argument 3 to match specific records, and we then iterate through these records and print out each individual result.

So, to pull out all records where a staff member is being paid £29,000, the script can be called like this:

./phpcli2.php Pay 29000

As the record set is limited, only two results are returned. Getting the feel of how this can be useful? Try combining the script with other tools - here's an example to get you started:

./phpcli2.php Pay 29000 | grep Name | sort

Here we take the same script and pipe it through grep so that it will filter for lines containing "Name", then finally pipe the result of grep through sort, so it will show the results in alphabetical order. The end result, as you will see when you run the script, is that the names of all people in the database earning £29,000 a year are outputted, sorted alphabetically.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.