MySQL DELAYED INSERT and MySQL DELAYED UPDATE

Overview

Performing MySQL queries on your websites or server can cause long delays in a page loading or script executing while the MySQL INSERT or MySQL UPDATE finishes executing. This causes long delays for the user or possibly delays the rest of a script from running until the MySQL query completes (or errors out). Generally the delay on mysql inserts or update is minimal however on large complex queries with lots of join across multiple tables and millions of rows of data (or badly indexed tables), there will be a significant pause while the query completes.

Solution to Long Queries

If there is no need for the user to see the result of the query, such as queries that are involved with logging statistics or building hidden reports, then it would be highly recommended to use mysql delayed insert or mysql delayed update syntax. Using MySQL DELAYED INSERT and MySQL DELAYED UPDATE to send the queries into the queue and run in the background allowing the page or script to continue executing. It is simple to use and can be implemented by adding the `delayed` keyword after INSERT or UPDATE command and the sql query will be queued by the MySQL server to be run in the background and the page while continue loading while the sql query is executed in the background as the example below shows:

MySQL DELAYED INSERT

MySQL Insert statements can be run in the background by using the following syntax:

INSERT DELAYED `some massive time consuming query`;

MySQL DELAYED UPDATE

MySQL Update statements can be run in the background by using the following syntax:

UPDATE DELAYED `some massive time consuming query`;

Conclusion

Now the page will continue to load while the MySQL server continues to execute the query which can drastically speed up page loading speeds or script execution times. Bear in mind that if the user needs to see the result, then you won’t be able to use mysql delayed inserts.

Display Disk Free Space Percentage In PHP

Overview – Display Disk Free Space Percentage In PHP

Have you ever needed a nice little progress bar on your website to display disk space free percentage? This can be handy if you would like to give your clients an indication on how much disk space free they have on their own virtually hosted website, or perhaps you need a simple web interface to keep an eye out on your server health. In any case, this progress bar will accurately show you how much disk space free and disk space usage on your server.

Checkout out a follow up article called showing an alternate way to determine the total size of a folder using php.

End Result – What This Article Will Achieve

Get Total Directory Size In PHP

Get Total Directory Size In PHP

Once you have copied and implemented the code shows in the 3 steps, you should have a tidy progress bar that will show you your disk usage on your server. Below is an image of what it should look like if you have followed the steps correctly.

 

Step 1 – PHP Code

Place this code into a PHP script inside the web directory of your choice. All three steps can be included into the same PHP file or seperated if requried.

<?php

/* get disk space free (in bytes) */
$df = disk_free_space("/var/www");
/* and get disk space total (in bytes)  */
$dt = disk_total_space("/var/www");
/* now we calculate the disk space used (in bytes) */
$du = $dt - $df;
/* percentage of disk used - this will be used to also set the width % of the progress bar */
$dp = sprintf('%.2f',($du / $dt) * 100);

/* and we formate the size from bytes to MB, GB, etc. */
$df = formatSize($df);
$du = formatSize($du);
$dt = formatSize($dt);

function formatSize( $bytes )
{
	$types = array( 'B', 'KB', 'MB', 'GB', 'TB' );
	for( $i = 0; $bytes >= 1024 && $i < ( count( $types ) -1 ); $bytes /= 1024, $i++ );
		return( round( $bytes, 2 ) . " " . $types[$i] );
}

?>

Step 2 – CSS Style Sheet

Place this code into a CSS style sheet inside the web directory of your choice. The CSS code can be included into the same PHP file created in step 1 or seperated into its own CSS file if required, however since it is executing php code to create the width of the element, it should not be placeed inside a separate css file.

<style type='text/css'>

.progress {
	border: 2px solid #5E96E4;
	height: 32px;
	width: 540px;
	margin: 30px auto;
}
.progress .prgbar {
	background: #A7C6FF;
	width: <?php echo $dp; ?>%;
	position: relative;
	height: 32px;
	z-index: 999;
}
.progress .prgtext {
	color: #286692;
	text-align: center;
	font-size: 13px;
	padding: 9px 0 0;
	width: 540px;
	position: absolute;
	z-index: 1000;
}
.progress .prginfo {
	margin: 3px 0;
}

</style>

Step 3 – HTML Code Including the Progress Bar

Place this code into an HTML file inside the web directory of your choice. The HTML code can be included into the same PHP file created in step 1 or seperated into its own file if required.

<div class='progress'>
	<div class='prgtext'><?php echo $dp; ?>% Disk Used</div>
	<div class='prgbar'></div>
	<div class='prginfo'>
		<span style='float: left;'><?php echo "$du of $dt used"; ?></span>
		<span style='float: right;'><?php echo "$df of $dt free"; ?></span>
		<span style='clear: both;'></span>
	</div>
</div>

Get Last Day Of Month MySQL And PHP

In order to get last day of month in mysql or php, you can use built in functions. As you will soon see below it is a rather straight foward task. I will show you two examples on how to get the last day of the month in mysql as well as php. In php you can use the date() function and in mysql we will make use of the last_day() method.

Get last day of the month PHP

PHP has a date function which returns a string representation of the date formatted according to the expression passed to it. So the simple way is to use the ‘t’ identifier in the date function which will return the last day of the month.

echo date('Y-m-t',strtotime('2014-01-12')); // echo's 2014-01-31
echo date('t M Y'); // echo's 31 Jul 2014

Get last day of month MySQL

MySQL has a function called last day that will return the last day of the date supplied to it. It can be the date of the current month or any other month.

SELECT last_day(CURDATE() - INTERVAL 1 MONTH) AS last_month;
SELECT last_day(CURDATE()) AS current_month; 
SELECT last_day(DATE('2014-03-12')) AS march_month

Conclusion

As you can see, getting the last day of the month is a simple task. If you get stuck, let me know in the comments.

Install PHP Pear and Pear Packages

What is PEAR?

PEAR is an acronym for PHP Extension and Application Repository. PEAR is a massive repository for PHP code. It provide easy access to hundreds of classes that can be implemented into your existing PHP project. It saves you time since you won’t have to write anything from scratch and you can simply reuse the code from the PEAR repository.

A few examples of the types PEAR packages available to you are packages such as encryption, mailing, image manipulation, benchmarking, caching, authenticating and so much more.

Install PHP Pear

Now that you know what PEAR is, you may be asking “how do i install PHP PEAR?”.

Under Debian this is quite simple. Enter the following command and it will download and install PEAR for you straight out the box.

$ apt-get install php-pear

It should really be that simple.

Installing a PEAR Package for PHP

Once you have installed PEAR, you can being installing all your much needed PEAR packages. To do this is a matter of entering the following:

$ pear install package-name

Where package-name is the name of the PEAR package you wish to install. So if I wanted to install the Auth_HTTP package, I would do this:

$ pear install Auth_HTTP

Upgrading a PEAR Package for PHP

Now to upgrade a PEAR package is pretty much the same as installing it.

$ pear upgrade package-name

Conclusion

Using PEAR packages will rapidly speed up your development since you will be using classes that are already complete with all the functionality you require instead of manually having to code from scratch. You can then focus on what is important, building amazing websites and programs that amaze everyone who comes to visit!

Validate Email Address Online

Use the form below to check email address is valid. Simply enter an email into the box below and hit ‘validate’ to validate the email address online. It simply makes sure the format of the email address is correct and does not verify that the email address actually exists or belongs to anyone.

Validate Email Address Online

You can get the code used to test the email address in this example by clicking here.
Enter Email Address and Click Validate

What is eAccelerator for PHP

Overview

Before we can understand how eAccelerator (http://eaccelerator.net/) will help speed up your websites loading speed, it is important to realise how php scripts are executed and run to power your websites.

Executing PHP Scripts

When you create a php script to do amazing things like pulling information out of a MySQL database or connecting to a web service to perform complex authentication or other wondrous things, the php script must be compiled into byte-code before it can be run by the web server. The result of this is that each time a user accesses that specific php script, usually through your website, that script is then compiled and then executed. When multiple people access the same script, it is being recompiled over and over which can slow down your website and essentially puts an extra bit of strain on the web server it is being run on.

What is eAccelerator for PHP

Now this is where eAccelerator comes into play. eAccelerator is a php extension that you install onto a web server. After it is installed, each php script will then automatically be precompiled the first time they are executed and also stored for later. Now each time the script is accessed, the pre-compiled script is executed without the need of having to compile the script first.

Interested in installing eAccelerator? I have created a quick tutorial on how to install eAccelerator on Debian here.

Install eAccelerator On Debian Linux

After trying eAccelerator and finding out how great and simple it is, I decided to write a quick guide to show you how to install eAccelerator for PHP. If you are unsure on what eAccelerator is, you can view my post describing what it does and how it can drastically improve your website page generation speed.

Step 1 – Pre-requisites

Now to begin installing eAccelerator for php, make sure you have php4 or php5 installed along with autoconf, automake, libtool and m4. Running the below command appeared to installs these programs for me without any issues but ymmv.

apt-get install libtool m4 php5-dev make

If you get the error phpize command not found then make sure you installed php5-dev. It will also install automake and autoconf which are all required to install eAccelerator.

Step 2 – Get the latest version of eAccelerator

Download the latest version from the offical eAccelerator website and extract the contents to a folder such as your home directory and then CD to the extracted folder.

Step 3 – Building and install eAccelerator

Next step is to Install eAccelerator On Debian Linux. In order to do so, run the following commands commands. If you get any errors such as command not found, then ensure you have followed step 1

phpize
./configure
make

Pay attention to any output to make sure there are no errors before running the command:

make install

Step 4 – Edit your php.ini

Add the following lines to your php.ini file. Your php.ini is usually under /etc/php5/apache2/php.ini. You can run the php command “” to see where your php.ini file is located.

extension="eaccelerator.so"
eaccelerator.shm_size="16"
eaccelerator.cache_dir="/tmp/eaccelerator"
eaccelerator.enable="1"
eaccelerator.optimizer="1"
eaccelerator.check_mtime="1"
eaccelerator.debug="0"
eaccelerator.filter=""
eaccelerator.shm_max="0"
eaccelerator.shm_ttl="0"
eaccelerator.shm_prune_period="0"
eaccelerator.shm_only="0"
eaccelerator.compress="1"
eaccelerator.compress_level="9"

Step 5 – Create the cache directory and restart apache2

Create the cache directories. This is were your compiled scripts will be held. My apache user is www-data so replace www-data with the user your apache is run under.

mkdir /tmp/eaccelerator
chown www-data.www-data /tmp/eaccelerator
chmod 0744 /tmp/eaccelerator
apache2ctl graceful

Step 6 – Check the installation

Check to make sure eAccelerator is installed and running by executing the php function “” and scroll down to the modules section and look for eAccelerator.

The Richters Gallery – Online Photo Gallery

The Richters Gallery is a basic online photo gallery. It was originally developed as an online photo sharing facility allowing us (i.e. the Richter family) to show our geographically scattered family what we are up to.

First version of the website is up and running and is as basic as any website comes and can be viewed here.

I am currently redeveloping the entire site and putting in a large number of improvments from ratings to search features to social networking and much more.

View The Richters Gallery Here…