Code Samples

Friday, January 5, 2018

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

Friday, December 22, 2017

Get code line and file name that's executing processing the current function in PHP

function log() {
    try {
        $bt = debug_backtrace();
        $fileAndLine = "";
        for ($i = 0; $i < 10; $i++) {
            $row = $bt[$i];
            if (isset($row["file"]) && isset($row["line"])) {
                $fileAndLine = $row["file"] . "::" . $row["line"];
                $i = 50;
            }
        }
        return $fileAndLine;
    }
    catch (Exception $ex) {
        return "";
    }
}

Thursday, December 21, 2017

Regex For Parse Html Tags

(<(?<tag>\w+)(\s[^>]*)*>(?>[^<])*<\/\k<tag>\s*>)

Test regex online

Tuesday, December 19, 2017

Grails on Groovy: Get Retrieve MySQL Database name from DataSource | Get List of MySQL Tables | Execute MySQL Raw Query

import grails.util.Holders
import org.hibernate.SessionFactory
import org.apache.commons.lang.StringUtils

def dataSource
SessionFactory sessionFactory

String connectionURL = dataSource.targetDataSource.targetDataSource.poolProperties.url
connectionURL = StringUtils.substringAfterLast(connectionURL, '/')
connectionURL = StringUtils.substringBefore(connectionURL, '?')
println(connectionURL)

String databaseName = sessionFactory.currentSession.createSQLQuery("SELECT DATABASE()")
        .setReadOnly(true).setCacheable(false).list().first()
println(databaseName)

String query = "SELECT table_name FROM information_schema.tables WHERE table_schema='$databaseName'".toString()
List list = sessionFactory.currentSession.createSQLQuery(query)
        .setReadOnly(true).setCacheable(false).list()
println(list.size())
println(list)

Pages