Move column data from one table to another

There may be situations when you want to move MYSQL column data from one table to another. 

MySQL Query: 

UPDATE accounts_users, accounts_membership  SET accounts_users.username=accounts_membership.username WHERE accounts_users.user_id=accounts_membership.user_id AND accounts_users.user_id >= 1;

Retrieve duplicate column values in MySQL

How to find duplicate column values in MySQL

Let's see some of the many ways to retrieve duplicate column value from MySQL table. In this example, we are going to find and return duplicate contact names from the contacts table. It is up to you to decide which is applicable according to the scenario.

Approach 1:

SELECT customer_name FROM contacts group by customer_name having count(*) > 1;

OR

SELECT 

    customer_name, 

    COUNT(customer_name)

FROM

    contacts

GROUP BY customer_name

HAVING COUNT(customer_name) > 1;

Result:

Approach 2:

SELECT contact_id, 

   contact_type, 

   contacts.customer_name 

FROM contacts

   INNER JOIN (SELECT customer_name

               FROM   contacts

               GROUP  BY customer_name

               HAVING COUNT(contact_id) > 1) dup

           ON contacts.customer_name = dup.customer_name;

Result:

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;
}

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;

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

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;
}

HTML Scalar Vector Graphics(SVG)

Left/Right Half Circle Using SVG

<svg width="100" height="100">
  <circle cx="50" cy="50" r="50" fill="grey" />
  <path d="M50,0 a1,1 0 100,100 0,0" fill="orange" />
</svg>

Bottom/Top Half Circle Using SVG

<svg width="100" height="100">
  <circle cx="50" cy="50" r="50" fill="grey" />
  <path d="M0,50 a1,1 0 0,0 100,0" fill="orange" />

</svg>