Setup Docker on your PHP, MySql and Ngnix web application

Docker is a powerful tool that allows developers to easily create and manage isolated environments for their applications. In this guide, we will walk through the process of setting up a Docker environment for PHP development that includes MySQL, Composer, and Nginx.

Step 1: Install Docker and Docker Compose

The first step is to install Docker and Docker Compose on your machine. You can download the Docker Desktop for Mac or Windows, or for Linux you can follow the instructions for your specific distribution. Here are the instruction for install it on linux ubuntu

To install Docker:

  1. Update the system: sudo apt update
  2. Install dependencies: sudo apt install apt-transport-https ca-certificates curl software-properties-common
  3. Add Docker GPG key: curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
  4. Add Docker repository: sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
  5. Update package index: sudo apt update
  6. Install Docker: sudo apt install docker-ce

To install Docker Compose:

  1. Download the binary: sudo curl -L "https://github.com/docker/compose/releases/download/1.27.4/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
  2. Apply executable permissions: sudo chmod +x /usr/local/bin/docker-compose
  3. Verify the installation: docker-compose --version

Step 2: Create a new directory for your project

Create a new directory for your project and navigate into it. This will be the root of your project and where all of your code and configuration files will be stored.

Step 3: Create a Dockerfile

Create a new file called “Dockerfile” in your project’s root directory. This file will contain the instructions for building your Docker image.

Here is an example of a basic Dockerfile for PHP development:

FROM php:7.4-fpm

RUN apt-get update && apt-get install -y \
    libpq-dev \
    libzip-dev \
    zip \
    unzip

RUN docker-php-ext-install pdo_mysql \
    && docker-php-ext-configure zip --with-libzip \
    && docker-php-ext-install zip

COPY . /var/www/html

WORKDIR /var/www/html

CMD ["php-fpm"]

This Dockerfile starts from the official PHP 7.4 FPM image, installs the necessary dependencies for using PDO with MySQL and the Zip extension, copies the contents of the current directory to the webroot, and sets the working directory to the webroot.

  1. FROM php:7.4-fpm specifies the base image to be used for building the Docker image. In this case, it’s using the PHP 7.4 FPM (FastCGI Process Manager) image from the official PHP Docker repository.
  2. RUN apt-get update && apt-get install -y updates the package list and installs the dependencies listed after it:
    • libpq-dev
    • libzip-dev
    • zip
    • unzip
  3. RUN docker-php-ext-install pdo_mysql installs the PDO (PHP Data Objects) extension for MySQL.
    • docker-php-ext-configure zip --with-libzip configures the zip extension to use libzip.
    • docker-php-ext-install zip installs the zip extension.
  4. COPY . /var/www/html copies all the files in the current directory to the /var/www/html directory in the Docker image.
  5. WORKDIR /var/www/html sets the working directory to /var/www/html, where the application files will be located.
  6. CMD ["php-fpm"] specifies the command to run when the Docker image is started. In this case, it’s running the php-fpm service.

Step 4: Create a docker-compose.yml file

Create a new file called “docker-compose.yml” in your project’s root directory. This file will define the services that make up your application and how they are connected.

Here is an example of a basic docker-compose.yml file for PHP development:

version: '3'
services:
  web:
    build: .
    ports:
      - "9000:9000"
    volumes:
      - .:/var/www/html
  db:
    image: mysql:5.7
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: mydb
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypassword
  nginx:
    image: nginx:latest
    ports:
      - "80:80"
    volumes:
      - .:/var/www/html
      - ./nginx/default.conf:/etc/nginx/conf.d/default.conf

The file docker-compose.yml is used to define and run multi-container Docker applications with Docker Compose. The content in the file describes the services needed to run the application and how they should be configured.

  1. version: '3' specifies the version of the Docker Compose file format used. In this case, it’s using version 3.
  2. services: section defines the services needed for the application:
    • web: section describes the service that runs the web application.
      • build: . specifies that the Docker image should be built using the Dockerfile in the current directory.
      • ports: maps the host’s port 9000 to the container’s port 9000, allowing external access to the web service.
      • volumes: mounts the current directory to the /var/www/html directory in the container, allowing changes to the code to take effect immediately.
    • db: section describes the service that runs the database.
      • image: mysql:5.7 specifies the MySQL 5.7 image to be used for the database service.
      • ports: maps the host’s port 3306 to the container’s port 3306, allowing external access to the database.
      • environment: sets the environment variables for the database service, including the root password, database name, user, and user password.
    • nginx: section describes the service that runs the web server.
      • image: nginx:latest specifies the latest version of the Nginx image to be used for the web server service.
      • ports: maps the host’s port 80 to the container’s port 80, allowing external access to the web server.
      • volumes: mounts the current directory to the /var/www/html directory in the container and the nginx/default.conf file to the /etc/nginx/conf.d/default.conf file in the container, allowing changes to the configuration to take effect immediately.

This file defines three services: web, db, and nginx. The web service uses the Dockerfile in the current directory, maps port 9000 to the host, and mounts the current directory as a volume. The db service uses the official MySQL 5.7 image, maps port 3306 to the host, and sets the necessary environment variables for the root password, database name, user, and password. The nginx service uses the official Nginx image, maps port 80 to the host, and mounts the current

Faceted Search (Layered Search) on MySQL database with example.

Last few day I was searching for the layered search using MySQL. Layered Search is actually a Faceted Search. (Magento, a highly professional e-commerce platform on php name it Layered Search). Faceted Search can be done using two way MySQL/Any DB Application or using Apache Solr.

In this post I will show you how we can do Faceted search using MySQL database. You need a specific database schema, but it’s feasible. Here’s a simple example:

product Table

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
|  2 | red paint  |
+----+------------+

classification Table

+----+----------+
| id | name     |
+----+----------+
|  1 | color    |
|  2 | material |
|  3 | dept     |
+----+----------+

product_classification Table

+------------+-------------------+-------+
| product_id | classification_id | value |
+------------+-------------------+-------+
|          1 |                 1 | blue  |
|          1 |                 2 | latex |
|          1 |                 3 | paint |
|          1 |                 3 | home  |
|          2 |                 1 | red   |
|          2 |                 2 | latex |
|          2 |                 3 | paint |
|          2 |                 3 | home  |
+------------+-------------------+-------+

So, say someones search for paint, you’d do something like:

SELECT p.* FROM product p WHERE name LIKE '%paint%';

This would return both entries from the product table.

Once your search has executed, you can fetch the associated facets (filters) of your result using a query like this one:

SELECT c.id, c.name, pc.value FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
   LEFT JOIN classification c ON c.id = pc.classification_id
WHERE p.name LIKE '%paint%'
GROUP BY c.id, pc.value
ORDER BY c.id;

This’ll give you something like:

+------+----------+-------+
| id   | name     | value |
+------+----------+-------+
|    1 | color    | blue  |
|    1 | color    | red   |
|    2 | material | latex |
|    3 | dept     | home  |
|    3 | dept     | paint |
+------+----------+-------+

So, in your result set, you know that there are products whose color are blue and red, that the only material it’s made from is latex, and that it can be found in departments home and paint.

Once a user select a facet, just modify the original search query:

SELECT p.* FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
WHERE 
   p.name LIKE '%paint%' AND (
      (pc.classification_id = 1 AND pc.value = 'blue') OR
      (pc.classification_id = 3 AND pc.value = 'home')
   )
GROUP BY p.id
HAVING COUNT(p.id) = 2;

So, here the user is searching for keyword paint, and includes two facets: facet blue for color, andhome for department. This’ll give you:

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
+----+------------+

So, in conclusion. Although it’s available out-of-the-box in Solr, it’s possible to implement it in SQL fairly easily.

 

Thanks stackoverflow.com

Bulk Email Script in PHP and MySQL Database

In this post, I will show you how we can send an email to multiple receivers using simple PHP script. This script also shows how we can send bulk if we have server limitation on number of emails can be sent in period of time.  Many web hosting provider do not support bulk emails so I have set this script to come out of this limitation.

This Bulk Email script can send HTML email. You can also use this script to send promotional and marketing emails.

In this tutorial we will create three files. First file is HTML content of email, second file is the actual code which sends emails to multiple address one at a time and third file is allowing receivers to unsubscribe from email list.

sample.html

<p>Hello there</p>
<p>This is sample email file</p>
<br>
<p>Thanks,</p>
<p>Administrator</p>

sendmail.php

<?php

$con = mysql_connect("localhost","dbuser","dbpass"); // replace dbuser, dbpass with your db user and password
mysql_select_db("dbname", $con); // replace dbname with your database name
/*
To use this script database table must have three fields named sno, email and sub_status
*/
$query = "select sno, email from dbtable where sub_status = 'SUBSCRIBED'";
$result = mysql_query($query, $con);
$emails = array();
$sno = array();
while($row=mysql_fetch_assoc($result)){
	$sno[] = $row['sno']; // this will be used to unsubscribe the user
	$emails[]=$row['email']; // email id of user
}
/* you can also get email id data from CSV using below code */
//$file =  file_get_contents("travel_database.csv"); 
//$emails = explode(",",$file);

/* count.txt is used to store current email sent number/count */
$count =  file_get_contents("count.txt");
for($i=$count;$i<count($emails);$i++)
{
	$to  = $emails[$i];

	// subject
	$subject = 'Set Your Title Here';

	// message
	$message = file_get_contents("sample.html"); // this will get the HTML sample template sample.html
	$message .= '<p><a href="http://yourdomain.com/path-to-folder/unsubscribe.php?id='.$sno[$i].'&username='.$emails[$i].'">Please click here to unsubscribe.</a></p>
	</body>
	</html>';
	// To send HTML mail, the Content-type header must be set
	$headers  = 'MIME-Version: 1.0' . "\r\n";
	$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

	// Additional headers
	//$headers .= "To: $to" . "\r\n";
	$headers .= 'From: Name <info@yourdomain.com>' . "\r\n";
	//$headers .= 'Cc: sendcc@yourdomain.com' . "\r\n";
	//$headers .= 'Bcc: sendbcc@yourdomain.com' . "\r\n";

	// Mail it
	if(mail($to, $subject, $message, $headers)) {
		$file = fopen("mailsentlist.txt","a+"); // add email id to mailsentlist.txt to track the email sent
		fwrite($file, $to.",\r\n");
		fclose($file);
	}
	else
	{
		$file = fopen("notmailsentlist.txt","a+"); // add email to notmailsentlist.txt here which have sending email error
		fwrite($file, $to.",\r\n");
		fclose($file);
	}

	if(($i-$count)>=200) // this will send 200 mails from database per execution
	{	
		$filec = fopen("count.txt",'w'); // store current count to count.txt
		fwrite($filec, $i);
		fclose($filec);
		break;
	}
}//for end
$filec = fopen("count.txt",'w'); // store fine count to count.txt this will be used as a start point of next execution
fwrite($filec, $i);
fclose($filec);

Replace “http://yourdomain.com/path-to-folder/” with your path to unsubscribe.php

You can set the cron job on sendmail.php on particular time frame. For example if you hosting provider support only 100 mail per hour than you can set cron job par hour and update the value here

if(($i-$count)>=100) // update this in code of sendmail.php

unsubscribe.php

<?php
$con = mysql_connect("localhost","dbuser","dbpass");
mysql_select_db("dbname", $con);

$sno = (integer)$_GET['id'];
$email = mysql_real_escape_string($_GET['username']);

$query = "update tablename set sub_status = 'UNSUBSCRIBED' where sno = $sno and email = '$email'";
mysql_query($query);
echo "You have Successfully unsubscribed. Thank you for using the service.";

 

Comment here if you have any queries. 

How to Improve MySQL Large Database Performance

Hello friends,

This is my first post for the blog. Recently I have worked on two large database projects on   backend MySQL Database. During the period of the project I came across with many problems and somehow I managed them all.

Many of you also came across with the same MySQL Database problem with Performance. Query is not running well etc. During this period  I read many blogs and forums to solve these problems. I have figured out some tips or can say some points which we should taken care of.

Database Engine (MYISAM vs. Innodb)

Both MySQL Database engines have its own pros and cons that we have to decide which type of db engine will work great for us. Below are some concerns with this two engines:

MYISAM

  • MYISAM use less memory
  • It allows full text search
  • It locks the table while writing
  • It is useful for application in which reading is high and fewer write

InnoDB

  • It uses more memory
  • It does not support full text search
  • InnoDB provides faster performance
  • It lock the table at row level while writing to the table
  • It works great for the application which make extensive use of read and write both

 

Good Database Design

Good database design is a backbone for the application performance. Bad design makes application performance less. Table must be normalized. Data Structure is the main factor which must be developed carefully. Every Developer must give time for each table and fields for making good design. You should give proper data type to each field. When you done with database creation, you would like to see what is MySQL suggest you for your database table. Here is the Command which can help you to get this information:

ANALYZE TABLE <table-name>;

You can find full description here: http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html

 Indexes

What many of us is knowing that indexes help us to increase the speed of query. Many times indexes create confusion in mind. Creating of proper indexes for the table is necessary but do make table overhead of indexes as indexes take space on the disk. So it increases the workload on the disk. Working on my project I many time need to add and delete column from large table which takes so much time. For increasing performance we can make index ON of OFF. so before whenever I start my database operation I make the index key off and do the operation when operations completed I again makes indexes ON. So rather than making new indexes on my each operation, it will make new index when I male Index on so its only one time. for turning on and index I use below syntax:

Disable Indexes:  ALTER TABLE table_name DISABLE KEY
Enable Indexes: ALTER TABLE table_name ENABLE KEY

 

Tuning MySQL

Hardware makes as important role as other things in database tuning. Our hardware needs just as much attention and tuning as our database and script does. It is also need to check MySQL Configuration file what type of changes we have make to the configuration. There is one tool available which is perl script. You can download and Install on server It tells that which type of configuration change you can make improve the performance.

 

Note: This are the simple things which I feel good to use and take care for better performance.