How to store and retrieve special characters on mysql database

In this quick tutorial I’m going to show you how to save special characters in a mysql database. Which we will later on try to retrieve.

First, setup a testing folder then create a new php file. Also include your database configuration file on the new file that you created.

Next, copy the following code into your file. Make sure to change ‘specialchars.php’ to the name of the file that you are using:

<form action="specialchars.php" method="post">
 <input type="text" name="specar">
 <input type="submit">

The text field with the name of ‘specar’ is the text field where we are going to paste the special character.


Then the code which will process, save, and retrieve the special character:

$id = htmlentities($_POST['x']);
$unique_key = substr(md5(time()), 0, 5);
$create = $db->query("INSERT INTO sys_users SET UserID='$unique_key', strPassword='$id'");

$selector = $db->get_var("SELECT strPassword FROM sys_users WHERE UserID='$unique_key'");
echo html_entity_decode($selector);

Yup, the table that I’m using is not in any way related to the data that were trying to store. But don’t mind that. You’re here to learn something on saving and retrieving special characters.

So what we did in the code is to first check whether we have any post data to process. So our script only executes if something is actually submitted to the server. Then we used a function called htmlentities. What it does is to convert a special character into an html entity. An html entity is basically a representation of a special character in terms of html.


Why convert?
We need to convert a special character into an html entity because you might run into problems later on if you store these kinds of characters as it is. I haven’t dug so much as to what problems might occur. Querying those values later on might produce some unexpected errors. Like an error in your sql query.

And yes, without using htmlentities. It works fine:

$id = $_POST['x'];
$unique_key = substr(md5(time()), 0, 5);
$create = $db->query("INSERT INTO sys_users SET UserID='$unique_key', strPassword='$id'");

$selector = $db->get_var("SELECT strPassword FROM sys_users WHERE UserID='$unique_key'");
echo $selector;


After saving the htmlentity to the database, we then retrieve it using the unique key that we assigned earlier. The unique key is pretty much very unique in the sense that it uses the unix timestamp as the primary source of data, then it encrypted it using the md5() function. Then we reduce the whole string into just 5 characters by using the substr() function.

To decode the html entity that was saved on the database, we used a function called html_entity_decode. And it does pretty much what its name says it does. It only requires a single argument which is the actual html entity to be decoded.


That’s pretty much what I have for this tutorial. To add a bit of security, you might want to include this function. And just call it whenever you feel like a character that you’re trying to save might ruin your query.

function clean($str){
    return trim(mysql_real_escape_string($str));

How to use phpmyadmin to secure mysql database

Its been a while since I’ve wrote a full fledged blog. I should really be writing 2 to 3 articles in order to make up. But making sure to make it a quality post. Since my blog has really been stagnating. I only get a minimum of 93 views each day and a maximum of 383 views each day. Haven’t got any higher amount of views.

So enough with the little blabber. Let’s get to the main point of this article. This time I’m going to show you how you can use phpmyadmin to configure your mysql database to have more security.

I know that most of us, especially the beginners often use the root account when developing web applications. Its actually fine to use the root account when in a development environment. But once you deploy the application, it would be wise to create another account that has privileges which are only applicable to the actions that the users are going to perform.

Go ahead and launch Wampserver and access phpmyadmin on your browser. Yes, its totally fine to use IE here but I don’t really recommend it. As you can see from the screenshot below, the root user has actually all the privileges. Pretty much every feature, functionality that is embedded in the mysql database can be performed and used by this user. If you examine the screenshot further, you will notice that the configuration is not in any way secure. Passwords hasn’t been setup for most of the accounts, only the root account accessing from the localhost has a password.


Creating a new user

To address the issue, we have to create a new user. And limit its privileges.
The first section is the login information. Here we define the username, password, and the host. The host is pretty much the location of the user or the access point. It might be local, any, or specified host. Localhost means, the user is accessing the database from the computer where it is installed.



Assigning Database

Next, is the database for the user. You can pretty much ditch this one and just select none. But doesn’t mean that if you select none, the user won’t actually have any database to play with.



Setting up privileges

Now, for the privileges. Most of the options are self-explanatory. But we’ll go ahead and try the most obvious ones. Under the data group box. Just check the select checkbox and then click go.



Database configuration file

And then go ahead and write a database configuration file:


$db = new ezSQL_mysql('reader', '1234', 'orgbpls', 'localhost');

I’m using a php library called ezsql for manipulating databases. Just make use of the usual way of how you connect to mysql database if you don’t  use the same library as I am.


Selector file

Next, create a testing folder, then create a new php file called selector.php. Which will contain the following code:


$select = $db->get_results("SELECT * FROM sys_users");

<table border="1">
	<?php foreach($select as $s){ ?>
		<td><?php echo $s->UserID; ?></td>
		<td><?php echo $s->strPassword; ?></td>
	<?php } ?>
<?php } ?>

In the above code, we just did a normal select query. Which yields us the following output:


Yup the hashed passwords are pretty much the same. Since I used 1234 on all of them. But I’m telling you not to use that dumb password once you’re going to deploy your application. Better yet generate a strong random password if you can’t think of anything secure.

As you can see, the query executed without problems. That’s because we checked the select action a while ago.

Updater file

This time, create another file called updater.php. And yes, were going to perform an update query using this file.

$db->query("UPDATE sys_users SET strPassword='1234' WHERE UserID='badmin'");

Go ahead, and access that php file in your browser. And see what happens:


It issues an error saying that update command is denied to the user. I can’t think of anything that  an attacker could do if this is the case. Since the user that’s been set to manipulate the database doesn’t have enough privileges to perform the action.


Other settings

There are more settings that you can check out and play around with. One of those is the resource limits. Which lets you setup the maximum number of queries that can be performed on an hourly basis, and other interesting stuff.


There’s also the structure settings. Most of the time, you can uncheck these guys if the users are just going to perform CRUD operations on the database. These are pretty much admin stuff.




You have learned how to setup user privileges using phpmyadmin. Making applications secure is a must for developers. Even if you think there might be no hackers that might try to hack your system. Its still important to protect the system that you have created.

Web Development Resources September 17, 2011

Time for another roundup. I don’t really track what links I’m posting here so a few things might get repeated. Especially now that this is the third time that I’m gonna be doing this. So here’s a bunch of links that you might want to check out:


As the name implies it makes form creation easier.

Form validation hints
A tutorial showing you how to create nice looking validation hints.
Quick lookup for php, mysql, js, and css stuff. Yeah pretty much like a search-engine.
A web-application for checking if your site conforms with design basics.

Use google+ to improve your UI
If you want to use the same UI Elements that are used in Google+. Then you can try this one. They also have some of the UI elements inspired by OS X Lion.

Awesome Javascript library for formatting date and time.

JS Bin
If you want to experiment on some Javascript code, this is the way to go. You can also include some of the most popular Javascript Libraries. Like Mootools, Jquery, Prototype, and YUI.

Yet another Jquery form validation plugin.

Jquery Easing
A jquery plugin for performing animations.

Jquery Animations
A beginner tutorial showing you how to perform animations in jquery.

Php js
Php functions ported to Javascript.






How to save and retrieve images using

Lately I have only been just talking about web development stuff. Php, jquery, and html5 to be exact.
Only this time that I’m going to write something on again.
What were going to do this time is to create an simple application that can save and retrieve images from a specific folder.



  • Visual Studio
  • Wampserver



First we have to create the table that were going to work with. Its up to you to build the database where you’re going to place this table.

  `item_name` varchar(150) NOT NULL,
  `filename` varchar(200) NOT NULL

Next, we build the interface that will be used by the user to upload images.


The image explains it all. You just have to drag these things to the canvas:

  • Label – description
  • Textbox – container for the original file path
  • Button – saves the filename to the database
  • Open File Dialog Box – for browsing the images to be uploaded.

Before we proceed, I think it would be better to explain the scenario:
First the user clicks on the textbox, which then opens up the open file dialog box. The user browses for an image then selects it. The textbox will be filled with the file path for that selected image. After the user has decided that this will be the image, he then clicks on the save button to transfer the selected image to a predefined location.

Moving on.

First, declare the global variables that will be used throughout this simple application

Dim MoveLocation As String
Dim TransferPath = "C:images"
Dim imgAdres As String
Dim fileName

Double click on the textbox then change the event to ‘click’. The following code goes inside of that click event.


 imgAdres = OpenFileDialog1.FileName
 fileName = System.IO.Path.GetFileName(imgAdres)
TextBox1.Text = imgAdres

Here’s a breakdown of the code from the first line to the 4th:

  1. Calls the Open File Dialog Box. This box allows you to browse for files in any available location in your computer.
  2. Assign the full path of the image to a variable called imgAddress. The full path of the image will look something like this: c:wampimagesabc.gif
  3. Assign the image file name to a variable called fileName. This time we used a system method called GetFileName. Which requires one argument and that is the file path. Calling this method yields a filename like: abc.gif
  4. As a visual representation, we just assigned the file path to the value of the textbox.


Next, double click on the save button and use the following codes:

 MoveLocation = TransferPath + fileName

        If System.IO.File.Exists(imgAdres) = True Then
            If System.IO.File.Exists(MoveLocation) = False Then
                System.IO.File.Move(imgAdres, MoveLocation)
                AutosizeImage(MoveLocation, PictureBox1)

            End If
        End If

Here’s a breakdown:

  1. we put the transfer path and the filename together.
  2. check if the file really exist in the address that we have specified. We stored that address in the variable imgAdres that’s why we used it as the argument.
  3. check if the file already exist in the folder where we are going to move it.
  4. If it does not already exist then it won’t be move, if it does not already exist, then we move it.
  5. Finally, we called the AutoSizeImage function. Which you can find in the link below. What it does is to resize an image to fit in a picturebox. The function only needs 2 arguments, the actual location of the image, and the picturebox where you want to display the image.

The final output would look something like this:



You have just learned how to transfer a file to a specific folder in your system. And how to use the open file dialog box to browse for files.

Yes this tutorial is incomplete because I didn’t show you how to save and retrieve the image location using the table that we created earlier. And that is why I’m going to leave that as a homework for you.

23 must have applications

This time I guess I’ll take a break from the usual programming-related article. In this article I’m going to share to you some of the applications that I use on my Windows machine. Yes I’m a Windows user, it’s the Operating System that I grew up with.
And here are the apps that I currently use:

Windows Live Writer

The one and only application that I’m using since I’ve put up this blog. It makes my life easier. Especially when I need to use images, links, tables and other kinds of media. So if you’re a blogger like me, I recommend that you use Live Writer. It supports most of the blogging platforms that you might be familiar with.




The only thing that I could rely on when I can’t remember the specs of the computer that I’m working with. Yes, it gives you a quick overview of what powers up your machine. And that includes the motherboard, cpu, ram, and some of the devices that are installed on your computer.




The all-time favorite browser. Words can really describe how awesome this browser is, so I’ll end this short.




Currently the main-browser that I’m using for browsing the web. Because I believe that its faster and easier to use.




Can play almost all of the media file that you can think of without you having to install a single video codec on your machine. Another bonus is that its cross-platform, so you can still reap its awesomeness whether you are a Linux, Windows, or a Mac guy.




Another kick-ass tool that’s oath to replace the ever-slow Windows default search tool. Just put this tool on your startup folder, give it a shortcut. So that you can summon it anytime you need it. Yes, you’ll only have to wait for a maximum of .5 seconds for the file that you wish to find to appear right in front of your face.




The one stop shop for your OS virtualization needs. VirtualBox allows you to virtualize any OS that you can think of as long as you have the installation disc or and existing image of an OS that has already been installed before. And don’t forget the hardware, because basically you cannot virtualize Mac OS on a mere PC. Except if you have the right PC hardware that allows you to run a Hackintosh.




For syncing files across multiple computers, Dropbox will do the job better than anything else.




Makes setting up a development server easier and faster by bringing together all the applications needed for running up a website. Yes, if you still don’t know it. Wamp stands for Windows, Apache, MySql, Php. Installing these applications individually would actually be a pain in the neck unless you’re a computer geek at heart.



Snipping Tool

The one and only tool that I use for taking screenshots when I make articles for my blog. Though I really had to use printScreen to take this cute little screenshot of Snipping Tool. Its really a very useful tool for bloggers like me who often take screenshots. Oh and by the way, its included in Windows 7 so you don’t have to download and install it separately.





Yet another awesome tool from Piriform. Yes it’s the same company that made Speccy, Recuva, and Defraggler. You might say that I’m a Piriform fanatic because I used almost all of their software.




If I feel like my system is getting slow and I feel like the hard disk is at fault. I make use of this tool to defragment my hard disk.




If you wish to sync all your notes. Everything and anything that you can think of, but not actually wanting to put it on a blog. Then you might also want to use Evernote. This is also a dropbox like software/service, but instead of syncing files. It allows you to sync notes. Its really a good replacement for the default Windows Sticky Notes.




I’ve really come this far in using this app. I guess I’m also one of the people who first used it when it launch. By the way, this app allows you to run any application or even the Windows Explorer inside a Sandbox. This makes the operation more secured since it adds up a layer between the OS and the application. So if the Sandbox gets infected with virus, you can simply delete it without it having to affect the whole OS.




One of the malware removal tools that I trust. I’m not really sure how effective it is in detecting and removing malware. But people from HowToGeek and other reputable tech sites always recommend it all the time. So I guess I have just gone with the flow. But I think this actually live up to its said effectiveness.




For my torrent downloading needs, I use Utorrent. One of the things that I like about it is that its super small installation size. Its actually just 625Kb in my case. Another thing is its simple interface. And lastly, the speed by which it downloads torrents is not that bad.




When I need to download something big, and can only be downloaded through direct-download. I use Free Download Manager.



Programmer’s Notepad

As the name suggests. This is a text-editor. I believe only a few people are using text-editors considering that there already exist a more kick-ass counterpart which is the IDE. But I think text-editors can still get the job done.



Foxit Reader

The pdf-reader that I came to love. I was using Sumatra before I discovered this awesome pdf-reader. Sumatra was lightweight but it can’t actually render pdf files as fast as Foxit, that’s why I chose Foxit. Its faster and it matches my current theme.




The built-in Windows Calculator is good enough for me when I need to perform quick calculations. Or I want to test if the calculations of the system that I’m trying to make is correct.



Freemake Video Converter

For my media-conversion needs I use FVC. Aside from the fact that its free, and its interface is awesome. It also produces high-quality videos in no time. It also allows you to choose the file output size which makes it even more awesome. You can convert a whole bunch of video files from one format to another as you can see from the sceenshot below.




Yeah, I’m really an interface junkie. Once I like the interface of a certain app I test it and see if the face matches the functionality. Oftentimes eye candy apps such as this one passes my test. Peazip supports a whole bunch of archive file types. Ranging from 7z, RAR, ZIP, and many others.



MS Word

Nothing can really replace the suite of productivity apps from Microsoft. And that includes this baby called MS Word.




I guess that’s it for this article. You’ve just known some of the applications that I currently use on my day to day life as a student. Next time I’m going to share to you some of the browser extensions that I’m using.

How to create an ajax powered cart system

The title might sound familiar. Its because there are already lots of tutorials on this topic before. Here are some of them:

Developing an ajax driven shopping cart with PHP and Prototype

Build an ajax powered shopping cart

Shopping cart php jquery


You might want to check out one of those before coming here. I recommend that you check the one from nettutsplus, its an in-depth tutorial on making an ajax powered cart.


  • Wampserver
  • Jquery core



Okay, seems like you’re still here after having me linking to some good content. So let’s not waste time and let’s get started. But before that, here’s a sample output. The list of products:


And the interface where the user can edit the quantity for each product. Any of the added products can also be removed and the grand total will automatically be updated.


First you’ll have to prepare your database and the directory that you’re going to work with. If you want to use the table that I’m using, you can go ahead and execute this query on phpmyadmin or the mysql console. Just make sure that you have already created a database before doing so. Because the query below is just use for creating table and putting some sample data.

  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`product_id`)

-- Dumping data for table `products`

INSERT INTO `products` (`product_id`, `product_name`, `quantity`, `price`) VALUES
(1, 'guava', 300, 200),
(2, 'melon', 200, 30),
(3, 'apple', 10, 2),
(4, 'papaya', 300, 700);

Next, create a new php file. This will be the one that were going to use as the main file for user interactions. In short this is where we will put most of the html.
As usual you need to include your database configuration file and the jquery  file. You can also put it on the same file if you like.

<?php require_once('pos_config.php'); ?>


<script src="jq.js"></script>

Next, perform a query that will fetch all the products from the table. Yes, this tutorial is pretty much in its simplest form. As much as possible I don’t want to add more functionalities. Just the core functionalities of a cart system is enough.

$fetch = $db->get_results("SELECT * FROM products");

<div id="prodlist">
<table border="1">
	<th>Add to Cart</th>
<?php foreach($fetch as $k=>$f){?>
<tr id="<?php echo $k; ?>">
	<td><?php echo $f->product_name; ?></td>
	<td><?php echo $f->price; ?></td>
	<td><img src="add.png" class="addtocart" data-prod="<?php echo $f->product_name; ?>" data-price="<?php echo $f->price;?>" id="<?php echo $f->product_id; ?>"></img></td>
<?php } ?>
<input type="button" id="btn_confirm" value="Confirm Selection">

Yup that’s easy enough if you’re already doing CRUD since you were an infant. But if that didn’t make sense to you then here’s what it does:

First we created a div that will contain the table of products that was fetched from the database. I put it inside a div because were going to hide that div later on once the selection of products to be both has been confirmed by the user. Then we just loop through the contents of the variable $fetch which contains an array of product names and prices.
Then in the add to cart button we added 3 attributes which is primarily used for storing data:

  • id – contains the unique product id, this will be used as a key.
  • data-prod – contains the product name
  • data-price – contains the price of the product

Lastly we have a button with the id attribute of btn_confirm. Which we will use to hide the product list once the list of products to be added to cart is confirmed by the user.

Next, add another div. This is where we will put all of the products that the user adds to the cart.

<div id="added">
   <!--products added to cart goes here-->

Next, go to the head section of your file and do the jquery housekeeping procedure:

    //magic happens here

Next, the action that we need to perform once the user clicks on any of the add to cart buttons.

	var prod_id = $(this).attr('id');
	var price = $(this).attr('data-price');
	var prod = $(this).attr('data-prod');
	$('#added').load('session_creator.php', {'prodid' : prod_id, 'price' : price, 'prod' : prod});

I recommend that you go back to the code where we declared those attributes. What were doing in the above code is just fetching the value in those attributes. If you’re updated with the latest web development news. You know that this is not how we ought to do things in the latest version of jquery. What we do is this:


Pretty much lighter on the fingers isn’t it? But I’m using a lower version of jquery when I was coding this so bear with me. You might as well download the latest version of jquery and reap all the benefits.

Okay so enough with that side dish. (I can’t think up of anything, bear with me). Then on the last second to the last line, we use a jquery load() function. Its basically used for loading up contents from another file in the server. The good thing is that we can also pass in arguments which will then be processed by the file that we specified.

So where is the result loaded? In the div that we created earlier. Scroll up if you’re uncertain.

So where is the session_creator.php file? Good question! I guess we will have to build it first before we continue with the rest of this tutorial.

The session_creator.php file, as the name implies is used to create a session where we will temporarily store the products added to the cart. We can pretty much achieve this using javascript cookies or maybe html 5 web storage. But I prefer to use php sessions.

First, assign all the post data to their corresponding variables. If you can remember the 3 variables below are the 3 variables that we passed earlier on the load() function.

$id = $_POST['prodid'];
$prod = $_POST['prod'];
$price = $_POST['price'];

Then we declared a variable called $add which we initialize to 0. We will use this variable to store the value of 1 or 0. 1 if the product that is being added already exist in the cart, 0 if its not. This is to avoid duplicate products on the cart.

$add = 0;

foreach($_SESSION['item_session'] as $k=>$v){
	if($v['prodid'] == $id){
		$add = 1;

if($add == 0){
	$_SESSION['item_session'][] = array('prodid'=>$id, 'prod'=>$prod, 'price'=>$price);

First thing that we did in the above code, is to determine if the item_session already exist. If it exists we loop through all of its data, and if it finds a product id which is similar to the one that is currently being added, then we set the $add variable to 1. Which means that the product was already added in the cart.
Then we only add the product to the cart if the $add variable has a value of 0. Which means that the product that’s currently being added does not still exist on the session array.

Next, we again check if the item_session array exist. If it exist, we build a table that contains all the products added to the cart.

<table border="1">
<?php foreach($_SESSION['item_session'] as $k=>$v){?> 
	<td><?php echo $v['prod']; ?></td>
	<td><?php echo $v['price']; ?></td>
	<td><input type="text" class="qty_class" name="qty[]" data-price="<?php echo $v['price']; ?>" data-qtyconn="<?php echo $k; ?>" readonly></td>
	<td><input type="text" class="sub_class" name="sub[]" data-subconn="<?php echo $k; ?>" readonly></td>
	<td><img src="del.png" class="del_class" id="<?php echo $k; ?>"></td>
<?php } ?>
<?php } ?>
Grand Total:
<input type="text" id="grand_total" name="grand_total" readonly>

This is similar to what we did when we fetched the product list from the database. The only difference is that this time were fetching it from a session array. And we also have a text box for inputting quantity to be bought, but its readonly by default to avoid the user from inputting anything before the selection is still not confirmed. This means that the user has to select the products first, confirm it, and then input the quantities for each product.  Though the user can still remove the products which were already added, he cannot add anything else once the selection has been confirmed.
Lastly we have the grand total textbox, which should be readonly for the rest of the program. Since it will display the total price for all the products added to cart.

Okay, so before we continue I need you to go back to the file that you were working with earlier. The one with the javascript code on it. Here’s the code for computing the subtotal, it only computes when the user tabs from the textbox with the qty_class. Scroll up and check to see if the data that were fetching from the quantity textbox really exist. Yes, we assigned data-price attribute in the textbox for inputting product quantity for the sole purpose of fetching it later on and multiply it with the quantity inputted by the user. As you can see from the code below, we did a little trick to determine which subtotal textbox we should put the result to. Scroll up again to the html code above. I’ll just share to you one of the tricks that I’m using. That is, if you need to connect 2 or more elements, then declare an attribute for each of those elements. Make the attribute name different, but make their values the same. This way, those 2 elements will have something common. And that is what were going to use to pinpoint the matching element. Lastly, we call up the total_looper() function. This function loops through all the textbox which contains the subtotal, it adds up all of those values to come up with the grand total.

$('.qty_class').live('blur', function(){
	var price = $(this).attr('data-price');
	var qty = Number($.trim($(this).val()));
	var qty_con = $(this).attr('data-qtyconn');
	var sub = qty *  price;
	$("input[data-subconn="+ qty_con +"]").val(sub);

Here’s the code for the total_looper() function. We always begin by setting the grand_total to 0. To make sure that we always start with 0 when looping through the contents of each sub total textbox. For looping I just used jquery’s each() function. In the code below, we used it to loop through all the elements with the class attribute of sub_class. If you scroll up to the html code, you will see that the subtotal text box has this class.

function total_looper(){
	var grand_total = 0;
		grand_total = Number($(this).val()) + grand_total;

Next, is the remove from cart button. This requires us to create another file called session_destroy.php. The only thing that we pass in that file is the product id. You’re always free to scroll up to see if the remove button indeed contains the product id. After that, we just remove the parent of the remove button. That is by using a neat jquery function called parents. Which requires a single argument which is the parent element.

$('.del_class').live('click', function(){
	var id = $(this).attr('id');
	$.post('session_destroy.php', {'id' : id});

Create a new php file. This is the file used for removing products which are already added in the cart. What we do is just loop through the item_session array. Then check if there is an item which has the same id with the one that the user tries to remove. Finally, we just use php’s unset() function to unset that particular item.

$id = $_POST['id'];

foreach($_SESSION['item_session'] as $k=>$v){
	if($k == $id){

And don’t forge the code for the confirm button. First, we issue a confirmation dialog to the user, and have the variable x store its value. The value is either true or false. If its true, then we hide the product list and set the quantity textbox readonly attribute to none. This allows the user to input quantity for each selected product.

	var x = confirm('Are you sure you dont want to buy anything else?');
	if(x == true){



Yes, you’ve reached the conclusion of this tutorial. In this tutorial, you learned how to create an ajax powered cart system. You’ve also learned some of the tricks that I don’t usually share to just anyone except for those who are reading my blog. As you can see, what I just showed you is a trimmed down version of a full-blown cart system. You might want to enhance the code that I used here and build your very own super-awesome cart system.
That’s all, see you next time!

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.


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



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:


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.




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.

	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; ?>

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.


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.



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.