/ Gists

Gists

On gists

webp image javascript browser detection

JavaScript ES 6

detection.js #

// https://www.jianshu.com/p/3ab716ee1a2e

  const supportsWebp = ({ createImageBitmap, Image }) => {
      if (!createImageBitmap || !Image) return Promise.resolve(false);

      return new Promise(resolve => {
          const image = new Image();
          image.onload = () => {
              createImageBitmap(image)
                  .then(() => {
                      resolve(true);
                  })
                  .catch(() => {
                      resolve(false);
                  });
          };
          image.onerror = () => {
              resolve(false);
          };
          image.src = 'data:image/webp;base64,UklGRh4AAABXRUJQVlA4TBEAAAAvAAAAAAfQ//73v/+BiOh/AAA=';
      });
  };

  const webpIsSupported = () => {
      let memo = null;
      return () => {
          if (!memo) {
              memo = supportsWebp(window);
              console.log('memo');
          }
          return memo;
      };
  };

  const result = webpIsSupported();

  result()
      .then(res => {
          alert('OK', res);
      })
      .catch(err => {
          alert(err);
      });

  result()
      .then(res => {
          alert('OK', res);
      })
      .catch(err => {
          alert(err);
      });
On gists

sort.php

•
PHP

sort.php #

public function sortInCurrentLanguage(array $items)
	{
		$oldLocale = setlocale(LC_ALL, 0);
		setlocale (LC_ALL, 'cs_CZ.UTF-8');
		uasort($items, 'strcoll');
		setlocale (LC_ALL, $oldLocale);
On gists

Sort in current language

PHP Libs PHP

sort.php #

<?php

public function sortInCurrentLanguage(array $items)
{
		$oldLocale = setlocale(LC_ALL, 0);
		setlocale (LC_ALL, 'cs_CZ.UTF-8');
		uasort($items, 'strcoll');
		setlocale (LC_ALL, $oldLocale);

}
On gists

reveal.js

•
JavaScript

reveal.js #

function reveal() {
        var reveals = document.querySelectorAll(".reveal");
        for (var i = 0; i < reveals.length; i++) {
          var windowHeight = window.innerHeight;
          var elementTop = reveals[i].getBoundingClientRect().top;
          var elementVisible = 150;
          if (elementTop < windowHeight - elementVisible) {
            reveals[i].classList.add("active");
          } else {
            reveals[i].classList.remove("active");
          }
        }
      }
      window.addEventListener("scroll", reveal);
On gists

Simple reveal

JavaScript

reveal.js #

function reveal() {
        var reveals = document.querySelectorAll(".reveal");
        for (var i = 0; i < reveals.length; i++) {
          var windowHeight = window.innerHeight;
          var elementTop = reveals[i].getBoundingClientRect().top;
          var elementVisible = 150;
          if (elementTop < windowHeight - elementVisible) {
            reveals[i].classList.add("active");
          } else {
            reveals[i].classList.remove("active");
          }
        }
      }
      window.addEventListener("scroll", reveal);
On gists

Top N per group - 4 ways

•
MySql MySQL - ArtfulSoftware MySql - advanced

0-structure.sql #

DROP TABLE IF EXISTS test;
CREATE TABLE test( id INT, entrydate DATE );
INSERT INTO test VALUES
( 1, '2007-5-01' ),( 1, '2007-5-02' ),
( 1, '2007-5-03' ),( 1, '2007-5-04' ),
( 1, '2007-5-05' ),( 1, '2007-5-06' ),
( 2, '2007-6-01' ),( 2, '2007-6-02' ),
( 2, '2007-6-03' ),( 2, '2007-6-04' ),
( 3, '2007-7-01' ),( 3, '2007-7-02' ),
( 3, '2007-7-03' );


-- expected result
+------+------------+------+
| id   | entrydate  | rank |
+------+------------+------+
|    1 | 2007-05-01 |    1 |
|    1 | 2007-05-02 |    2 |
|    2 | 2007-06-01 |    1 |
|    2 | 2007-06-02 |    2 |
|    3 | 2007-07-01 |    1 |
|    3 | 2007-07-02 |    2 |
+------+------------+------+
On gists

Group Concat with count

MySql MySQL - ArtfulSoftware

q.sql #

drop table if exists t;
create table t (
  type int(10) ,
  instance int(10) 
) ;
insert into t values 
 (1,4),(1,7),(1,9),(1,10),(2,2),
  (2,3),(2,5),(2,6),(2,8),(3,1),(4,11);

select 
  group_concat(concat(type,'(',qty,')') separator ', ') 
  as list 
from (
  select type, count(*) qty 
  from t 
  group by type
) n
+------------------------+
| list                   |
+------------------------+
| 1(4), 2(5), 3(1), 4(1) |
+------------------------+
On gists

table.sql

•
MySql

table.sql #


League table
from the Artful Common Queries page
Here is a simple soccer league table setup that was developed in the MySQL Forum by J Williams and a contributor named "Laptop Alias". The teams table tracks team ID and name, the games table tracks home and away team IDs and goal totals for each game. The query for standings is built by aggregating a UNION of home team and away team game results:
DROP TABLE IF EXISTS teams, games;
CREATE TABLE teams(
  id int primary key auto_increment,
  tname char(32)
);
CREATE TABLE games(
  id int primary key auto_increment, 
  date datetime, 
  hteam int, 
  ateam int, 
  hscore tinyint,
  ascore tinyint
);

INSERT INTO teams VALUES
(1,'Wanderers'),(2,'Spurs'),
(3,'Celtics'),(4,'Saxons');
INSERT INTO games VALUES
(1,'2008-1-1 20:00:00',1,2,1,0),
(2,'2008-1-1 20:00:00',3,4,0,2),
(3,'2008-1-8 20:00:00',1,3,1,1),
(4,'2008-1-8 20:00:00',2,4,2,1);
SELECT * FROM teams;
+----+-----------+
| id | tname     |
+----+-----------+
|  1 | Wanderers |
|  2 | Spurs     |
|  3 | Celtics   |
|  4 | Saxons    |
+----+-----------+
SELECT * FROM games;
+----+---------------------+-------+-------+--------+--------+
| id | date                | hteam | ateam | hscore | ascore |
+----+---------------------+-------+-------+--------+--------+
|  1 | 2008-01-01 20:00:00 |     1 |     2 |      1 |      0 |
|  2 | 2008-01-01 20:00:00 |     3 |     4 |      0 |      2 |
|  3 | 2008-01-08 20:00:00 |     1 |     3 |      1 |      1 |
|  4 | 2008-01-08 20:00:00 |     2 |     4 |      2 |      1 |
+----+---------------------+-------+-------+--------+--------+

-- Standings query:
SELECT 
  tname AS Team, Sum(P) AS P,
  Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
  SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,
  SUM(Pts) AS Pts 
FROM(
  SELECT 
    hteam Team, 
    1 P,
    IF(hscore > ascore,1,0) W,
    IF(hscore = ascore,1,0) D,
    IF(hscore < ascore,1,0) L,
    hscore F,
    ascore A,
    hscore-ascore GD,
    CASE 
      WHEN hscore > ascore THEN 3 
      WHEN hscore = ascore THEN 1 
      ELSE 0 
    END PTS
  FROM games
  UNION ALL
  SELECT 
    ateam,
    1,
    IF(hscore < ascore,1,0),
    IF(hscore = ascore,1,0),
    IF(hscore > ascore,1,0),
    ascore,
    hscore,
    ascore-hscore GD,
    CASE 
      WHEN hscore < ascore THEN 3 
      WHEN hscore = ascore THEN 1 
      ELSE 0 
    END
  FROM games
) as tot
JOIN teams t ON tot.Team=t.id 
GROUP BY Team 
ORDER BY SUM(Pts) DESC ;
+-----------+------+------+------+------+------+------+------+------+
| Team      | P    | W    | D    | L    | F    | A    | GD   | Pts  |
+-----------+------+------+------+------+------+------+------+------+
| Wanderers |    2 |    1 |    1 |    0 |    2 |    1 |    1 |    4 |
| Spurs     |    2 |    1 |    0 |    1 |    2 |    2 |    0 |    3 |
| Saxons    |    2 |    1 |    0 |    1 |    3 |    2 |    1 |    3 |
| Celtics   |    2 |    0 |    1 |    1 |    1 |    3 |   -2 |    1 |
+-----------+------+------+------+------+------+------+------+------+
Last updated 22 May 2009
On gists

League standings

MySql MySql - advanced

table.sql #

DROP TABLE IF EXISTS teams, games;
CREATE TABLE teams(
  id int primary key auto_increment,
  tname char(32)
);
CREATE TABLE games(
  id int primary key auto_increment, 
  date datetime, 
  hteam int, 
  ateam int, 
  hscore tinyint,
  ascore tinyint
);

INSERT INTO teams VALUES
(1,'Wanderers'),(2,'Spurs'),
(3,'Celtics'),(4,'Saxons');
INSERT INTO games VALUES
(1,'2008-1-1 20:00:00',1,2,1,0),
(2,'2008-1-1 20:00:00',3,4,0,2),
(3,'2008-1-8 20:00:00',1,3,1,1),
(4,'2008-1-8 20:00:00',2,4,2,1);
SELECT * FROM teams;
+----+-----------+
| id | tname     |
+----+-----------+
|  1 | Wanderers |
|  2 | Spurs     |
|  3 | Celtics   |
|  4 | Saxons    |
+----+-----------+
SELECT * FROM games;
+----+---------------------+-------+-------+--------+--------+
| id | date                | hteam | ateam | hscore | ascore |
+----+---------------------+-------+-------+--------+--------+
|  1 | 2008-01-01 20:00:00 |     1 |     2 |      1 |      0 |
|  2 | 2008-01-01 20:00:00 |     3 |     4 |      0 |      2 |
|  3 | 2008-01-08 20:00:00 |     1 |     3 |      1 |      1 |
|  4 | 2008-01-08 20:00:00 |     2 |     4 |      2 |      1 |
+----+---------------------+-------+-------+--------+--------+

-- Standings query:
SELECT 
  tname AS Team, Sum(P) AS P,
  Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
  SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,
  SUM(Pts) AS Pts 
FROM(
  SELECT 
    hteam Team, 
    1 P,
    IF(hscore > ascore,1,0) W,
    IF(hscore = ascore,1,0) D,
    IF(hscore < ascore,1,0) L,
    hscore F,
    ascore A,
    hscore-ascore GD,
    CASE 
      WHEN hscore > ascore THEN 3 
      WHEN hscore = ascore THEN 1 
      ELSE 0 
    END PTS
  FROM games
  UNION ALL
  SELECT 
    ateam,
    1,
    IF(hscore < ascore,1,0),
    IF(hscore = ascore,1,0),
    IF(hscore > ascore,1,0),
    ascore,
    hscore,
    ascore-hscore GD,
    CASE 
      WHEN hscore < ascore THEN 3 
      WHEN hscore = ascore THEN 1 
      ELSE 0 
    END
  FROM games
) as tot
JOIN teams t ON tot.Team=t.id 
GROUP BY Team 
ORDER BY SUM(Pts) DESC ;
+-----------+------+------+------+------+------+------+------+------+
| Team      | P    | W    | D    | L    | F    | A    | GD   | Pts  |
+-----------+------+------+------+------+------+------+------+------+
| Wanderers |    2 |    1 |    1 |    0 |    2 |    1 |    1 |    4 |
| Spurs     |    2 |    1 |    0 |    1 |    2 |    2 |    0 |    3 |
| Saxons    |    2 |    1 |    0 |    1 |    3 |    2 |    1 |    3 |
| Celtics   |    2 |    0 |    1 |    1 |    1 |    3 |   -2 |    1 |
+-----------+------+------+------+------+------+------+------+------+
Last updated 22 May 2009




-- OR

select 
    team, 
    count(*) played, 
    count(case when goalsfor > goalsagainst then 1 end) wins, 
    count(case when goalsagainst> goalsfor then 1 end) lost, 
    count(case when goalsfor = goalsagainst then 1 end) draws, 
    sum(goalsfor) goalsfor, 
    sum(goalsagainst) goalsagainst, 
    sum(goalsfor) - sum(goalsagainst) goal_diff,
    sum(
          case when goalsfor > goalsagainst then 3 else 0 end 
        + case when goalsfor = goalsagainst then 1 else 0 end
    ) score 
from (
    select hometeam team, goalsfor, goalsagainst from scores 
  union all
    select awayteam, goalsagainst, goalsfor from scores
) a 
group by team
order by score desc, goal_diff desc;
On gists

Pivot table (2 zpusoby)

•
MySql MySql - advanced AW

pivot-table.sql #


-- https://diskuse.jakpsatweb.cz/?action=vthread&forum=28&topic=174158
-- Pivotni tabulka

-- Ja
SELECT user_id, (
  SUM(IF (action_type = 'login', 1, 0)) 
) AS 'login', (
  SUM(IF (action_type = 'publish', 1, 0)) 
) AS 'publish', (
  SUM(IF (action_type = 'download', 1, 0)) 
) AS 'download', (
  SUM(IF (action_type = 'upload', 1, 0)) 
) AS 'upload', (
  SUM(IF (action_type = 'comment', 1, 0)) 
) AS 'comment',(
  SUM(IF (action_type = 'share', 1, 0)) 
) AS 'share'
 
FROM ss_stats
GROUP BY user_id
HAVING user_id IS NOT NULL


-- Kajman
SELECT user_id,
       ( Sum(IF (action_type = 'login', pocet, 0)) )    AS 'login',
       ( Sum(IF (action_type = 'publish', pocet, 0)) )  AS 'publish',
       ( Sum(IF (action_type = 'download', pocet, 0)) ) AS 'download',
       ( Sum(IF (action_type = 'upload', pocet, 0)) )   AS 'upload',
       ( Sum(IF (action_type = 'comment', pocet, 0)) )  AS 'comment',
       ( Sum(IF (action_type = 'share', pocet, 0)) )    AS 'share'
FROM   (SELECT user_id,
               action_type,
               Count(*) pocet
        FROM   ss_stats
        GROUP  BY action_type,
                  user_id) pocty
WHERE  user_id IS NOT NULL
GROUP  BY user_id


--- tttt; nejlepsi a nejrychlejsi
SELECT
    user_id,
    logins.count AS 'login', 
    publishes.count AS 'publish', 
    downloads.count AS 'download', 
    uploads.count AS 'upload', 
    "comments".count AS 'comment', 
    shares.count AS 'shares'
FROM 
  users 
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'login' GROUP BY user_id) logins USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'publish' GROUP BY user_id) publishes USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'download' GROUP BY user_id) downloads USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'upload' GROUP BY user_id) uploads USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'comment' GROUP BY user_id) "comments" USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'share' GROUP BY user_id) shares USING(user_id)