Code Samples

Saturday, May 13, 2017

Responsive CSS - It's Begining

Friday, May 12, 2017

Cakephp 3.X Get Database Table Columns From Model

TableRegistry::get("Test2")->getSchema()->columns()

CakePHP 3.X Get Database Table Name From Model

TableRegistry::get("Test2")->getTable();

CakePHP 3.X Sub Query Example

$sub_query = TableRegistry::get("Test2")->find();
$sub_query->select(["count" => "SUM(Test2.score)"])
->where(["Test2.ref = Test1.id"]);

$query = TableRegistry::get("Test1")->find("All");
$query->select([
"Test1.id", "Test1.name", "Test1.roll",
"score" => $sub_query
]);

$list = $query->all()->toArray();

PHP Calculate Percentage Value Which Includes In Amount Reverse Way

<?php
$amount = 500;
$percentage = 20;
$percentage_value = calculateReversePercentage($amount, $percentage);
echo "$percentage% of $amount (Includes) = $percentage_value";
$percentage_value = calculateForwardPercentage($amount, $percentage);
echo "<br/>$percentage% of $amount (Not Includes) = $percentage_value";

function calculateReversePercentage($amount, $percentage) {
    $percentage = round(($percentage / ((100 + $percentage) / $amount)) * 1, 2);
    return $percentage;
}

function calculateForwardPercentage($amount, $percentage) {
    $percentage = ($amount * $percentage) / 100;
    return $percentage;
}

Which will output as below:
20% of 500 (Includes) = 83.3320% of 500 (Not Includes) = 100

PHP + MySQL transactions examples

MySLQL transactions can be used when you want to make sure all the statements you specify are executed. Only the tables of InnoDB storage engine support transactions.

In a transaction, if at least one statement fails, all the changes will be rolled back and database will be in its initial state (There are some statements that can not be rolled back: Will be discussed at the end).

In web applications, it’s common that you want to either run set of statements or do no change since success of only few statements can cause data corruption.

A transaction is a set of inter-dependent SQL statements that needs to execute in all-or-nothing mode. A transaction is successful if all SQL statements executed successfully. A failure of any statement will trigger the system to rollback to the original state to avoid data inconsistency.

Suppose we have a process where need to execute 3 queries. If an error occurs in the second step, the third step should not continue and first step must reversed. In addition, if an error occurs in the third step, then first and second step must be reversed.

When you use PDO to create a connection to the MySQL database that supports the transaction, the auto-commit mode is set. It means that every query you issue is wrapped inside an implicit transaction.

Notice that not all storage engines in MySQL support transactions e.g., MyISAM does not support the transaction, however, InnoDB does. So you can't use MyISAM engine for MySQL if you want transaction support.

To handle MySQL transaction in PHP, you use the following steps:

Start the transaction by calling the beginTransaction() method of the PDO object.
Place the SQL statements and the commit() method call in a try block.
Rollback the transaction in the catch block by calling the rollBack() method of the PDO object.

Below is a simple PHP script that shows how we can achieve the functionality.

<?php
define("DB_DEFAULT_HOST", "localhost");
define("DB_DEFAULT_DATABASE", "test_database");
define("DB_DEFAULT_USER", "root");
define("DB_DEFAULT_PASSWORD", "");
$dbh = null;
try {
    $dbh = new PDO(
        "mysql:host=".DB_DEFAULT_HOST.";dbname=".DB_DEFAULT_DATABASE,
        DB_DEFAULT_USER,
        DB_DEFAULT_PASSWORD
    );
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $query = $dbh->prepare("SELECT COUNT(*) as count FROM student WHERE id > :id");
    $query->bindValue("id", 0);
    $query->execute();
    $count = $query->fetch()[0];
    echo "OLD COUNT=$count<br/>";

    $stmt = $dbh->prepare("INSERT INTO student (name,roll) VALUES(?,?)");
    $stmt->execute(['Name', 'Roll']);
    /* Will commit data to database, if you don't call this, data will not save */
    $dbh->commit();

    $count = $dbh->query("SELECT COUNT(*) as count FROM student WHERE id > 0")->fetch()[0];
    echo "NEW COUNT=$count<br/>";

    $students = $dbh->query("SELECT * FROM student WHERE id > 0")->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>";

    /* SEARCH CAPABILITY IN ARRAY DATA BIND */
    $id_list = [1, 2, 3];
    $in = rtrim(str_repeat('?,', count($id_list)), ',');
    $query = $dbh->prepare("SELECT * FROM student WHERE id in ($in)");
    $query->execute($id_list);
    $students = $query->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>"; die();
}
catch (\Exception $ex) {
    echo "MySQL_Error=" . $ex->getMessage();
    $dbh->rollBack();
}

And output is like below:

OLD COUNT=2
NEW COUNT=3
Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => Prtiom First Year
            [roll] => P1
        )

    [1] => stdClass Object
        (
            [id] => 2
            [name] => Pritom Second Year
            [roll] => P2
        )

    [2] => stdClass Object
        (
            [id] => 21
            [name] => Some_Name
            [roll] => 3040
        )

)

Note that, with this idea, if a query fails, an Exception must be thrown:

PDO can do that, depending on how you configure it
See PDO::setAttribute
and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
You might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

HTML2PDF Ahead of TCPDF - PHP PDF LIBRARY

Generate PDF from Html is always difficult, because if you want a better look PDF then you must need to spend good amount of time of it. TCPDF is well known to us as a rich PDF library. No problem with it but HTML2PDF is some type of as layer over TCPDF which makes it better. It has great custom page header and footer support.

Below is a code snippet:

require_once(dirname(__FILE__) . '/../vendor/autoload.php');
try {
$html2pdf = new HTML2PDF('P', 'A4', 'en');
$html2pdf->setDefaultFont('Arial');
$html2pdf->writeHTML($html_content);
$html2pdf->Output('PdfName.pdf');
} catch (HTML2PDF_exception $e) {
echo $e;
exit;

}

You can download full example code from link below.
Click here to download.

Below are attached some screenshots:

Pages