Code Samples

Saturday, January 6, 2018

XERO API Integration | XERO Public API Integration | XERO Connect Through Public API

Friday, January 5, 2018

Regex Domain Name Validation | Regex URL Validation

Domain Name Validation (Regex-101 Link)
^((https?):\/\/)?([a-zA-Z0-9_][-_a-zA-Z0-9]{0,62}\.)+([a-zA-Z0-9]{1,20})$

URL Validation (Regex-101 Link)

^((https?):\/\/)?([a-z]*\.)+([a-z]+)(?:\/)?([a-z]+\/?)*(?:\?([a-z]+\=?[a-z]*&?)+)?(#[a-z]*)*

JQuery | Resizable Table Example | Table Column Resizing | Resizable Table Columns Using jQuery UI | Resizable table columns with jQuery

Core Part of Code I am Using to Resize Table Columns with Percentage Value

var table = $("#table-css-border-1"), tw = table.width();
table.find("tr").each(function() {
 var tr = $(this), c = 0;
  tr.find("td, th").each(function() {
   var te = $(this), ew = ((te.width() * 100) / tw);
   te.css("width", ew + "%").addClass("resizable column-" + (++c));
    te.data("cc", ".column-" + c);
    te.data("nc", ".column-" + (c + 1));
  });
 tr.find("td:last-child, th:last-child").removeClass("resizable");
});

var r = $("#table-css-border-1 .resizable"), mw = 50;
r.resizable({
  handles: 'e',
  maxWidth: mw,
  start: function(e, ui) {
   var td = $(ui.element);
    var nd = td.next() || td.previous();
    r.resizable( "option", "maxWidth", gw(td) + gw(nd) - mw);
    table.find(td.data("cc")).css("width", "");
    table.find(td.data("nc")).css("width", "");
  },
  stop: function(e, ui) {
   var td = $(ui.element);
    var nd = td.next() || td.previous();
    table.find(td.data("cc")).css("width", (((td.width()+5) * 100)/tw)+"%");
    table.find(td.data("nc")).css("width", ((nd.width() * 100)/tw)+"%");
  }
});
function gw(e) {
 return parseFloat(e.css("width").replace(/px/g, ''));
}

JSFiddle link to play

Get Latest Record In Each MySQL Group | How to select the first/least/max row per group in SQL | Select max, min, last row for each group in SQL without a subquery | MySQL - How To Get Top N Rows per Each Group

First approach (LEFT JOIN)

SELECT s.Name,c1.Id AS Max_Score_ID,c1.Score as Max_Score
FROM Students s
LEFT JOIN Scores c1 ON (c1.Student=s.Id)
LEFT JOIN Scores c2 ON (
  c1.Student=c2.Student 
  AND (c1.Score<c2.Score OR (c1.Score=c2.Score AND c1.Id<c2.Id))
)
WHERE c2.Score IS NULL
ORDER BY c1.Score DESC;

Execution Plan: First image shows statistics, an index is used when performing the above SQL, example created in MySQL-Fiddle. And second image for large data size, its around 8 million rows, and it takes a small amount of time to execute.

Now we will go for second approach (GROUP_CONCAT WITH SUBSTRING_INDEX)

SELECT s.Name,
CAST(SUBSTRING_INDEX(
  GROUP_CONCAT(c1.Id order by c1.Score desc),',',1
) AS DECIMAL(10, 2)) AS Max_Score_ID,
CAST(SUBSTRING_INDEX(
  GROUP_CONCAT(c1.Score order by c1.Score desc),',',1
) AS DECIMAL(10, 2)) as Max_Score
FROM Students s
LEFT JOIN Scores c1 ON (c1.Student=s.Id)
GROUP BY s.Id
ORDER BY Max_Score DESC;

Execution plan: First image show statistics taken from MySQL-Fiddle, used group-concat and substring-index both to calculate (min/max) value and/or other column of the table. Second image show that its also good time effective as it also takes a small amount of time to get min/max value and table size around 8 million rows.

MySQL Fiddle Link

Thursday, December 28, 2017

PHP Round up to specific number | Round to nearest number | Round to next multiple of number

<?php
#Round to the next multiple of 5, exclude the current number
function roundNumberToUp($n, $x = 5)
{
    return round(($n + $x / 2) / $x) * $x;
}
roundNumberToUp(10);    #10
roundNumberToUp(10.2);  #15
roundNumberToUp(11.5);  #15

#Round to the nearest multiple of 5, include the current number
function roundNumberToNearest($n, $x = 5)
{
    return (round($n) % $x === 0) ? round($n) : round(($n + $x / 2) / $x) * $x;
}
roundNumberToNearest(10);   #10
roundNumberToNearest(10.3); #10
roundNumberToNearest(10.5); #15
roundNumberToNearest(11.7); #15

#Round up to an integer, then to the nearest multiple of 5
function roundNumberToNearestAsInteger($n, $x = 5)
{
    return (ceil($n) % $x === 0) ? ceil($n) : round(($n + $x / 2) / $x) * $x;
}
roundNumberToNearestAsInteger(10.3);    #15
roundNumberToNearestAsInteger(11.7);    #15

URL Redirect: Redirecting From of an iFrame

IFrame

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>IFrame Title</title>
    <script type="text/javascript" src="//code.jquery.com/jquery-1.9.1.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            $("button").click(function() {
                window.top.location.href = 'https://pritomkumar.blogspot.com';
            });
        });
    </script>
</head>
<body>
<button type="button">Reload IFrame Parent</button>
</body>
</html>

To modify your iframe embed code, you will add the following attribute inside the opening iframe tag:
sandbox="allow-top-navigation allow-scripts allow-forms"

Container of IFrame

<style type="text/css">
    iframe {
        width: 40%;
        height: 95%;
        margin-left: calc(30%);
        border: 1px solid lightgray;
    }
</style>
<div>
    <iframe src="iframe-html.php" sandbox="allow-top-navigation allow-scripts allow-forms"></iframe>
</div>

Get Latest Record In Each MySQL Group

Table=check_group_by
id   group_by    value
-------------------------
1    1           Value 1
2    1           Value 2
3    1           Value 3
4    2           Value 4
5    2           Value 5
6    3           Value 6

Query will be as like:

SELECT id,group_by,value FROM check_group_by
WHERE id IN (
 SELECT MAX(id) FROM check_group_by GROUP BY group_by
)

SQL Fiddle Example

Pages