Use indexes to reduce the overhead on MySQL server

In MySQL database, indexes are used to optimize the database server by reducing the cost for searching data. Take it as an example of index page you see at the end of text books. It contains the page number of important words, so you do not have to check each page of the book to find that word. You just get the page number from index page, and directly lands on that specific page.

Same is the case with indexes in MySQL database. Indexes are applied on column level, so you can specify which values should be indexed. Generally, we apply indexes on those columns whose values are searched most often. For example, in user’s table, one might search for user by his email address. So we can apply the index on email address.

In this tutorial, we are using sample database called classicmodels and the table where we will be working is named as orderdetails. As discussed earlier, indexes are applied on column level, so we will be using column named productCode for this purpose. So let’s get started.

Get all indexes on table

The basic query of getting all indexes applied on some specific table is:

SHOW INDEX FROM table_name;

It will return name of index, sequence number of when applied, column name, integrity and collation etc. The output will be similar as screenshot above.

Even if you haven’t applied any index on any column of that table, you will still see indexes already applied on your table’s primary key (if you have any). They will be applied at the time of creation of table.

You can try running the query on productCode column using EXPLAIN alias and you will see the number of records MySQL server has to go through, just to fetch specific records.

EXPLAIN SELECT * FROM orderDetails WHERE productCode = "S18_3232";

In our case, the query has returned 53 records without applying EXPLAIN clause. And after applying EXPLAIN clause, it will tell that it has searched in 2996 records. So it is an overhead on MySQL server that it has to search in 2996 records just to pull 53 records. So we will be applying index on this column and you will see how much the query will be optimized.

Add index on column

You can either apply index by using ALTER statement or by CREATE INDEX, but we recommend using ALTER since it is been used by other DDL operations too, so it will be easier for you to remember. Also, ALTER helps you when deleting an index from table. Deleting index from column is also discussed at the end of this tutorial.

ALTER TABLE table_name ADD INDEX index_name (column_name);

In this statement, index_name can be anything of your choice. But make sure that it does not have any space and that index name must not be applied on any column before.

Check performance of query

As discussed earlier, we will be applying index on column named productCode. So suppose we have applied the index using the above query, and now we want to see the performance of our query.

After applying the index, you can run again the query but put EXPLAIN clause before the query. So you will performance of query. You will see that the number of rows fetched will be equal (or almost equal) to the number of rows MySQL server has seached.

In our case, MySQL server returned 53 rows using the above query. And the number of rows it has to go through is also 53. Your number may differ based on the size of your table and the query that you are testing.

Also, you will see the column named filtered in result of EXPLAIN query. That tells the percentage of data it has filtered between number of searched rows and number of rows in result.

Delete indexes from column

This will be almost similar to the add index section. You just need to replace the “ADD” with “DROP” and execute the query. In this query, you do not have to specify the name of column, just specify the name of index and it will be done.

ALTER TABLE table_name DROP INDEX;

Try running the show index query again and you will see that specific index will be removed from that column.

PHP PDO prepared statement – CRUD Operation

PHP PDO (PHP Data Objects) is basically an abstraction to database access, which means it is not limited to only MySQL or any other database system. If you are building a website in MySQL and suddenly you are required to change the database from MySQL to PostgreSQL, if you are using PDO at backend then only minor changes needs to be done in order to switch database. However, in other cases, you might have to re-write all the queries again in order to switch database.

PDO also provides a very neat way to write prepared statements which helps to prevent SQL injection in your database. In this tutorial, we will be performing completed CRUD (Create, Read, Update, Delete) operation using PHP PDO & MySQL. For sending data from forms, we will using AJAX so you also learn to fetch data from database without having to refresh the page.

Create data using PHP PDO prepared statement

First we will create a form from where we will get the input from user, and when user click the submit button, it will send an AJAX request with all form data. At the server end, server will first connect with our sample database called classicmodels using PDO. Then we will get all fields attached in AJAX request, prepare a query to prevent from SQL injection. Finally execute the query to add a new row in database.

You need to create a new file named save-data.php which will store the record in database. As this is an insert operation, so we might need the newly inserted ID on client side. So we can get the newly inserted ID by calling the $conn->lastInsertId() method from PDO connection object.

index.php

<form onsubmit="return addData();">
    <p>
        <input id="firstName" placeholder="Enter first name">
    </p>

    <input type="submit">
</form>

<script>
	function addData() {
        var firstName = document.getElementById("firstName").value;

        var ajax = new XMLHttpRequest();
        ajax.open("POST", "save-data.php", true);
        ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        ajax.send("firstName=" + firstName);

        ajax.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                console.log(this.responseText);
            }
        };

        return false;
    }
</script>

save-data.php

<?php

// Connecting with database
$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");

// SQL query string
$sql = "INSERT INTO employees (firstName) VALUES(:firstName)";

// Preparing the statement
$result = $conn->prepare($sql);

// Actually executing the query in database
$result->execute(array(
	":firstName" => $_POST["firstName"]
));

// Sending inserted ID back to AJAX
echo $conn->lastInsertId();
?>

Remember that you should not write variables directly in SQL query string. You just have to mark the places where you wanted to put variables like :firstName. This will tell that we are going to map this with our variable later. Then in execute() function, you have to pass an array and map each string with corresponding variable.

Read data using PHP PDO prepared statement

Now that we have saved the data in database, now we need to display it to the user. First we will create table tag where we will display all records from database in tabular form. Then we will call an AJAX to request the server to pull records from database and return them as JSON. When data is returned from server, we will convert the JSON back to Javascript objects and display that in table rows and columns.

index.php

<table>
    <tbody id="data"></tbody>
</table>

<script>
	var ajax = new XMLHttpRequest();
	ajax.open("GET", "get-data.php", true);
	ajax.send();

	ajax.onreadystatechange = function () {
	    if (this.readyState == 4 && this.status == 200) {
	        var data = JSON.parse(this.responseText);

	        var html = "";
	        for (var a = 0; a < data.length; a++) {
	        	var id = data[a].employeeNumber;

	            html += "<tr>";
	                html += "<td>" + data[a].firstName + "</td>";
	            html += "</tr>";
	        }

	        document.getElementById("data").innerHTML += html;
	    }
	};
</script>

On server side, we will connect with database using PDO same way as we did for insert operation. When you are working on projects, make sure to use only one PDO object. You can do that by either using singleton objects or sending persistent attribute in PDO constructor after password field as below.

Using singleton

if ($conn == null)
{
	$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");
}

Connect with database, execute the query to get all records from database. In this query, we are not using any variable input which is received from user, so no need to use prepared statement. But if you are required to use user input variable in query, you should follow the same method as we did for insert operation.

get-data.php

<?php

$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "", array(
	PDO::ATTR_PERSISTENT => true
));

$sql = "SELECT * FROM employees";
$result = $conn->query($sql);

echo json_encode($result->fetchAll());

?>

Adding persistent attribute in PHP PDO constructor will make your website faster by caching the connection. So the next time some script request for database connection, it will not create a new connection again but will return the cached connection. This will result in removing the overhead on database for making multiple connection on same database. Persistent connections are not closed at the end of script but will remain open for other scripts too.

Update data using PDO

Updating data consists of 3 parts.

  1. Create an edit button which when clicked will redirect to new page.
  2. Get all data of selected record from database and auto-populate that in form.
  3. Update the data in database when that form submits.

In your read operation AJAX where you are displaying data in table, add the below line inside the loop along with other <td> tags. This will redirect to edit page along with ID of selected record. That should be the unique ID in your table. We were already getting this ID variable in read operation.

index.php

html += "<td><a href='edit.php?id=" + id + "'>Edit</a></td>";

The code used for update operation is almost similar to the one we used for insert operation. First we will get the record from database using ID which we will be receiving from URL. User might temper that URL so we must bind that in prepared statement to prevent SQL injection.

The form will be similar to insert operation’s form, except we add a new hidden field for sending ID of selected record. It will also be appended in AJAX request. The rest will remains same.

edit.php

<?php

    $conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");

    $id = $_GET["id"];

    $sql = "SELECT * FROM employees WHERE employeeNumber = :id";
    $result = $conn->prepare($sql);
    $result->execute(array(
        ":id" => $id
    ));
    $data = $result->fetch();

?>

<form onsubmit="return editData();">
    <input type="hidden" id="id" value="<?= $id; ?>">
    <input id="firstName" placeholder="Enter first name" value="<?= $data["firstName"]; ?>">

    <input type="submit">
</form>

<script>
    function editData() {
        var firstName = document.getElementById("firstName").value;
        var id = document.getElementById("id").value;

        var ajax = new XMLHttpRequest();
        ajax.open("POST", "edit-data.php", true);
        ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        ajax.send("firstName=" + firstName + "&id=" + id);

        ajax.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                console.log(this.responseText);
            }
        };

        return false;
    }
</script>

Delete data using PDO

Deleting data consists of 2 parts.

  1. Create a delete button which when clicked will send an AJAX request.
  2. Server will run the delete query on selected record’s unique ID.

In your read operation AJAX where you are displaying data in table, add the below line inside the loop along with other <td> tags. This will call our Javascript function along with ID of selected record. That should be the unique ID in your table. We were already getting this ID variable in read operation.

A new function deleteData(id) will be created which will accept the ID of selected record and pass that ID with the AJAX request to the server.

index.php

html += "<td><button onclick='deleteData(\"" + id + "\")'>Delete</button></td>";

function deleteData(id) {
    if (confirm("Are you sure ?")) {
        var ajax = new XMLHttpRequest();
        ajax.open("POST", "delete-data.php", true);
        ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        ajax.send("id=" + id);

        ajax.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                console.log(this.responseText);
            }
        };
    }
}

Delete operation will be almost similar to update operation. You just create a prepared statement and map the ID which we are receiving from AJAX request and execute the query.

delete-data.php

<?php

$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");

$sql = "DELETE FROM employees WHERE employeeNumber = :id";
$result = $conn->prepare($sql);
$result->execute(array(
	":id" => $_POST["id"]
));

echo "Done";

?>

If you want to delete the <tr> tag from <table> when the delete button is pressed, you need to give a custom data ID attribute to TR tag and call the remove() function to remove the TR node.

// In read operation AJAX loop
html += "<tr data-id='" + id + "'>";

// In deleteData() function
document.querySelector("tr[data-id='" + id + "']").remove();

So that’s the completed CRUD operation using PHP PDO & MySQL and AJAX. If you face any problem, feel free to ask in the comment’s section below.

[wpdm_package id=’203′]

Custom error handler – PHP

We will be craeting a custom error handler in PHP. As it name suggests, we will be displaying PHP errors in our own style.

Introduction

Error logs means to store a copy of an error that occurred in your website and saving it in a separate file. This helps you to keep track of all the errors in your website. By default PHP will display the error output on the screen. So if you are running a website on live server, it is always a good idea to keep track of any error, if happened.

For example, if a user perform some action that causes an logical error, so it causes a bug in your code. So you must know what and where that error occurred in your script. We can get the error code and the error code must be one of the following as mentioned in table. We can also get the error string which actually tells the error, for example, “undefined variable”, and file name which causes the error and also it’s line number.

Error levels

ValueConstantDescription
1E_ERRORFatal run-time error
2E_WARNINGRun-time warning
4E_PARSECompile-time parsing error
8E_NOTICEError in script/code
256E_USER_ERRORUser-generated error by calling trigger_error($error)
512E_USER_WARNINGUser-generated warning by calling trigger_error($error)
1024E_USER_NOTICECustom error
8192E_DEPRECATEDDeprecated code error
32767E_ALLAny type of error and warning

Following code will create a custom function and tells PHP to use this function instead of using built-in PHP error system. We will be storing logs for each day so the file name must be of today date. For this purpose, we are using PHP date($format) function to dynamically fetch today’s date. Then we are creating a variable to store error level, actual error, file name where error occurred and line number which causes the error.

We have echo the error so it will be visible in the browser, but can skip this line if you do not want to show the error to your users. We are using file_put_contents($filename, $content, $mode) function to save that error in file. You need to create a folder named logs where all error files will be saved. We will be using FILE_APPEND mode which will create a file if not exists, and append the data at the end of file if file of same name already exists.

PHP built-in error handler function

Finally we will call the set_error_handler($custom_function_name) function that tells the PHP to use custom function for handling errors. The parameter will be the name of function that we have just created, and it’s name must be in string. At the end, we have echo a variable which is not yet created, so it will trigger an error. We did this deliberately to generate an error.

<?php

// Create a custom error function
function on_error($error_no, $error, $filename, $linenumber)
{
	// get today date, saving logs for each day
	$today = date("Y-m-d");

	// Creating array for possible errors
	$error_levels = array(
		"1" => "Fatal error",
		"2" => "Warning",
		"8" => "Error",
		"1024" => "Custom error"
	);
	
	// Getting name of error by error level
	$str = $error_levels[$error_no] . ": ";

	// Display file name where error occurred
	$str .= $error . " in " . $filename;

	// Show line number which causes error
	$str .= " at " . $linenumber;

	// Moving to next line
	$str .= "\n";

	// Display error in browser
	// if you do not want to show errors to user,
	// then you can skip this line
	echo $str;

	// save the $str value in file
	file_put_contents("logs/" . $today . ".txt", $str, FILE_APPEND);
}

// Tells PHP to use custom function for errors
set_error_handler("on_error");

// Generating error deliberately
echo $a;

?>

If you are using Mac or Linux, you may also need to enable a folder to write permissions of the logs folder. That’s how you can create and use the custom error handler function in PHP and save errors in files as logs.

You can also enable all error reporting using default PHP functions:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

In Javascript, you can catch all errors using the following code:

window.onerror = function (error, file, line) {
    alert(error + " at line " + line)
}

If you face any problems, feel free to ask in the comments section below.

[wpdm_package id=’201′]

Page level cache PHP

Page level cache in PHP means that the most frequent data will be stored in cache. So instead of requesting the same data from database, we will just use the cache to display that data. This will reduce the overhead on database by eliminating number of queries on SQL. Or any other database server you are using.

Cached data storage

Cached data will be stored in files. So it is advised not to store sensitive data, like passwords and credit cards, in the cache. Although no one will know by which name you have saved the cache. But in some circumstances, it can be readable. So we must save that data in a database which is not sensitive. And we can use page level cache in PHP without any fear.

Once data is cached in a file then whenever a user requests that data it will not be fetched from the database. Instead it will be fetched from the cache. This will decrease the page load time and hence will improve the performance of website. For example, if a database query took 6 seconds to fetch the data. Then using this technique you can skip those 6 seconds wait.

Expiry time of cached data

You can also set the expiry time of the cache. So if you have cached some data and now the user is seeing the data from cached files. Now, what if you made some changes in the database ? The user will still be seeing the old cached data ! That is where we need to set the expiry time of the cache. A common practice is to expire the cache after 24 hours but you can customize it as per your needs.

Display page load time

First, we need to find a way to check the actual page load time. So we can get the time when we were at 1st line of the page and we can get the time when we were at the last line of the page. Then we will get the difference between start and end time and we will know the number of seconds it takes to load the page.

<?php

$start_time = time();

?>

<?php

$end_time = time();
echo "<h1>Difference: " . ($end_time - $start_time) . " seconds</h1>";

Create cache

We set the name of the file where we want to store the cache. You can create a separate folder where all caches will be stored, most modern frameworks use this approach. We also will set the expiry time of the cache so it will fetch fresh data after a specified time period.

We will be using output buffering which is used to hold plain HTML before it is displayed in the browser. Whatever content is displayed between ob_start() and ob_get_contents() will be saved in the file.

Then we create a file using write mode and the content of the file will be plain HTML that is rendered on the webpage. So if you open the cache file, you will see that it will have all plain HTML (same as you see when you do “view page source”). That is why it is recommended not to save sensitive data like passwords and credit cards in the cache. Finally, we can close the file object and when you run the script now, you will be able to see a new file created with HTML content.

<?php

$cache_file = "cache.php";
$cache_time = 60; // 1 minute

// Start output buffering
ob_start();

// run all DB queries here

$file = fopen($cache_file, "w");
fwrite($file, ob_get_contents());
fclose($file);

Read cache

We will read the cache only if the file exists and it has not been expired. When you run the script the first time, then the file has not yet been created. So that means that you do not have any cache, after that we will check the expiry time of the cache using file modified time.

Now if the cache is found and it is not expired, then we will display the content from the cache instead of requesting from database. To read the file we have 2 options, one is to use the include or require function and the second is to use readfile function. However, readfile is more secure than include so we will be using this.

You can use else condition if you want to perform some other action, that depends on your needs. But for simplicity, we will stop the script after reading from the cache using the exit() function. Paste the following code after $cache_time variable and before ob_start() function.

<?php

$cache_time = 10; // seconds

if (file_exists($cache_file) && (filemtime($cache_file) + $cache_time > time()))
{
	readfile($cache_file);
	exit();
}

ob_start();

That’s it, now if you run the script you will see that when it loads from the database, it takes more time than when it does from the cache. The script is tested on multiple already created projects and by average it loads 5 seconds faster than using without cache.

Tired of clearing browser cache after making changes in CSS or JS files ?

Prevent browser cache from CSS, JS, and image files

[wpdm_package id=’199′]

Lazy load images – Javascript

Lazy load images means loading images on websites asynchronously. Lazy loading is technique that defers loading of non-critical resources at page load time. Instead, these non-critical resources are loaded at the moment of need. Where images are concerned, “non-critical” is often synonymous with “off-screen”. If you’ve used Lighthouse and examined some opportunities for improvement, you may have seen some guidance in this realm in the form of the Offscreen Images audit.

Intersection observer API

The Intersection Observer API provides a way to asynchronously observe changes in the intersection of a target element with an ancestor element or with a top-level document’s viewport.

We are going to use 2 images. One will be displayed on top and second will be at the bottom of page. You have to scroll a lot to see the second image. Now you will see that the second image will only be loaded once the user scrolled to the second one. So this will decrease the page load time and hence improves the performance of your website.

First create 2 image tags and give class attribute to access it in Javascript and data-src attribute. It is a custom attribute which we can use to get the actual path of image. DO NOT use src attribute as it will actually load the image, which we do not want. Give both images width as 100% so they will be completely visible inside the browser window.

<!-- Displaying first image -->
<img data-src="image-1.jpg" class="image" alt="1"><br>

<!-- Displaying second image but at bottom -->
<img data-src="image-2.jpg" class="image" alt="2" style="margin-top: 1000px;">

<!-- Image should not overflow the window -->
<style>
	.image {
		width: 100%;
	}
</style>

Load image when scrolled

Then we create a built-in intersection observer which will be called automatically when page is scrolled and will return the items which are visible. We can check if each item is intersection (inside the browser window). We will get the data-src attribute and place it as src attribute, so in this way it will be loaded asynchronously. Once an image is loaded we do not want it to be loaded again, so we will disable it from observing again by calling the unobserve(item) method.

<script>
	// IntersectionObserver is a built-in javascript object
	var observer = new IntersectionObserver(function (items, self) {

		// Loop through all visible items
		for (var a = 0; a < items.length; a++) {

			// Check if item is in visible area of browser
			if (items[a].isIntersecting) {

				// Debug the img tag
				console.log(items[a].target);

				// Get image data-src (custom) attribute
				var src = items[a].target.getAttribute("data-src");

				// Load the image
				items[a].target.setAttribute("src", src);

				// Should not call this function for same image again
				self.unobserve(items[a].target);
			}
		}
	});
</script>

At this point if you run the script, nothing happens. Because we havn’t tell it which items needs to be observed. Since we have given a class attribute to images so we can get all images having that class. Loop through each image and set it to be observed by observer using it’s instance.

<script>
	// Get all images
	var image = document.getElementsByClassName("image");

	// Loop through each image
	for (var a = 0; a < image.length; a++) {
		// Make it observable
		observer.observe(image[a]);
	}
</script>

Conclusion

There are also many libraries available which can do the lazy loading for you. But nothing gives more control than writing your own code. So using this Vanilla JS way you can use intersection observers to lazy load images you want as per required.

[wpdm_package id=’197′]

Use sprites to reduce number of requests on website – Javascript, PHP

Generate sprite image

Sprite is a computer graphics term for a two-dimensional bitmap that is integrated into a larger scene, most often in a 2D video game. Which means that small images are combined into one large image to reduce the number of requests on your server. Sprites helps a lot to reduce the number of HTTP requests.

In CSS sprites, we combine multiple images into a single image. So, if you are using 12 small images in your CSS, it will take 12 requests without sprite. Now if you combine all these images into one image, you will need only 1 request.

The basic principle is, you will combine multiple images into one large image. When you try to access it, the web server will not fetch the new copy of each image everytime you request it. Instead it will fetch just one image which contains all images and you can display them by specifying their position. Same as the image above.

So we will first combine all necessary images into one image and save it as a “sprite.png”. Then we will display the image by telling it’s position. We will be using two files, 1st for combining multiple images into one (save-sprite.php) and 2nd to display the images from sprite.

Generate sprites

First we create an HTML5 canvas tag where all images will be drawn. Give it unique ID to be accessible in Javascript and give it appropriate width and height.

Then we create an array of images which holds width, height and path (src) of each image. We will be looping through this array to automatically draw image on canvas. Also a counter variable to tell the number of current image under process.

Then we get the canvas object and 2D context of canvas. Create a variable named marginLeft to automatically draw the image after the previous one. If this is not specified, then all images will be placed on top of other.

After that, you will create a recursive function that will draw all images inside the array. Inside this function, create a new Image() object and set the image path in src attribute. Call onload function with this image object to wait for the image to fully loaded. The draw the image using drawImage(imageObj, x, y, width, height) function that will render the image on canvas.

Increase margin from left for next image, increment the counter. If there is more images in array then recursion occurs by calling the function itself inside the function. Otherwise if all images are rendered then we will convert that canvas into image and call an AJAX function to save this image data as a sprite.

<!-- Canvas where all images will be drawn -->
<canvas id="myCanvas" width="1000" height="500"></canvas>

<script>

	// An array where all images should be entered
	var images = [{
		width: 308,
		height: 183,
		src: "image1.png"
	}, {
		width: 206,
		height: 260,
		src: "image2.png"
	}];

	// A variable used to get the number of current image
	var count = 0;

	// Get canvas element
	var c = document.getElementById("myCanvas");

	// Get canvas context used for drawing
	var ctx = c.getContext("2d");

	// Give margin to each image so they will be aligned horizontally
	var marginLeft = 0;

	// A recursive function which will keep calling itself
	// until all images are drawn on canvas
	function loadImage() {
		// Create new image object
		var imageObj = new Image();

		// Set the path of image
		imageObj.src = images[count].src;

		// Wait till image fully loaded
		imageObj.onload = function() {
			// Draw image on canvas given image object, x, y, width & height
			ctx.drawImage(imageObj, marginLeft, 0, images[count].width, images[count].height);

			// Increase margin from left
			marginLeft += images[count].width;

			// Increment to next image
			count++;

			// If there is more image to draw
			if (count < images.length) {
				// Recursive occur here
				loadImage();
			} else {
				// All images has been drawn on canvas
				console.log("All images loaded");

				// Convert the canvas into PNG image
				var image = c.toDataURL("image/png", 1);

				// Create AJAX request object
      			var ajax = new XMLHttpRequest();

      			// Set method to POST, file name and asynchnronous
				ajax.open("POST", "save-sprite.php", true);

				// Set headers in POST request
				ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");

				// Send the request and pass image data
				ajax.send("image=" + image);

				// Listen for server request changes
				ajax.onreadystatechange = function () {

					// Request is successful if ready state is 4 and status is 200
					if (this.readyState == 4 && this.status == 200) {
						// Print response sent from server
						console.log(this.responseText);
					}
				};
			}
		}
	}

	loadImage();
</script>

Save sprite image

Create a new file named “save-sprite.php” and paste the following code in it. This will give the image data from client and remove the “base64” part from it as that is not necessary. When using AJAX post request we should also convert all spaces into plus sign and then decode the base64 string. Finally you can save the image as separate file.

<?php

$image = $_POST["image"];
$image = explode(";", $image)[1];
$image = explode(",", $image)[1];
$image = str_replace(" ", "+", $image);

$image = base64_decode($image);
file_put_contents("sprite.png", $image);

echo "Done";
?>

Display image from sprite

To display an image we will create a <div> tag and set the background image as sprite. Given the background position allow you to display specific portion of sprite image. For example, if your sprite has 3 images of 100px width each, then your total sprite will be of 300px in width. To display the second image you have give the background position as -100px so it will move the image from left to right.

<style>
	#home {
		width: 308px;
		height: 183px;
		background: url('sprite.png');
		background-repeat: no-repeat;
	}

	#next {
		width: 206px;
		height: 260px;
		background: url('sprite.png');
		background-repeat: no-repeat;
		background-position: -308px 0px;
	}
</style>
<div id="home"></div>
<br>
<div id="next"></div>

Conclusion

Sprites are very useful specially in video games where optimization and performance is very important. So instead of loading all car’s types (Lamborghini, Bugatti, Ferrari, Mclaren) as 4 images they create a single sprite named “cars-sprite.png” which holds all the images of cars. This is just an example, you can categories the sprites as much as required. The point is to send minimum requests on the server.

Hope that helps you in your upcoming or legacy projects to reduce the number of HTTP requests using sprites. If you face any problem, feel free to ask in the comment’s section below.

[wpdm_package id=’195′]

Search in all tables and columns – PHP & MySQL

Search is one of the feature which is needed in almost every website. Whether you are working on admin panel or user side, you need to provide a functionality where user can search and get data. So, in this article, we will discuss how you can search a string in all tables and columns of a database. We will be using PHP and MySQL.

When it comes to admin panel, then it require to search all tables and all its fields in database for search query. For example, admin wants to know all payments transferred via account number 1234. Then he will simply type 1234 and it automatically scan all tables (including the payment’s table) and display the data.

While in typical environment, you have to manually set the table name along with column name to search. So we will make it to search complete database for searched query.

Create a form

Create a simple form where user can enter string and we will search that string in whole database.

<form method="GET" action="search.php">

    <input name="search" placeholder="Enter query">
    <input type="submit" name="submit">

</form>

Perform the search

Create a new file named “search.php” and paste the following code in it:

<?php

// Check if form submits
if (isset($_GET["submit"]))
{
	// Get searched query
    $search = $_GET["search"];

    // Connect with database
    $conn = mysqli_connect("localhost", "root", "", "classicmodels");
}

?>

First we need to get all tables inside the database. We will be running the MySQL command SHOW TABLES and it will return all tables. Then we will loop through each table. Here we will get object like “Tables_in_dbname” where “dbname” will be your database name. In this case, it will be “Tables_in_classicmodels”.

It will give us the table name which can be used to get all columns (structure) and data inside the table.

$tables = mysqli_query($conn, "SHOW TABLES");
while ($table = mysqli_fetch_object($tables))
{
    $table_name = $table->{"Tables_in_classicmodels"};

    // put below code here
}

Now we need to get all the rows and columns of this table. Since we do not know the number of each table so we will create an array that will store all columns of each table with LIKE clause on each column.

<caption>

We can display column name as caption of each table using <caption> tag. Then we will run the query to get all columns of each table and loop through each column and display that as TH (table heading). After that, we will need the $columns array again when display row data. So we will use the function mysqli_data_seek($columns, 0) and it will move the database cursor of this object to zero.

We also will push the column name in an array with LIKE clause so it will become “WHERE column1 LIKE ‘%value%’ OR column2 LIKE ‘%value%’ “ and so on. We can combine this array into string using implode function and append in $sql variable.

Finally we will run this query and loop through each row returned from it. Inside the row array we also need to run the column array to get all columns of that table. Make sure to call mysqli_data_seek function otherwise you won’t be able to see more than 1 row.

<?php

// put this code inside above while loop

// Create SQL query to get all rows (more on this later)
$sql = "SELECT * FROM " . $table_name . " WHERE ";

// An array to store all columns LIKE clause
$fields = array();

// Query to get all columns from table
$columns = mysqli_query($conn, "SHOW COLUMNS FROM " . $table_name);

?>
	
<table>

	<!-- Display table name as caption -->
    <caption>
        <?php echo $table_name; ?>
    </caption>

    <!-- Display all columns in table header -->
    <tr>
        
		<?php
			// Loop through all columns
			while ($col = mysqli_fetch_object($columns)):

				// Use LIKE clause to search input in each column
				array_push($fields, $col->Field . " LIKE '%" . $search . "%'");

		?>

			<!-- Display column in TH tag -->
			<th><?php echo $col->Field; ?></th>

		<?php
			endwhile;

			// Move cursor of $columns to 0 so it can be used again
			mysqli_data_seek($columns, 0);
		?>
        
    </tr>

    <?php
    	// Combine $fields array by OR clause into one string
		$sql .= implode(" OR ", $fields);
		$result = mysqli_query($conn, $sql);

		// Loop through all rows returned from above query
		while ($row = mysqli_fetch_object($result)):
			?>

			<tr>

				<?php
					// Loop through all columns of this table
					while ($col = mysqli_fetch_object($columns)):
				?>

					<td>

						<?php
							// Display row value from column field
							echo $row->{$col->Field};
						?>

					</td>

				<?php endwhile; mysqli_data_seek($columns, 0); /* end of column while loop */ ?>

			</tr>

		<?php endwhile; /* end of row while loop */ ?>

</table>

So that’s it. Hope that helps you in your almost every project because in every website you will need to allow user to search some string in whole database. If you face any problem, please feel free to ask in the comment’s section below.

Learn how you can recursively search string in all folders in your cPanel’s file manager.

Recursively search string in all folders in cPanel – PHP

[wpdm_package id=’193′]

Recursively search string in all folders in cPanel – PHP

Suppose you are working on cPanel on a very big project, now you want to search some string in all folders and in all of their files in cPanel. There are very few cPanel that provide such functionality to search some specific string in all files in your domain. This script will allow you to do this.

You just needs to put this file in the root folder of your domain where you want to perform search. For the sake of simplicity we are creating a simple form where you will enter the string that needs to be searched and hit enter to do the search.

Creating a form to enter string

<form method="GET" action="search.php">
	<input name="search" placeholder="Enter query" autocomplete="off">

	<input type="submit" name="submit">
</form>

Perform search

Create a recursive function that will search for all files. While calling this function you need to send the root folder path in the first call. If you are using Laravel you need to use Laravel built-in base_path() instead of core PHP dirname(__FILE__). Create a new file named “search.php” and paste the following code in it:

search.php

<?php

	// A recursive function to search all files
	function get_directory_content($directory)
	{
		//
	}

	// Check if form submits
	if (isset($_GET["submit"]))
	{
		// Get searched query
		$search = $_GET["search"];

		// Where all files (where that string is found) will be stored
		$results = array();

		// Send root folder path to start the search
		get_directory_content(dirname(__FILE__));
	}

As we will be using $search and $results variable inside the function so we need to declare them as global variables, otherwise they will not be accessible inside the function. Also we need to get all the files inside the folder which is being passed as parameter, loop through all the files.

Skip “.” and “..”

In Unix file system there are 2 commands inside every folder which are “.” and “..”. The first one means the current directory and second one means the parent directory. In this case we will skip the loop by using continue; command.

global $search, $results;

$files = scandir($directory);
foreach ($files as $file)
{
	if ($file == "." || $file == "..")
	{
		continue;
	}
}

Next, we will check if the incoming name is a directory or a file. If it is a file then we will simply process it for searching text. If it is a directory then we will call the recursive function again to get its files too. In this way it will check for all files no matter how many nested folders you have.

$is_file = false;
$path = realpath($directory . DIRECTORY_SEPARATOR . $file);

if (is_dir($path))
{
	get_directory_content($path);
	$is_file = true;
}
else
{
	$is_file = true;
}

Then we will get the content of that file and check if that searched query exists in that file. stripos(content, search) will search case-insensitively which means that you do not have to worry about small and capital letters.

stdClass in PHP

If that text is found in file then we will create a custom object using built-in PHP stdClass() class. If you are working on Laravel then you need to push back-slash before the class name, for example for Laravel \stdClass() because Laravel classes works in namespaces.

Set the name of file in that object and lastly push that object in the $results array we created in second step.

if ($is_file)
{
	$content = file_get_contents($path);

	if (stripos($content, $search) !== false)
	{
		$obj = new stdClass();
		$obj->file_name = $file;

		array_push($results, $obj);
	}
}

Now we will know the name of file where your searched string is found. But we would also want to know the line number in case you have larger files. Also if you were searching for some variable then it will have multiple occurrences in same file so we need to create an array. Another variable will be created to tell the line number of each occurrence.

In order to check each line number we need to loop through each line of that file. To do that, first open the file in read mode using fopen(“file_name”, “r”) and this will return the file handler which we can use later.

fgets in PHP

fgets(file_handler, length) function will read one line each time it is called and by specifying length paramter it will move to the next line every time it is called. In each iteration, we are increasing the number of line by incrementing the variable with 1.

Then we will use the same function to search but this time for single line and not for whole file. If found then we will push that line number in $lines array.

After the while loop we will assign that lines array to our custom object we created in previous step. So lines array will also be sent along with file name which we did in previous step.

$lines = array();
$line_number = 0;

$file_handler = fopen($path, "r");
while ( ( $line = fgets($file_handler, 4096) ) !== false )
{
	$line_number++;

	if (stripos($line, $search) !== false)
	{
		array_push($lines, $line_number);
	}
}

$obj->lines = $lines;

The full recursive function is given below:

function get_directory_content($directory)
{
	global $search, $results;

	$files = scandir($directory);
	foreach ($files as $file)
	{
		if ($file == "." || $file == "..")
		{
			continue;
		}

		$is_file = false;
		$path = realpath($directory . DIRECTORY_SEPARATOR . $file);

		if (is_dir($path))
		{
			get_directory_content($path);
			$is_file = true;
		}
		else
		{
			$is_file = true;
		}

		if ($is_file)
		{
			$content = file_get_contents($path);

			if (stripos($content, $search) !== false)
			{
				// Laravel, \stdClass()
				$obj = new stdClass();
				$obj->file_name = $file;

				$lines = array();
				$line_number = 0;

				$file_handler = fopen($path, "r");
				while ( ( $line = fgets($file_handler, 4096) ) !== false )
				{
					$line_number++;

					if (stripos($line, $search) !== false)
					{
						array_push($lines, $line_number);
					}
				}

				$obj->lines = $lines;

				array_push($results, $obj);
			}
		}
	}
}

To display the data is quite simple, just call the function and loop through $results array.

<?php

get_directory_content(dirname(__FILE__));

foreach ($results as $result):
?>

	<h1><?php echo $result->file_name; ?></h1>

	<h2>Lines</h2>
	<ul>
		<?php foreach ($result->lines as $line) { ?>
		<li>
			<?php echo $line; ?>
		</li>
		<?php } ?>
	</ul>

<?php endforeach; ?>

Hope this tutorial helps you in search your query string in all folders in your cPanel. If it did not work, or if you are having any error in this, kindly do let us know.

Learn more

Search in all tables and columns – PHP & MySQL

[wpdm_package id=’191′]

Save div as image – Html2Canvas

We will teach you how you can convert your <div> into an image using html2canvas library.

Download html2canvas library

First, you need to download the library called Html2Canvas and paste in your project folder. You can download it from here. After that, paste the JS file in your project and include it via script tag.

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

Then give a unique ID to the div tag whose screenshot you wants to take. After that, create a function in Javascript which will be called when you want to take the screenshot. We will be calling that function on some button press, but you can use it as per your needs.

<script>
	// A function to convert the required div to image
	function doCapture() {
		//
	}
</script>

<button onclick="doCapture();">Capture</button>

Scroll to top

In order to make this library works, your scroll position should be on header of your site. Even if you want to take the screenshot of footer or any other section in the middle. Your header must be visible before converting the div to image. So we will move the scroll to top by simply calling the window.scrollTo(x, y) function. And pass the x, y coordinates as 0 both.

function doCapture() {
	// Move the scroll on top of page
	window.scrollTo(0, 0);
}

Calling html2canvas function

Now we need to call the html2canvas function, it’s first parameter will be the tag which needs to be converted as image. As we already would have given it some unique ID attribute, so we can get it easily by calling document.getElementById(id) function. Lastly the library provides a function called then() which sends a callback when the div has been converted to image successfully. Basically it sends a canvas object and we can get the image data from that canvas. We can call the toDataURL(imageType, quality) function to get the image type. Possible image types are “image/jpeg” or “image/png” and the value of quality parameter ranges from 0 to 1. By setting the value to 0.9 we can get the minimum compression and maximum quality on image.

function doCapture() {
	window.scrollTo(0, 0);

	// Convert the div to image (canvas)
	html2canvas(document.getElementById("capture")).then(function (canvas) {

		// Get the image data as JPEG and 0.9 quality (0.0 - 1.0)
		console.log(canvas.toDataURL("image/jpeg", 0.9));
	});
}

Call AJAX with base64 image

In order to save this image in our server, you need to call an AJAX request and pass this image data as parameter.

function doCapture() {
	window.scrollTo(0, 0);

	html2canvas(document.getElementById("capture")).then(function (canvas) {

		// Create an AJAX object
		var ajax = new XMLHttpRequest();

		// Setting method, server file name, and asynchronous
		ajax.open("POST", "save-capture.php", true);

		// Setting headers for POST method
		ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");

		// Sending image data to server
		ajax.send("image=" + canvas.toDataURL("image/jpeg", 0.9));

		// Receiving response from server
		// This function will be called multiple times
		ajax.onreadystatechange = function () {

			// Check when the requested is completed
			if (this.readyState == 4 && this.status == 200) {

				// Displaying response from server
				console.log(this.responseText);
			}
		};
	});
}

Save file on server

Now create a server file named save-capture.php and paste the following code to save the incoming data as image.

<?php

// Get the incoming image data
$image = $_POST["image"];

// Remove image/jpeg from left side of image data
// and get the remaining part
$image = explode(";", $image)[1];

// Remove base64 from left side of image data
// and get the remaining part
$image = explode(",", $image)[1];

// Replace all spaces with plus sign (helpful for larger images)
$image = str_replace(" ", "+", $image);

// Convert back from base64
$image = base64_decode($image);

// Save the image as filename.jpeg
file_put_contents("filename.jpeg", $image);

// Sending response back to client
echo "Done";

You can also resize the saved image without stretching.

How to resize an image without stretching – PHP

[wpdm_package id=’189′]

Create and read Excel sheets with styles and dynamic data – PHP & MySQL

We will be using PHPSpreadsheet library which provides all the functionality to create and read the Excel sheets in PHP. You can download it via composer, make sure you have downloaded and install the composer in your system. You can download the composer from here. Right now there is no standalone version of this library that is why you must have composer installed in your system. Open command prompt in your project folder, if you are working directly on cPanel then you might need to open terminal from cPanel home page like the screenshot below:

While in your project folder in command prompt run the following command to install the library. If you are using Mac or Linux make sure you have folder permissions enabled because this library will create a lot of files and folders.

composer require phpoffice/phpspreadsheet

Create excel file

Open your file from where you want to run the function to create Excel file and paste the following code in it. We will be using file named index.php:

<?php

// Including all files from library
require "vendor/autoload.php";

// Creating a new sheet in Excel file
$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();

// Create a write object to save the file and pass spreadsheet instance as parameter
$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

// Save the file named "Test.xlsx"
$writer->save("Test.xlsx");

?>

Create Table Row

We will be creating a simple table that will display the data from database. As Excel sheets use row and column address to save the values, also known as Cells. So paste the following code that will create 5 columns in first row of first sheet:

// Get first sheet from file and set value in specified columns respectively
$spreadsheet->getSheet(0)->setCellValue("A1", "Customer Name");
$spreadsheet->getSheet(0)->setCellValue("B1", "Phone");
$spreadsheet->getSheet(0)->setCellValue("C1", "Address Line");
$spreadsheet->getSheet(0)->setCellValue("D1", "Check number");
$spreadsheet->getSheet(0)->setCellValue("E1", "Amount");

Adjust the width of cells

If you run the code now you will see a new Excel file will be created in your project root folder. But all cells will have equal width which makes it difficult to see full content of cell. We need to find a way to make it dynamic so that it will automatically adjust the cell size according to its content. We will be using setAutoSize(bool) function for this purpose as following:

$spreadsheet->getSheet(0)
	->getColumnDimension("A")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("B")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("C")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("D")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("E")
	->setAutoSize(true);

Styling the cells

Now we need to highlight the cells of header so they will be prominent than the others. We will be creating an array that will hold all the styles. For simplicity, we will be setting the background color (fill color) as red and font color as white.

$styles = [
	"fill" => [
		"fillType" => PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
		"startColor" => [ "rgb" => "FF0000" ]
	],
	"font" => [
		"color" => [ "rgb" => "FFFFFF" ]
	]
];
// Set the range of cells where this style should be applied
$spreadsheet->getSheet(0)
	->getStyle("A1:E1")
	->applyFromArray($styles);

Add data in cells from database

We will be using a sample database named classicmodels of course you will be using your own. We will connect with database and run the required query. Then we will loop through all the records returned from that query and create 5 cells in each iteration and after that move to the next row.

// Connecting with database
$conn = mysqli_connect("localhost", "root", "", "classicmodels");

// Executing the required query
$result = mysqli_query($conn, "SELECT * FROM payments INNER JOIN customers ON customers.customerNumber = payments.customerNumber");

// Start from second row
$count = 2;
while ($row = mysqli_fetch_object($result))
{
	// Add required data
	$spreadsheet
		->getSheet(0)
		->setCellValue("A" . $count, $row->customerName);

	$spreadsheet
		->getSheet(0)
		->setCellValue("B" . $count, $row->phone);
	
	$spreadsheet
		->getSheet(0)
		->setCellValue("C" . $count, $row->addressLine1);
	
	$spreadsheet
		->getSheet(0)
		->setCellValue("D" . $count, $row->checkNumber);
	
	$spreadsheet
		->getSheet(0)
		->setCellValue("E" . $count, $row->amount);

	// Move to next row
	$count++;
}

Reading data from cells

You can read the data from any Excel file using IOFactory class and loop through rowIterator. Each row will return all cells in it and you can loop through all cells using cellIterator. To get the cell value you can either call getValue() or getCalculatedValue(). If you are using some formula in your cells then you need to use getCalculatedValue() and it will return the calculated value after performing that function. But you can also use this for normal cells too.

<?php

// Get Excel file
$reader = PhpOffice\PhpSpreadsheet\IOFactory::load("Test.xlsx");

// Get all rows in first sheet
$rows = $reader->getSheet(0)->getRowIterator();

?>

<table>
	<?php
		// Loop through all rows
		// $count will be use to make first table row as heading
		$count = 1; foreach ($rows as $row) {
	?>

		<tr>
			<?php
				// Get all cells in each row
				$columns = $row->getCellIterator();
			?>

			<?php
				// Loop through all cells
				foreach ($columns as $column) {
			?>

				<?php if ($count == 1) { ?>

					<th>
						<?php echo $column->getCalculatedValue(); ?>
					</th>

				<?php } else { ?>

					<td>
						<?php echo $column->getCalculatedValue(); ?>
					</td>

				<?php } ?>

			<?php } ?>
		</tr>

	<?php $count++; } ?>
</table>

Downloading the excel file

In some cases you want to give users the ability to download the Excel file in his computer. After HTML5 it became so easy that you just have to create an anchor tag, set the path of file that needs to be downloaded and give the attribute download.

<a href="Test.xlsx" download="Test.xlsx">Download</a>

So that’s it. Hope you find it super simple to create and read Excel files using PHP and it might help if you ever came across with some project that involve the use of spreadsheets. If you face any problems or have any suggestions, please feel free to tell in the comments section below.

Download with progress bar

However, there is another approach to download the file. You can display a progress bar to know how much of the file has been downloaded. Learn more from here.

[wpdm_package id=’187′]