Simultaneously empty mysql tables

Have you ever been in a situation where you have lots of mysql tables in your database that you want to empty. Probably because you want to test your application with a clean database.
In this tutorial, I’m going to show you how to make an application that allows you to empty multiple mysql tables in one go.



  • Wampserver
  • Jquery core
  • Text-editor



As usual, prepare all the requirements. And place them all in one folder. I’m using a framework used for connecting and manipulating the database. It’s called EzSql. You can either follow along using your own method of connecting to mysql. Or you can go ahead and have a little crash course on EzSql, and come back here once you think you can already follow along.

EzSql Documentation

After having all the requirements prepared. Create a new php file, and name it info_schema.php or whichever name you like to name it. What this file does is just to connect to the built-in mysql database called information_schema. When I say built-in, it means that it is already there after you installed mysql. The main-purpose of this database is to store the information about the other database that you create, and that includes information on the tables and its fields. For more information about information_schema, visit this link.


$db = new ezSQL_mysql('root', '', 'information_schema', 'localhost');

Next, create another php file and name it empty_tables.php. Include the info_schema.php that we created earlier and also the jquery core file:


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

Also create a function that would check all the checkboxes. This allows you to check multiple checkboxes at once by just checking the main checkbox with the id of mc. The code is pretty much self-explanatory. We just select all of the checkbox by using the selector input[type=checkbox]. Then we just change the attribute checked to true or false, if the main checkbox is checked all of the input elements with the type of checkbox will also get checked. If the main checkbox is unchecked, all the other checkboxes will also get unchecked.


	var stat = $(this).attr('checked');
	if(stat == true){
		$('input[type=checkbox]').attr('checked', true);
	}else if(stat ==false){
		$('input[type=checkbox]').attr('checked', false);

Next, we check if the the variable database has been set in the url. If it does, we use mysql_real_escape_string() function to strip the string with all the characters which can be harmful to the database. This can help us avoid mysql injection.

<?php if(!empty($_GET['database'])){ $database = mysql_real_escape_string($_GET['database']);



<?php } ?>

Inside the empty check is the form that will be used to to select all the tables that needs to be truncated/emptied. Inside the form is a table, which has 2 columns, the table name, and then the checkbox that will be used to truncate the table. Then we performed an sql query to select the following fields: table_schema, table_name. If you carefully examine the information_schema database, you will notice that the table_name field stores all of the names of the tables that you have in each of the database that you have created. And the table_schema field contains the name of the database where that specific table is located. Yup, my explanation is really confusing so just go ahead and explore what’s in the information_schema database.

We then loop through all of those by selecting only the tables in the database that we have specified in the url. The string that is stored by the $database variable.
And then setting the name of the checkbox to be the actual name of the table.

<form name="x" action="empty_tables.act.php" method="post">

<input type="hidden" name="db"  value="<?php echo $database; ?>"> <table border="1"> <tr> <th>Table</th> <th>Truncate<input type="checkbox" id="mc"/></th> </tr> <?php $tables = $db->get_results("SELECT TABLE_SCHEMA, TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '$database'"); ?> <?php foreach($tables as $t){ ?> <tr> <td><?php echo $t->TABLE_NAME; ?></td> <td><input name="<?php echo $t->TABLE_NAME; ?>" type="checkbox"/></td> </tr> <?php } ?> </table> <input type="submit" value="truncate selected tables"/> </form>

Finally, create a file called empty_tables.act.php. This is where all the data from the empty_tables.php will be submitted and process.

$database = $_POST['db'];
$db = new ezSQL_mysql("root", "", "$database", "localhost");
$tables = array();

foreach($_POST as $k=>$p){
	$tables[$k] = $k; 	
	$db->query("TRUNCATE TABLE $k");


echo 'successfully emptied the following tables:<br/>';
foreach($tables as $t){

	echo '<ul><li>'. $t . '</li></ul>';

What this does is just to loop through all the post variable. This includes the name of the database that we are using, that’s why mysql will return a warning every time you execute this script. But it does its job well, since the tables that you checked still gets truncated.



I just showed you how to create a simple php application that can be used to simultaneously empty mysql tables.  The script that I have written is not in any way perfect. You might want to improve it.

How to unformat and save formatted numbers using accounting.js

Okay, so this is part 2 of the tutorial that I have written yesterday on the basic usage of accounting.js.

How to convert numbers to currency format using accounting.js

This time I’m going to show you how you can unformat those formatted numbers so that it can be stored in the database. I’m also going to show you how to save the data using ajax.

Since this is the second part of an existing tutorial. The requirements are still the same. You only need to install a database server like Wampserver if you want to have a database where you can save data.

We have a few changes on the markup. If you followed along in the first part its better if you just create a new file.

<label for="raw_num">Raw Number 1:</label>
     <input type="text" name="raw_num" id="raw_num">
     <label for="raw_num">Raw Number 2:</label>
     <input type="text" name="raw_num2" id="raw_num2">
     <label for="raw_num">Raw Number 3:</label>
     <input type="text" name="raw_num3" id="raw_num3">
     <label for="form_num">Formatted Number 1:</label>
     <input type="text" name="form_num" id="form_num" class="formnum">
     <label for="form_num">Formatted Number 2:</label>
     <input type="text" name="form_num2" id="form_num2" class="formnum">
     <label for="form_num">Formatted Number 3:</label>
     <input type="text" name="form_num3" id="form_num3" class="formnum">
     <input type="button" id="btn_save" value="save">

Does the markup suggests anything to you? To kill the question, yes were going to make use of arrays here in order to capture all the values in the text fields which has the formnum class. Formnum is short for formatted number.
As the name suggest, the one and only button in the markup is used for saving the data into the database.

Next is the script itself.
First, ensure that the user only gets to input numbers:

$('#raw_num, #raw_num2, #raw_num3').numeric();

Then format the raw numbers. Nothing new here, we just repeated what we did on the first tutorial.

	var raw_num = $(this).val();
	var form_num = accounting.formatMoney(raw_num);
	var raw_num = $(this).val();
	var form_num = accounting.formatMoney(raw_num);
	var raw_num = $(this).val();
	var form_num = accounting.formatMoney(raw_num);

Finally, the action to be performed when the user clicks on the save button. To explain things, on the 2nd line, we just declared an array called raw_num in which the unformatted numbers will be stored. On the 3rd line (Whitespaces isn’t considered a line), we loop through each of the elements which has a class of formnum. Then we used the unformat method of accounting.js to turn the number back to its raw form. Then on the 6th line, we called the jquery’s $.post function. The $.post function is a simpler version of the $.ajax function. Which only requires a minimum of 2 arguments, one is the php file that would talk to the server, and the second is the variable that you’re going to pass on to that php file. In this case we want the array of unformatted numbers to be the variable that’s going to be passed.

     var raw_num = [];
	raw_num[index] = accounting.unformat($(this).val());
     $.post('save.php',{'raw' : raw_num});


Maybe you’re wondering why do I still bother unformatting the number if there is already an unformatted version of that number. My answer would be yes, there’s really no purpose in formatting the number then unformatting it again if there exist a copy of the unformatted number.
But what if there’s none? What if the formatted version of the number should also be placed wherever the original number is.

And I almost forgot what the heck is save.php. Well save.php will be the one who talks to mysql. Saying: “could you please save this bunch of numbers into your database?“ Here’s what it contains. What we did here was to loop through the contents of $_POST[‘raw’]. ‘Raw’ is the name of the variable that we assigned in the javascript earlier.


foreach($_POST['raw'] as $num){
	$db->query("INSERT INTO accounting_js SET numbers='$num'");


Accounting.js is indeed a very nice tool for number formatting. But there are more features which aren’t discussed here, so you might as well check out the official site for more information regarding accounting.js.
Aside from accounting.js I also showed you how to loop through elements containing data, and save data into the database using jquery’s $.post method.

How to convert numbers to currency format using accounting.js

What’s up! I know why you’re here, don’t worry you don’t have to do another Google search because this is just what you need.
Yes, just like what the title sounds like. I’m going to show you how to format numbers using this awesome javascript plugin called accounting.js.





To make things different, first I want to show you the first output that we will be having today:


If you have just proven to yourself that this is what you need. Then keep on reading, and I’ll show you how to do it.

Second, do the housekeeping:

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

If its your first time here. Housekeeping is similar to getting things ready. So to get things ready, you should include the files that you need into the file that you are going to use for testing.

Next, build the form:

     <label for="raw_num">Raw Number:</label>
     <input type="text" name="raw_num" id="raw_num">
     <label for="form_num">Formatted Number:</label>
     <input type="text" name="form_num" id="form_num" readonly>

Raw number field is where user input goes. And Formatted number field is where the formatted number goes. When I say formatted number, it has all these stuff:

  • Currency Sign (Peso, Euro, Yen, Dollar, etc..)
  • Separators – comma separators to make the number more readable. To make the value easy to determine.
  • Suffix  – the 2 zeroes which follow the period found at the end of a formatted number (.00)


Okay, so we are now going to write the javascript code. First, to limit user input to only numbers. We call the numeric function. Note that this will only work if you have properly included the js numeric file into your working file.


Next, we need to do something when the user starts typing bunch of numbers on the text field.

     var raw_num = $(this).val();
     var form_num = accounting.formatMoney(raw_num, "P");

On the second line, we just assigned the current value that is in the text field to a variable called raw_num. On the third line, we called the formatMoney function of the accounting js class. Most of the time, it only requires 1 parameter. But if you want to be more specific with what you like then you are free to do so. Here are the arguments, note that the only mandatory argument is the actual number to be formatted:

  • Number to format
  • Prefix
  • Number of zeroes to suffix
  • Separator

Finally, on the fourth line, we just assigned the formatted value to be the value of the formatted number field.

On the example above. We made use of the default settings of accounting.js. Using the letter “P” as the prefix.

I believe you need more than one example. So here’s another example from the site itself. When you look at it, it is self explanatory if you have been coding for a while now.

accounting.formatMoney(4999.99, "€", 2, ".", ","));

But for the sake of those who don’t know, then here’s my explanation. Where 1 is the first argument, and so on.

  1. Number to format
  2. Prefix
  3. Number of zeroes to suffix
  4. Custom Separator
  5. Separator between the actual number and the 2 zeroes used for suffix.

So that if you change the example to:

accounting.formatMoney(4999.99, "€", 2, "-", "/"));

You will end up with something like this:



Next time, I’ll discuss about how you can unformat the formatted number so that it can be stored in the database and use for computations.

Tools for lazy developers

We’ve heard it countless times before. In order to become a successful developer you need to be lazy. But what exactly does that mean? In this article, I’ll show you what it means to be a lazy developer by sharing you some of the tools that I’m using to make system development faster and easier.


Find and Replace

Use the Find and Replace functionality of the text editor or IDE that you are using. This will be faster, instead of replacing the text one by one. Just be careful though, you might replace something that’s not supposed to be replaced.


Integrated Development Environment

Without a doubt, IDE’s makes life easier for developers. I often call it text-editor on steroids. Since its much like a text-editor but with additional functionalities. Like IntelliSense or auto-completion of code that you are trying to write. Most IDE’s also include a file manager for easier file access. One example of an awesome IDE is Aptana Studio.


Cheat Sheet

A cheat sheet is also another indispensable tool for developers. Used as a quick reference for functions available for a programming language. Or different kinds of css selectors. Here’s a link where you can download some of the useful cheat sheets for web development.


Code Snippets

Use code snippets if you’ll be using the same functions throughout a project. Code snippets is basically a reusable code. Which you can use over and over again throughout the development of a system. Sometimes it requires some modification to make your code more descriptive. has a huge collection of code snippets available on their site:


Free Scripts

“Don’t reinvent the wheel”. The overly used quote in systems development. This is very true, that’s why sites like dynamicdrive and htmldrive are created in order for us to make use of code that’s already been written before. Yes its not evil to make use of the scripts available on the sites I’ve mentioned above. Just be sure to acknowledge the author of the code by not removing the comments which is a  reference to the author.



I guess that’s it for this roundup. Being lazy is not always a negative trait especially for developers. Just be sure that being lazy for you is not intellectually lazy. Because there’s no room for you to improve if you are too lazy to do anything that can help you improve.

Web Development Resources August 20 2011

What’s up! I’m here again writing about some random stuff that I find  useful for web designers and developers.
I know that I have recently posted a roundup entitled Web Development Resources August 2011. So its expected that you will see something like this 10 days from the time of writing of this post. But I decided to just do it randomly. That is, if my Evernote gets super filled up with these resources then I post it here.
So without further spoilage, here it is:


Disallow special characters on text field

Submit html form through ajax

Jquery autocomplete
A Jquery plugin for cross-browser autosuggest functionality on text fields. Just don’t forget to turn off autocomplete if you don’t want the browser to fetch autosuggest data from the forms you have recently submitted.

Php Minifier
If you want to decrease the size of your php file for faster loading. Then this tool is just what you need.

Javascript Code Minification Tools
Here are some of the tools used for compressing javascript code. Compressed or minified files, downloads faster because it is smaller in size. These tools are really important to improve performance of your site. You may only need this on a production environment.

Code Checking Tools
If you want to improve the quality of your code, be it php, css, or javascript code. Then you can visit the following links and paste the code you want to check. It will show you all sorts of bad stuff about your code no matter how awesome you think your code is.

Javascript Libraries
Here are some of the Javascript libraries that can help your life as a designer or a developer easier.

Jquery useful plugins
This is actually an article which shows you some of the jquery plugins that you might find useful in the current project that you’re working with.

The following jquery plugins makes table pagination easier than ever before.


Image for web optimizer
Its not just Javascript, css or php files which needs to be compressed. Even images needs it. Yes, again for faster page load and overall site performance.

Image lazy loading
Load the image only when the image is in the visible part of the page. And not when the page loads. This has some performance benefits as well.

Custom drop-down menus
If you’re already sick with the default styling and functionality of the html drop-down menu. Then try this script to breath new life into those boring drop-down menus.

Javascript Search Engine
Some sort of a search engine. But exclusively for Javascript stuff. Try it if Big G can’t provide you any good answers.

Awesome Collection of Css Tips and Tricks
Yet another awesome article showing you awesome stuff on css.

Disable context menu using javascript
Can hide your source code from prying eyes. But pretty much useless once the perpetrator disables javascript on the browser. You can still use this one though if you think people who wants to look at your code doesn’t know how to disable javascript on the browser.

10 online tools to simplify html5 coding
Another collection of awesome stuff from catswhocode. This time on html tools and sites where you can find useful information on html5.

5 ways to make ajax calls in jquery
Finally, here’s an absolute beginner tutorial on how to perform ajax calls using the jquery. The most awesome kickass Javascript Library of them all.

How to create an image uploader using php

This is a 2-part tutorial series where I’m going to show you how to create an image viewer in php. Which will consist of:

  1. image uploader
  2. image viewer

What you’re reading right now is the first part. That is how to upload images using html and php.



You are only going to need Wampserver if you are on Windows.



First you’re going to need to build the table that were going to use:

  `productID` int(11) NOT NULL AUTO_INCREMENT,
  `str_productName` varchar(100) NOT NULL,
  `str_category` varchar(80) NOT NULL,
  `str_description` varchar(700) NOT NULL,
  `dbl_qty` double NOT NULL,
  `dbl_price` double NOT NULL,
  `str_filename` varchar(70) NOT NULL,
  PRIMARY KEY (`productID`)

Just access phpmyadmin. Create a sample database, then execute the code above as an sql to build the table.

Next, create a new php file. And copy the following code:

<form action="create_item.php" method="post" enctype="multipart/form-data">
<label for="item_img">Item Image:</label>
<input type="file" name="item_img" id="item_img">
<label for="product">Item:</label>
<input type="text" name="product" id="product">
<label for="category">Category:</label>
<input type="text" name="category" id="category">
<label for="description">Description:</label>
<textarea id="description" name="description" id="description">
<label for="qty">Quantity:</label>
<input type="text" name="qty" id="qty">
<label for="price">Price:</label>
<input type="text" name="price" id="price">
<input type="submit" name="create_item" value="Create Item">

There’s really nothing new and fancy in this code. Except for the enctype attribute for the form. Then the file input type.

First I’ll explain what enctype is. Enctype is short for encoding type. Which means that when we are using this attribute, we are specifying how the data which is submitted through that form is encoded. By default you don’t really need to use this attribute if you’re just submitting text. But since were going to submit an image were gonna need to specify the enctype. And the proposed enctype for submitting images is multipart/form-data.

Next, we process the submitted data:

  //code goes here

I just did that to make things short. But normally you would need to check if the post variable for the submit button contains anything.

Next, do a simple data processing. To make things easier on the fingers. Just create a loop which will loop through all the post data. We then do the string cleansing inside the loop, and assign the cleansed data into an array called $proc.

$proc = array();

foreach($_POST as $key=>$raw){
	$proc[$key] = strtoupper(trim(mysql_real_escape_string($raw)));

In case you need a little helping what each function does:

  • strtoupper – converts a string of text into uppercase format. Something like this-> “hELLo”. Will turn to this-> “HELLO”. This is to ensure that all the data that will be stored in the database has the same format. Of course you can also do strtolower.
  • trim – removes the leading and trailing whitespaces on your strings. It won’t really make much sense if I created an example here. So just try it by yourself.
  • mysql_real_escape_string – removes any invalid characters from a string. By invalid characters, I mean something that could ruin an sql query if that character actually existed in the query. Most of the time this is the only thing that you’ll need to ensure the sanity of data that’s going to enter into your database.


Next, assign the processed data into their corresponding variable:

$item = $proc['product']; $category = $proc['category']; $descr = $proc['description']; $qty = $proc['qty']; $price = $proc['price'];

$filename = $_FILES["item_img"]["name"];

By corresponding variable, I mean that you’re going to assign it into something that would actually describe what that variable is going to store. Not something gibberish like $thisVariable or $str1 or $var1.

I think something needs some explanation here. That is the $filename variable. This stores the filename of the image that was selected using the form. This only stores the filename and not the actual path. Example: angrybirds.jpg.

Then perform the sql query that would insert the data into the table:

$create = $db->query("INSERT INTO tbl_products SET str_productName='$item', str_category='$category', 
				str_description='$descr', dbl_qty='$qty ', dbl_price='$price', str_filename='$filename'");

Finally, we check if the file that is being uploaded meets our criteria:

  • It has to be an image file (jpg, png, gif)
  • It has to be 1 953.125 Kb. (It could go lower or higher depending on what you want)
  • It must not already exist in the folder which stores images (Of course, you can just fake this one by giving a different filename to the same exact image)

Here’s the code:

if ((($_FILES["item_img"]["type"] == "image/gif")
|| ($_FILES["item_img"]["type"] == "image/jpeg")
|| ($_FILES["item_img"]["type"] == "image/pjpeg")
|| ($_FILES["item_img"]["type"] == "image/png"))

&& ($_FILES["item_img"]["size"] < 2000000))
  if ($_FILES["item_img"]["error"] > 0)
    echo "Return Code: " . $_FILES["item_img"]["error"] . "<br />";
    echo "Upload: " . $_FILES["item_img"]["name"] . "<br />";
    echo "Type: " . $_FILES["item_img"]["type"] . "<br />";
    echo "Size: " . ($_FILES["item_img"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["item_img"]["tmp_name"] . "<br />";

    if (file_exists("../img/items/" . $_FILES["item_img"]["name"]))
      echo $_FILES["item_img"]["name"] . " already exists. ";
      "../img/items/" . $_FILES["item_img"]["name"]);
      echo "Stored in: " . "upload/" . $_FILES["item_img"]["name"];
  echo "Invalid file";

Here’s a breakdown of the variables that we used:

  • $_FILES[“item_img”][“type”] – item_img is the name that we gave to our input with a type of file(input type=file) . And the type just stores its file extension. Not necessarily the type since you can just freely change the file extension.
  • $_FILES[“item_img”][“name”] – this stores the filename, together with the file extension. I have a feeling that I already said that a while ago but never mind.
  • $_FILES[“item_img”][“error”]  – in case there is an error in the image to be uploaded. Whatever it is, this variable stores it.
  • $_FILES[“item_img”][“size”] – as the name suggests. This simply stores the size of the file.

And here’s a breakdown of the functions that we used:

  • file_exist() – checks whether a file exist on a specified folder in the server. This requires 1 argument by default. That is, the absolute or relative address of the image in the server. What we implemented in the above code is the relative address. If you want you can also use the absolute address: “http://localhost/store_is/img/items/” . $_FILES[“item_img”][“name”]
  • move_uploaded_files()  – as the name of the function suggests. This simply moves the selected file to a specified folder in the server. This requires 2 parameters: first is the source address, second is the address where you want to move it. You don’t actually need to write a bunch of code to determine the actual location of the file in the client computer(the one accessing the server). Because its already stored in a temporary folder in the server. Once you submit a data to the server. Normally it is stored under: C:wamptmp . But of course, the files stored in that folder doesn’t actually take the form of an image.



I guess that’s it for this tutorial. You have learned how to:

  • sanitize string using foreach loop.
  • validate file size, and file type of an uploaded file in php
  • check if the file already exists in the specified folder in the server
  • move the uploaded files to a specified folder in the server

In the part 2 of this series. I’m going to show you how you can build an interface where you can view those images along with a description fetched from the database.

Cross-browser auto-suggest using jquery ui

You might say this is yet another auto-suggest tutorial if you have read the last one that I have wrote:

How to use php and datalist for easier auto-suggest input field

And countless others in the blogosphere. This time around, I want to show you how to do it using jquery ui. And because its through the use of a framework. You might say that this will be cross-browser. And it works on every MODERN browser that you can think of.



  • jquery core
  • jquery ui



First, link the jquery  and jquery ui javascript files on the file that you’re going to use.

<link rel="stylesheet" href="../css/jquery_ui.css"/>
<script src="../js/jquery.js"></script>
<script src="../js/jquery_ui.js"></script>

It’s a good practice that css comes first when linking files. Its because its FASTER to load css files than javascript files.  Just Google it to have some proof. Because I currently do not have the link for it.

Second, build your forms:

    <label for="product">Product:</label>
    <input type="text" name="product" id="product">

Third, create another php fille. This will be used as the data fetcher. In other words, this file will be used to fetch the items for our auto-suggest text field.


$prods = $db->get_results("SELECT * FROM tbl_products");
foreach($prods as $p){
	echo $p->str_productName.';';

Here’s a summary of what we did in the above code:

  • include database configuration file
  • fetched all the records from the tbl_products
  • loop through those records and output them. Using a semi-colon as a separator.


Fourth, declare an array that will hold the values fetched from the php file for fetching records. Then make an ajax call. Specify the url, and the action that you want to perform once the data is fetched.

    url: "search.php",
    success: function(data){
    prods = data.split(';');

The name of the file for fetching data is search.php. If you have used a different filename for your data fetcher then replace search.php with the filename.

The callback function for the attribute success, will have a default parameter which contains the data that was fetched. It can be any name you want. In this case its called ‘data’.

Then we just use the javascript split() function. Which is like the explode() function in php. This will convert a long string of text into an array.

We need to convert it to an array because the auto-suggest/ autocomplete function for jquery ui uses array as a data source.

This will mean that the variable prods now contains an array of of the product names that we have on the database.

Finally, execute the autocomplete function everytime a focus event on a text field happens:

    source: prods



I guess that’s it for this short tutorial. You have learned how to make use of jquery ui to create a cross-browser implementation of auto-suggest text field.