Monday, May 25, 2020

Export to excel using PHP and Jquery

Export to excel using PHP and Jquery

In order to export MySQL table data to excel let's create a HTML page and put two anchor tags, one is to export to excel and the other to export to CSV (Comma Seperated Value). Write a jquery function to submit the form which makes call to the PHP function to export data returned to excel.


HTML Page
<form id="site" method="post" enctype="multipart/form-data">
  <div>
    <a id="studentsExportExcel" href="javascript:void(0)" onclick="exportExcel(this)">Export to excel (.xls)</a>
    <a id="studentsExportCSV" href="javascript:void(0)" onclick="exportExcel(this)">Export to csv</a>
  </div>
  <input type="hidden" value="" id="hidden-type" name="studentsExportType"/>
</form>
Jquery
$(document).ready(function () {
	function exportExcel(obj) {
    	var target = $(obj).attr('id');
    	switch (target) {
        case 'studentsExportExcel':
        case 'studentsExportCSV':
            $('#hidden-type').val(target);
            $('#site').submit();
            $('#hidden-type').val('');
            break;
    	}
	}
});
PHP Function
public function students()
    {
        if(isset($_POST["studentsExportType"]))
        {
            switch($_POST["studentsExportType"])
            {
                case "studentsExportExcel" :
                    // Submission from
                    $filename = "students". date('dMY') .".xls";       
                    header("Content-Type: application/vnd.ms-excel");
                    header("Content-Disposition: attachment; filename=\"$filename\"");
                    students_export_excel();
                    $_POST["studentsExportType"] = '';
                    exit();
                case "studentsExportCSV":
                    $filename = "students". date('dMY') . ".csv";
                    header("Content-type: application/csv");
                    header("Content-Disposition: attachment; filename=\"$filename\"");
                    header("Pragma: no-cache");
                    header("Expires: 0");

                    $handle = fopen('php://output', 'w');
                    $data = get_students();
                    foreach ($data as $data)
                    {
                        fputcsv($handle, $data);
                    }
                    fclose($handle);
                    exit();
                default :
                    die("Unknown action : ".$_POST["action"]);
                    break;
            }
        }
    }


function students_export_excel()
    {
        $heading = false;
        $students = get_students();
        
        if(!empty($students))
          foreach($students as $row) {
            if(!$heading) {
              // display field/column names as a first row
              echo implode("\t", array_keys($row)) . "\n";
              $heading = true;
            }
            echo implode("\t", array_values($row)) . "\n";
          }
        exit;
    }


function get_students()
{
        $servername = "localhost";
        $username = "username";
        $password = "password";
        $dbname = "myDB";
 
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        $sql = "SELECT id, firstname, lastname FROM students";
        $result = $conn->query($sql);
 
        return $result;
}

Monday, May 18, 2020

Sum columns in multiple tables - MySQL

How to sum columns in multiple tables in MySQL

We will see two methods to sum columns from multiple tables. The first method is more preferred than the second method. Therefore consider using first approach.


METHOD 1:
SELECT credit.contact_id, (IFNULL(credit, 0) - IFNULL(debit, 0)) as balance
     FROM (
    (SELECT SUM(credit) as credit, contact_id FROM table_a GROUP BY contact_id) as credit
       LEFT JOIN
    (SELECT SUM(debit) as debit, contact_id FROM table_b GROUP BY contact_id) as debit
     ON debit.contact_id = credit.contact_id);
METHOD 2:
SELECT contact_id, SUM(balance) FROM
    (SELECT contact_id ,credit as balance FROM table_a
       UNION ALL
    SELECT contact_id,- debit as balance FROM table_b
   ) as x
  GROUP BY contact_id;

Saturday, May 2, 2020

Infinite scroll for div element

Infinite scroll loading for div element

This article explains how to incrementally load items as you scroll down div element in the page. In this example a HTML page is created with an unordered list element where the list items returned by the function gets appended. Here difference of the scroll height of the div container and its client height is compared against the scroll top offset value.

HTML Page
<div>
  <div id="containerName" style="overflow: scroll;height: 500px;"></div>
  <div style="text-align: center;"><img alt="Loading..." src="../assets/images/svg/three-dots.svg" class="loading-info" /></div>
  <div id="notifyMessage"></div>
</div>
Jquery
$(document).ready(function () {
  var offset = 0;
  var loading = false;
  $('.loading-info').hide();
  appendItems(offset, loading);
  $(window).scroll(function () {
        if ($('#notifyMessage').html() === "") {
            var divHeight = $('#containerName')[0].scrollHeight - $('#containerName')[0].clientHeight;
            if ($('#containerName').scrollTop() === divHeight) {
                offset += 20; appendItems(offset, loading); } } });
});
function appendItems(offset, loading) {
    if (loading == false) {
        loading = true;
        $('.loading-info').show();
        $.post('/admin/items/append', { 'offset': offset }, function (data) {
            loading = false;
            if (data.trim().length == 0) {
                $('.loading-info').hide();
                $('#notifyMessage').html('<p> End of records</p>');
                return;
            }
            $('.loading-info').hide();
 
            $("#containerName").append(data);
        }).fail(function (xhr, ajaxOptions, thrownError) {
            alert(thrownError);
        })
    }
}
PHP Function
function append()
{
        $list = NULL;
        $offset = $_POST['offset'];
        $servername = "localhost";
        $username = "username";
        $password = "password";
        $dbname = "myDB";
 
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        $sql = "SELECT id, firstname, lastname FROM employees limit $offset, 20";
        $result = $conn->query($sql);
 
        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                $list +=  "<div>id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "</div>";
            }
        } 
        $conn->close();
 
        echo $list;
}

Scroll Loading using Jquery and PHP

Scroll loading using jquery and PHP

This article explains how to incrementally load items as you scroll down the page. In this example a HTML page is created with an unordered list element where the list items returned by the function gets appended.

HTML Page
<div>
  <ul id="containerName"></ul>
  <div style="text-align: center;"><img alt="Loading..." src="../assets/images/svg/three-dots.svg" class="loading-info" /></div>
  <div id="notifyMessage"></div>
</div>
Jquery
$(document).ready(function () {
  var offset = 0;
  var loading = false;
  $('.loading-info').hide();
  appendItems(offset, loading);
  $(window).scroll(function () {
        if ($('#notifyMessage').html() === "") {
            if ($(window).scrollTop() + $(window).height() >= $(document).height() - 100) {
                offset += 20;
                appendItems(offset, loading);
            }
        }
    });
});
function appendItems(offset, loading) {
    if (loading == false) {
        loading = true;
        $('.loading-info').show();
        $.post('/admin/items/append', { 'offset': offset }, function (data) {
            loading = false;
            if (data.trim().length == 0) {
                $('.loading-info').hide();
                $('#notifyMessage').html('<p> End of records</p>');
                return;
            }
            $('.loading-info').hide();
 
            $("#containerName").append(data);
        }).fail(function (xhr, ajaxOptions, thrownError) {
            alert(thrownError);
        })
    }
}
PHP Function
function append()
{
        $list = NULL;
        $offset = $_POST['offset'];
        $servername = "localhost";
        $username = "username";
        $password = "password";
        $dbname = "myDB";
 
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        $sql = "SELECT id, firstname, lastname FROM employees limit $offset, 20";
        $result = $conn->query($sql);
 
        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                $list +=  "<li>id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "</li>";
            }
        } 
        $conn->close();
 
        echo $list;
}

Codeigniter Shield Authorization

Codeigniter Shield Authorization CodeIgniter Shield is the official authentication and authorization framework for CodeIgniter 4. It provide...