BACKGROUND DATA CRUNCHING

In this example, we will be working on a relatively large user database, and be exporting the contact details of the users’ database into a spreadsheet. As you can guess, this will take a pretty long time to crunch all the data.

So we will be creating a script that will run in the background instead, and send an email to the administrator when the export is complete. Let the administrator have some tea while the server does the weightlifting. This is sort of like when we upload a video to YouTube, and the video processing will happen in the background.

THE DATABASE SAMPLE

For you guys who want to follow up closely with this guide, here is the sample set that we will be using.

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users` (`user_id`, `user_email`, `user_name`) VALUES
(1, '[email protected]', 'Jane Doe'),
(2, '[email protected]', 'Joe Doe'),
(3, '[email protected]', 'John Doe'),
(4, '[email protected]', 'Julie Doe'),
(5, '[email protected]', 'Johan Doe'),
(6, '[email protected]', 'Joanne Doe'),
(7, '[email protected]', 'Juliet Doe'),
(8, '[email protected]', 'June Doe'),
(9, '[email protected]', 'Juan Doe'),
(10, '[email protected]', 'Jamir Doe'),
(11, '[email protected]', 'Jaden Doe'),
(12, '[email protected]', 'James Doe'),
(13, '[email protected]', 'Janus Doe'),
(14, '[email protected]', 'Jason Doe'),
(15, '[email protected]', 'Jay Doe'),
(16, '[email protected]', 'Jeff Doe'),
(17, '[email protected]', 'Jenn Doe'),
(18, '[email protected]', 'Joah Doe'),
(19, '[email protected]', 'Joyce Doe'),
(20, '[email protected]', 'Joy Doe'),
(21, '[email protected]', 'Juke Doe'),
(22, '[email protected]', 'Johnnie Doe'),
(23, '[email protected]', 'Jim Doe'),
(24, '[email protected]', 'Jess Doe'),
(25, '[email protected]', 'Jabril Doe');

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_name` (`user_name`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;
COMMIT;

THE CONFIG FILE

Let us start by creating a config file to hold all the settings and stuff. Do remember to change the database settings and file paths to your own.

THE EXPORT SCRIPT

Next, we create a script that will dig out the database entries and put them into a spreadsheet.

 PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
} catch (Exception $ex) {
  $pass = false;
  $result = "Error connecting to database. ".$ex->getMessage();
}
 
// FETCH ENTRIES & GENERATE CSV FILE
if ($pass) {
  $file = fopen(EXPORT_TO . "contacts.csv", "w");
  $stmt = $pdo->prepare("SELECT * FROM `users`");
  $stmt->execute();
  while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    fputcsv($file, [$row['user_id'], $row['user_email'], $row['user_name']]);
    // JUST TO SIMULATE WORKING WITH LARGE DATASETS
    sleep(1);
  }
  fclose($file);
}
 
// EMAIL THE RESULTS OR SOMETHING
$mailto = "[email protected]";
$mailsubject = $pass ? "Process OK" : "Process ERROR" ;
$mailtxt = $pass ? "Download link is at http://mysite.com/somewhere" : $error ;
//@mail($mailto, $mailsubject, $mailtxt);

This should be pretty straightforward and easy to understand. There are only a few things to take note here:

• It might be better to set a time limit, just to make sure that it will end even if it hangs.
• There is a small pause after writing each line to the spreadsheet, this is just to simulate a long-running script – Remove this in your production.

That’s it. But if you run this script “as-it-is”, it will become a “blocking script”, and we will need to create another script to run this one.

THE TRIGGER SCRIPT

 /dev/null &");
}
echo "RUNNING!";

Yep, that is actually all we need to run another script in the background –

• Windows is kind of a quack. We run the shell script with popen and immediately close the handler with pclose to allow the script to continue running in the background.
• For Unix-based OSX and Linux, we will be using exec, but append a “small extra” to the end of the command so that it runs “silently”.

A SMALL SEARCH TWEAK

First, let us tweak the export script a little bit. Just like our “usual” web-based scripts, we can append a WHERE clause to the SQL to include the search parameter – But instead of picking it up from $_POST or $_GET, we will be getting it from $argv.

 PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
} catch (Exception $ex) {
  $pass = false;
  $result = "Error connecting to database. ".$ex->getMessage();
}

// FETCH ENTRIES & GENERATE CSV FILE
if ($pass) {
  $file = fopen(EXPORT_PATH . "contacts.csv", "w");
 
  // WE PUT THE ARGUMENTS HERE FOR SEARCH
  $sql = "SELECT * FROM `users`";
  $cond = null;
  if ($argv[1]) {
    $sql .= " WHERE `user_name` LIKE ? OR `user_email` LIKE ?";
    $cond = ["%$argv[1]%", "%$argv[1]%"];
  }
  $stmt = $pdo->prepare($sql);
  $stmt->execute($cond);
  while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    fputcsv($file, [$row['user_id'], $row['user_email'], $row['user_name']]);
    // JUST TO SIMULATE WORKING WITH LARGE DATASETS
    sleep(1);
  }
  fclose($file);
}

// EMAIL THE RESULTS OR SOMETHING
$mailto = "[email protected]";
$mailsubject = $pass ? "Process OK" : "Process ERROR" ;
$mailtxt = $pass ? "Download link is at http://mysite.com/somewhere" : $error ;
//@mail($mailto, $mailsubject, $mailtxt);

PASSING IN THE ARGUMENTS

Next, we simply add the search term to the end of the command.

 /dev/null &");
}
echo "RUNNING!";

TASKS DATABASE TABLE

First, we will need to create another database table to contain all the running background tasks and the their process ID.

CREATE TABLE `tasks` (
  `user_id` int(11) NOT NULL,
  `process_id` varchar(12) NOT NULL,
  `task_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tasks`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `task_date` (`task_date`);
COMMIT;

With this, we can now track who/what is running and limit accordingly – If a user is already running a background task, we do not allow him/her to run another one. Or a system-wide limit of, let’s say, only 5 background processes can be run at any one time.

RUN SCRIPT

 PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
} catch (Exception $ex) {
  $pass = false;
  $result = "Error connecting to database. ".$ex->getMessage();
}

// CHECK IF USER ALREADY HAS RUNNING TASKS
if ($pass) {
  $stmt = $pdo->prepare("SELECT COUNT(*) `c` FROM `tasks` WHERE `user_id`=?");
  $stmt->execute([$USERID]);
  $count = 0;
  while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    $count = $row['c'];
  }
  if ($count>0) {
    $pass = false;
    $result = "Already have a background task running!";
  }
}

// RUN SCRIPT IN BACKGROUND
if ($pass) {
  // The second argument is the user ID and an option third one for search
  $cmd = 'wmic process call create "d:/xampp/php/php.exe -f d:/http/background/3c-export.php '. $USERID .'" | find "ProcessId"';
  $handle = popen("start /B ". $cmd, "r");
  $processID = preg_replace("/[^0-9]/", '', fread($handle, 200));
  pclose($handle);

  // Create task entry in database
  $stmt = $pdo->prepare("INSERT INTO `tasks` (`user_id`, `process_id`) VALUES (?,?)");
  $stmt->execute([$USERID, $processID]);
}

echo $result;

This is our “upgraded” trigger script.

• Before running the export script, it will do a check if the user already has another script running in the background.
• It will only continue if there are no other background tasks running for that particular user.
• A bit more command line yoga than usual here, basically creating a new task with wmic and getting the system process ID.
• We then store the process ID in the database as the script continues to run.
• Take extra note that we need to pass the user ID to the export script.

EXPORT SCRIPT

 PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
} catch (Exception $ex) {
  $pass = false;
  $result = "Error connecting to database. ".$ex->getMessage();
}

// FETCH ENTRIES & GENERATE CSV FILE
if ($pass) {
  $file = fopen(EXPORT_PATH . "contacts.csv", "w");

  // WE PUT THE ARGUMENTS HERE FOR SEARCH
  $sql = "SELECT * FROM `users`";
  $cond = null;
  if ($argv[2]) {
    $sql .= " WHERE `user_name` LIKE ? OR `user_email` LIKE ?";
    $cond = ["%$argv[2]%", "%$argv[2]%"];
  }
  $stmt = $pdo->prepare($sql);
  $stmt->execute($cond);
  while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    fputcsv($file, [$row['user_id'], $row['user_email'], $row['user_name']]);
    // JUST TO SIMULATE WORKING WITH LARGE DATASETS
    sleep(1);
  }
  fclose($file);
}

// EMAIL THE RESULTS OR SOMETHING
$mailto = "[email protected]";
$mailsubject = $pass ? "Process OK" : "Process ERROR" ;
$mailtxt = $pass ? "Download link is at http://mysite.com/somewhere" : $result ;
//@mail($mailto, $mailsubject, $mailtxt);

// TASK COMPLETE
if ($pass) {
  $stmt = $pdo->prepare("DELETE FROM `tasks` WHERE `user_id`=?");
  $stmt->execute([$argv[1]]);
}

The export script remains pretty much the same, except that we delete the task database entry when the job is done.

KILL TASK SCRIPT

 PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
} catch (Exception $ex) {
  $pass = false;
  $result = "Error connecting to database. ".$ex->getMessage();
}

// FETCH RUNNING TASK
$pid = 0;
if ($pass) {
  $stmt = $pdo->prepare("SELECT * FROM `tasks` WHERE `user_id`=?");
  $stmt->execute([$USERID]);
  while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    $pid = $row['process_id'];
  }
  if ($pid==0) {
    $pass = false;
    $result = "No process running!";
  }
}

// KILL TASK
if ($pass) {
  pclose(popen("taskkill /PID $pid /F", "r"));
  $stmt = $pdo->prepare("DELETE FROM `tasks` WHERE `user_id`=?");
  $stmt->execute([$USERID]);
}

echo $result;

So what happens if we want to cancel the task halfway? This script will do the rest of the cancellation magic – It simply gets the process ID from the database and runs a command line task kill.

ALTERNATIVES

Of course, the shell script is only one of the many ways to do parallel processing. There are many other packages that you can check out, and here are a few that I find to be pretty interesting:

• PHP Threads
• Pheanstalk
• Symfony Process
   

SOURCE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
 

The post 3 Steps to Run PHP Background Process appeared first on Code Boxx.