Using php command line to update database in the background

What’s up! It’s been a week and I wasn’t able to blog about anything at all. That’s why I’ll try to  make up by writing a post which I think is very useful.

In this tutorial, I’m going to show you how you can use php’s command-line capabilities to manipulate mysql database in the background.
This is useful in instances where you need to execute sql queries automatically. That is without user intervention.

Requirements

  • PHP – you can also install Wampserver or XAMPP if you’re on Windows.

 

Procedure

First, you’ll have to setup your machine to use php. Luckily, I have already written about it before. So please check out the link below and then come back here once you know how to do it.

How to setup environment variables on Windows

Once you’ve read that, I’m sure you’ll already know what to do with the following address:

C:wampbinphpphp5.3.5

Yes, that’s the address which you need to include in the path for the environment variables so that your machine will be able to execute php commands. However, you might want to modify the address above if you’re using a different version of php. Or if you have installed  Wamp in a different location.

Next, you need to open a text-editor then create a new php file. Save it into whichever location you want. Its not necessary that you save it on a web accessible folder since were going to execute it in the command-line and not in the browser.

If you’re using a framework which handles the database manipulation for you. You can also include it like you do in a normal php file accessed in the web. And as you can see, we still wrap the code with php tags.

<?php

require_once('db_config.php');

?>

Ok, so I’m going to leak a few of the codes that I might probably use in the current project that I’m working with since I’m not really in the mood to think up of examples.

First, I setup two variables. The rates for surcharge and interest:

$surcharge_rate = .24;
$interest_rate = .10;

Then I selected the records which doesn’t have equal amount and amountpaid. To explain, the amount is the total amount that needs to be paid. And the amount paid, is the amount that has already been paid.

Then we loop through all the records which matches our query. And updated them one by one.

if(!empty($penalize)){
	foreach($penalize as $p){
		$bp_ledgerID = $p->bpledgerBillID;
		$not_paid = $p->dblAmount - $p->dblAmountPaid;
		$surcharge = round($not_paid * $surcharge_rate, 2);
		$interest = round($not_paid * $interest_rate ,2);
		
		$updater = $db->query("UPDATE sys_bpledgerbill SET dblSurcharge='$surcharge', dblInterest='$interest' WHERE bpledgerBillID='$bp_ledgerID'");
	?>
	
		<?php echo $bp_ledgerID; ?>
		<?php echo $surcharge; ?>
		<?php echo $interest; ?>
	
	<?php
	}
}
?>

As you can see from the code above, we didn’t actually use html tags when we outputted the values. You probably already know the reason for this. But I’ll repeat it once again. That is because were executing all of this in the command-line, the command-line isn’t a browser which can understand html tags that’s why we must try not to use them. Although, I actually got a nice tabular data when I executed the script. I don’t know the reason for this but it certainly is awesome, considering the fact that we don’t have html which acts as a data container.

image

To execute php scripts in the command-line, all you need is to specify the path where the script is stored:

cd c:wampcl_scripts

Then once you get there, type ‘php’ followed by the filename of the script:

php surcharge_updater.php

As simple as that. But where’s the automated part? Well, that’s another story which I have already written before:

How to automate mysql database backup

The same procedure can be used to execute you php script automatically. All you need to add is a batch file which will call the php script:

cd c:wampcl_scripts
php surcharge_updater.php

Then just select that batch file from the Windows Task Scheduler and configure it to your liking.

 

Conclusion

You’ve just learned how to execute php scripts from the command-line, how to create a batch file which calls a php script, and how to use the Windows Task Scheduler to execute the batch file automatically. If you have any questions, feel free to ask in the comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s