Category: Php

MySQL Schema

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int NOT NULL,
  `city` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

REST API

rest_api.php

<?php

header('Access-Control-Allow-Origin: *');

$servername = "localhost";
$username = "diituser";
$password = "%TGBbgt5";
$dbname = "ecom";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}




switch($_SERVER['REQUEST_METHOD']) {
    case 'GET':

        if(isset($_REQUEST['id']) && $_REQUEST['id'] != '') {
            $id = $_REQUEST['id'];
            $sql = "SELECT * FROM users WHERE id = $id";
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
            $records = $stmt->fetch();
        } else {
            $sql = "SELECT * FROM users";
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
            $records = $stmt->fetchAll();
        }
        echo json_encode($records);

    case 'DELETE':

        $data = json_decode(file_get_contents('php://input'), true);

        $id = $data["id"];
        $sql = "DELETE FROM users WHERE id = $id";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        echo json_encode(["result" => "success"]);

    case 'POST':

        $data = json_decode(file_get_contents('php://input'), true);

        $name = $data["name"];
        $age = $data['age'];
        $city = $data['city'];
        $sql = "INSERT INTO `users` (`id`, `name`, `age`, `city`) VALUES (NULL, '$name', '$age', '$city'); ";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        echo json_encode(["result" => "success"]);

    case 'PUT':

        $data = json_decode(file_get_contents('php://input'), true);

        $id = $data['id'];
        $name = $data["name"];
        $age = $data['age'];
        $city = $data['city'];
        $sql = "UPDATE users SET `name` = '$name', age = '$age', city = '$city' WHERE id = $id";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        echo json_encode(["result" => "success"]);
}


$conn = null;

FRONT END

script.js

const api_url = "rest_api.php";
function loadData(records = []) {
  var table_data = "";
  for (let i = 0; i < records.length; i++) {
    table_data += `<tr>`;
    table_data += `<td>${records[i].name}</td>`;
    table_data += `<td>${records[i].age}</td>`;
    table_data += `<td>${records[i].city}</td>`;
    table_data += `<td>`;
    table_data += `<a href="edit.php?id=${records[i].id}"><button class="btn btn-primary">Edit</button></a>`;
    table_data += "&nbsp;&nbsp;";
    table_data += `<button class="btn btn-danger" onclick=deleteData('${records[i].id}')>Delete</button>`;
    table_data += `</td>`;
    table_data += `</tr>`;
  }
  //console.log(table_data);
  document.getElementById("tbody").innerHTML = table_data;
}
function getData() {
  fetch(api_url)
    .then((response) => response.json())
    .then((data) => {
      console.table(data);
      loadData(data);
    });
}
function getDataById(id) {
  fetch(`${api_url}?id=${id}`)
    .then((response) => response.json())
    .then((data) => {
      console.log(data);
      document.getElementById("id").value = data.id;
      document.getElementById("name").value = data.name;
      document.getElementById("age").value = data.age;
      document.getElementById("city").value = data.city;
    });
}
function postData() {
  var name = document.getElementById("name").value;
  var age = document.getElementById("age").value;
  var city = document.getElementById("city").value;
  data = { name: name, age: age, city: city };
  fetch(api_url, {
    method: "POST",
    headers: {
      Accept: "application/json",
      "Content-Type": "application/json",
    },
    body: JSON.stringify(data),
  })
    .then((response) => response.json())
    .then((data) => {
      console.log(data);
      window.location.href = "index.php";
    });
}
function putData() {
  var id = document.getElementById("id").value;
  var name = document.getElementById("name").value;
  var age = document.getElementById("age").value;
  var city = document.getElementById("city").value;
  data = { id: id, name: name, age: age, city: city };
  fetch(api_url, {
    method: "PUT",
    headers: {
      Accept: "application/json",
      "Content-Type": "application/json",
    },
    body: JSON.stringify(data),
  })
    .then((response) => response.json())
    .then((data) => {
      console.table(data);
      window.location.href = "index.php";
    });
}
function deleteData(id) {
  user_input = confirm("Are you sure you want to delete this record?");
  if (user_input) {
    fetch(api_url, {
      method: "DELETE",
      headers: {
        Accept: "application/json",
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ id: id }),
    })
      .then((response) => response.json())
      .then((data) => {
        console.log(data);
        window.location.reload();
      });
  }
}

index.php

<html>

<head>
    <title>Project</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>

<body class="d-flex flex-column h-100 container">
    <header>
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container-fluid">
                <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
                    <div class="navbar-nav">
                        <a class="nav-link active" aria-current="page" href="#">Listing</a>
                        <a class="nav-link" href="add.php">Add New</a>
                    </div>
                </div>
            </div>
        </nav>
    </header>
    <table class="table table-striped table-hover text-center">
        <thead>
            <th>Name</th>
            <th>Age</th>
            <th>City</th>
            <th>Action</th>
        </thead>
        <tbody id="tbody">
        </tbody>
        <tfoot>
        </tfoot>
    </table>
    <footer class="footer mt-auto py-3 bg-light">
        <div class="container text-center">
            <span class="text-muted">right &copy; 2021</span>
        </div>
    </footer>
</body>
<script src="script.js"></script>
<script>
    getData();
</script>

</html>

add.php

<html>

<head>
    <title>Project</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>

<body class="d-flex flex-column h-100 container">
    <header>
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container-fluid">
                <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
                    <div class="navbar-nav">
                        <a class="nav-link" href="index.php">Listing</a>
                        <a class="nav-link active" aria-current="page" href="add.php">Add New</a>
                    </div>
                </div>
            </div>
        </nav>
    </header>
    <h3>Add Document</h3>
    <form onsubmit="return false;">
        <div class="mb-3">
            <label for="name" class="form-label">Name</label>
            <input type="text" class="form-control" id="name" autofocus>
        </div>
        <div class="mb-3">
            <label for="exampleInputPassword1" class="form-label">Age</label>
            <input type="text" class="form-control" id="age">
        </div>
        <div class="mb-3">
            <label for="city" class="form-label">City</label>
            <input type="text" class="form-control" id="city">
        </div>
        <button class="btn btn-primary" onclick="return postData()">Submit</button>
        <a href="index.php" class="btn btn-primary">Cancel</a>
    </form>
    <footer class="footer mt-auto py-3 bg-light">
        <div class="container text-center">
            <span class="text-muted">right &copy; 2021</span>
        </div>
    </footer>
</body>
<script src="script.js"></script>
<script>
</script>

</html>

edit.php

<html>

<head>
    <title>Project</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>

<body class="d-flex flex-column h-100 container">
    <header>
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container-fluid">
                <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
                    <div class="navbar-nav">
                        <a class="nav-link" href="index.php">Listing</a>
                        <a class="nav-link active" aria-current="page" href="add.php">Add New</a>
                    </div>
                </div>
            </div>
        </nav>
    </header>
    <h3>Edit Document</h3>
    <form onsubmit="return false;">
        <input type="hidden" class="form-control" id="id">
        <div class="mb-3">
            <label for="name" class="form-label">Name</label>
            <input type="text" class="form-control" id="name" autofocus>
        </div>
        <div class="mb-3">
            <label for="exampleInputPassword1" class="form-label">Age</label>
            <input type="text" class="form-control" id="age">
        </div>
        <div class="mb-3">
            <label for="city" class="form-label">City</label>
            <input type="text" class="form-control" id="city">
        </div>
        <button class="btn btn-primary" onclick="return putData()">Update</button>
        <a href="index.php" class="btn btn-primary">Cancel</a>
    </form>
    <footer class="footer mt-auto py-3 bg-light">
        <div class="container text-center">
            <span class="text-muted">right &copy; 2021</span>
        </div>
    </footer>
</body>
<script src="script.js"></script>
<script>
    const urlParams = new URLSearchParams(window.location.search);
    const id = urlParams.get('id');
    getDataById(id);
</script>

</html>

Install required packages

sudo apt-get update
sudo apt-get install php-mbstring
sudo apt-get install php-curl
sudo apt-get install php-xml

Install Laravel via composer

composer create-project laravel/laravel example-app
composer install

php artisan --version
php artisan serve

Generating app key

php artisan key:generate 
php artisan key:generate --show

What are namespaces?

Namespaces are a way of encapsulating items.
e.g. In any operating system directories serve to group related files, and act as a namespace for the files within them.

As a concrete example, the file foo.txt can exist in all 4 directories with different contents in each.

Similarly to avoid ambiguity in PHP we have namespaces. Namespaces solve the confusion of having same class name in program.

Let’s understand this with example.

AdminAccount.php

<?php

class AdminAccount {
        public function __construct() {
                echo "Admin Account Controller..." . PHP_EOL;
        }
}

UserAccount.php

<?php

class UserAccount {
        public function __construct() {
                echo "User Account Controller..." . PHP_EOL;
        }
}

index.php

<?php

require("UserAccount.php");
require("AdminAccount.php");

new UserAccount();
new AdminAccount();

---

Now, what if we have the same class name in both files.
This scenario always comes when you use third-party libraries or any framework.

AdminAccount.php

<?php
class Account {
        public function __construct() {
                echo "Admin Account Controller..." . PHP_EOL;
        }
}

UserAccount.php

<?php
class Account {
        public function __construct() {
                echo "User Account Controller..." . PHP_EOL;
        }
}

index.php

<?php

require("UserAccount.php");
require("AdminAccount.php");

new Account();
new Account();

This will create confusion about which instance to be created.
Now let’s resolve this problem using namespaces.

AdminAccount.php

<?php
namespace Admin;

class Account {
        public function __construct() {
                echo "Admin Account Controller..." . PHP_EOL;
        }
}

UserAccount.php

<?php
namespace User;

class Account {
        public function __construct() {
                echo "User Account Controller..." . PHP_EOL;
        }
}

index.php

<?php

require("UserAccount.php");
require("AdminAccount.php");

new User\Account();
new Admin\Account();

To install phpunit framework make sure you have installed composer
If not follow instructions from this link http://codeinsightacademy.com/blog/php/composer/

Installation

Install php-xml
As we are going to use phpunit.xml we need to install php-xml

sudo apt install php-xml
#To install specific version
sudo apt install php7.4-xml

Install mbstring

sudo apt-get install php-mbstring

Create composer.json file in project root directory

{
"autoload" : {}
}

Run one of the composer command

composer dump-autoload -o
OR
composer update

Install sluggable

composer require cviebrock/eloquent-sluggable

Install intl
This is required when you are using Code Igniter framework

apt-get install php-intl
#OR specific version
apt-get install php7.4-intl

Install php unit framework

composer require phpunit/phpunit ^9

Create function

global_functions.php

<?php

function add($x = 0, $y = 0) {
        return $x + $y;
}

index.php

<?php

require_once("vendor/autoload.php");

echo add(5, 6);

composer.json

{
    "autoload": {
            "files": ["global_functions.php"]
    },
    "require": {
        "cviebrock/eloquent-sluggable": "^8.0",
        "phpunit/phpunit": "^9"
    }
}

Run index.php file

php index.php

Write Testcase

phpunit.xml

<?xml version="1.0" encoding="UTF-8"?>

<phpunit bootstrap = "vendor/autoload.php"
 colors = "true">
    <testsuites>
        <testsuite name="Sample test suite">
            <directory>tests</directory>
        </testsuite>
    </testsuites>

</phpunit>

tests/GlobalFunctionsTest.php
NOTE: MAKE SURE TO GIVE test PREFIX TO YOUR TEST FUNCTION

<?php

class GlobalFunctionsTest extends \PHPUnit\Framework\TestCase {
        public function testAdd() {
                $result = add(5, 6);
                $this->assertEquals(11, $result);
        }
}

Get all assertions functions from here
https://phpunit.readthedocs.io/en/9.5/assertions.html

Run TestCase from root directory

./vendor/bin/phpunit

./vendor/bin/phpunit --testdox

project directory structure

tree -I "vendor"

What is INI file

An INI file is a configuration file for computer software that consists of a text-based content with a structure and syntax comprising key–value pairs for properties, and sections that organize the properties.

parse_ini_file function

Parse ini file is a function to parse any configuration file which has key-value pair
This is required when you want to keep all application-level configuration parameters in one place
Maintaining configuration level parameter/variables is easy when you use ini file
You need to make changes in one file and it will reflect changes throughout the application

---

Syntax

parse_ini_file(file, process_sections, scanner_mode)

myapp.ini

#comment goes here

APP_NAME	= UMS
ADMIN_EMAIL	= admin@umsapp.com


#local database credentials
[localdb]
DB_HOSTNAME	= localhost
DB_USERNAME	= root
DB_PASSWORD	= ""
DB_PORT		= 3306
DB_NAME		= ecom


#production database credentials
[proddb]
PRD_DB_HOSTNAME	= localhost
PRD_DB_USERNAME	= root
PRD_DB_PASSWORD	= ""
PRD_DB_PORT	= 3306
PRD_DB_NAME	= ecom

global_functions.php

function getConnection() {
	
	$db = parse_ini_file('config/myapp.ini', true)['localdb'];
	
	$conn = null;
	
	$servername	= $db['DB_HOSTNAME'];
	$dbname		= $db['DB_NAME'];
	$username 	= $db['DB_USERNAME'];
	$password	= $db['DB_PASSWORD'];
	
	try {
	  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);	
	} catch(PDOException $e) {
	  echo "Error: " . $e->getMessage();
	}
	
	return $conn;
}

Ref: https://www.w3schools.com/php/func_filesystem_parse_ini_file.asp

A Dependency Manager for PHP

Composer is an application-level package manager for the PHP programming language that provides a standard format for managing dependencies of PHP software and required libraries. It was developed by Nils Adermann and Jordi Boggiano in 2012, who continue to manage the project.

---

To install composer in windows download and install executable file
https://getcomposer.org/download/

To install in ubuntu

apt install composer

To install latest version of composer on ubuntu

cd ~
curl -sS https://getcomposer.org/installer -o composer-setup.php

sudo php composer-setup.php --install-dir=/usr/bin --filename=composer

OR

sudo php composer-setup.php --install-dir=/usr/local/bin --filename=composer

---

composer.json
Keep this file in the project’s root directory
NOTE: This file is mandatory to in root directory to use composer as composer look for this file when you run dump-autoload

{
	"autoload" : {
		"files" : ["lib/global_functions.php"],
	}
}

lib/global_functions.php

function getMessage() {
	
	echo "Hello World" . PHP_EOL;

}

Create autoload file by running any one following command

composer dump-autoload
composer dump-autoload -o

index.php

<?php
require("vendor/autoload.php");
echo "=====================\n";
getMessage();

---

How to autoload classes using composer

app/admin/Account.php

<?php
namespace App\Admin;

class Account {
	//this is magic method as it will invoked automatically 
        //when you create instance of account
	public function __construct() {
		
		echo "I am from Admin Account" . PHP_EOL;
	}
}

app/user/Account.php

<?php
namespace App\User;

class Account {
	//this is magic method as it will invoked automatically 
	//when you create instance of account
	public function __construct() {
		echo "I am from User Account" . PHP_EOL;
	}	
}

modify composer.json

{
	"autoload" : {
		"files" : ["lib/global_functions.php"],
		"classmap" : ["app"]
	}
}

Once you modify composer.json file run dump-autoload command again to add classmapping in autoload file

composer update

OR

composer dump-autoload -o

modify index.php

<?php

require("vendor/autoload.php");

echo "=====================\n";

getMessage();

echo "=====================\n";

new App\User\Account();

echo "=====================\n";

new App\Admin\Account();

echo "=====================\n";

Ref Code: https://gitlab.com/codeinsightacademy/composer-demo.git

1.Difference Between Char and Varchar data type?

• CHAR Data Type is a Fixed Length Data Type. It can be any value from 0 to 255 bytes.Uses static memory allocation.
  For example, if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example, as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.
• On the other hand, VARCHAR is a variable length Data Type.Maximum lengths can range from 0 to 255 bytes (before MySQL 5.0.3) or from 0 to 65,535 bytes in later versions.Uses dynamic memory allocation.
  For example, if you declare a variable/column of VARCHAR(10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as , VARCHAR(10) so we can store max 10 characters in this column.

2.What all are the Mysql Storage Engines?

• InnoDB-
  InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint. Oracle recommends using InnoDB for tables except for specialized use cases.
•   MyISAM-
  MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used mostly in Web and data warehousing.
• Memory-
  Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted.
• CSV-
  CSV stores data in CSV files. It provides great flexibility because data in this format is easily integrated into other applications.
• Merge-
  Merge operates on underlying MyISAM tables. Merge tables help manage large volumes of data more easily. It logically groups a series of identical MyISAM tables, and references them as one object. Good for data warehousing environments.
• Archive-
  Archive storage engine is optimised for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing and retrieving large amounts of seldom referenced historical, archived data.
• Blackhole-
  The Blackhole storage engine accepts but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally. This storage engine can be used to perform performance tests or other testing.
• Federated-
  Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.

3.$a=3,$b=7 swap values of these two variable without using third variable.
$a = $b – $a;
$b = $b – $a;
$a = $b + $a;
4.Difference between joins and union?
5.What all types of joins are there?
6.Difference between minified js jquery.min.js and jquery.js ?
7.Latest versions of Mysql,PHP,Codeigniter.
8.Differences between older and newer versions.
9.Difference between echo and print?
10.CURL functions?
11.How to set unlimited upload_max_filesize?
12.libarary/helper for xss_clean?
13.Difference between library and helper?
14.Get last executed query and insert id in MySQL with PHP/CodeIgniter.
$this->db->last_query();
$this->db->insert_id();

Sample htaccess file

<Limit GET POST>
order allow,deny
SetEnvIf Remote_Addr ^10\.87\. ban
#allow from 10.87.63.0/8
allow from env=allow
deny from env=ban
</Limit>
#https://www.countryipblocks.net/block-specific-ips-from-your-allow-list-using-setenvif

<IfModule mod_rewrite.c>
  RewriteEngine on
  RewriteRule ^test$ https://google.co.in
  #RewriteRule .* https://google.co.in
    #RewriteRule ^abc/([a-z]*)/([0-9]*)$ xyz.php?name=$1&id=$2 [NC,L,QSA]
    #RewriteCond %{HTTP_HOST} ^reporting.soc.saint-gobain.net [NC] 
    #RewriteCond %{SERVER_PORT} 80 RewriteRule ^(.*)$ https://domain.com/$1 [R,L]
</IfModule>

 

 

Php Script to check bugs and performance:

<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Kolkata');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Asia/Kolkata');

echo date('H:i:s') , " Program Started" , EOL;


// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
echo date('H:i:s') , " Program End" , EOL;

?>