Applications.ge

set url parameter(s) from javascript CODE

Usage: set 1 parameter

setUrlParameter({'param_name': 'value1'})

set multiple parameters

setUrlParameter({'param_name': 'value1', 'param_name2': 'value2'})

open the altered url in new tab:

setUrlParameter({'param_name': 'value1'}, true)

get string of updated url instead of redirecting:

let url = setUrlParameter({'param_name': 'value1'}, null, true)

update custom url string instead of using current page url:

setUrlParameter({'param_name': 'value1'}, null, null, 'https://youtube.com')

function setUrlParameter(NameValuePairs, newTab = false, get_string = false, custom_url = false)
{
    let url;
    if(custom_url){
      url = custom_url;
    }else{
      url = window.location.href;
    }
    let hash = location.hash;
    url = url.replace(hash, '');

    for(const ParamNameKey in NameValuePairs){
      if(NameValuePairs.hasOwnProperty(ParamNameKey)){

      
        let paramName = encodeURIComponent(ParamNameKey);
        if (url.indexOf(paramName + "=") >= 0)
        {
            let prefix = url.substring(0, url.indexOf(paramName + "=")); 
            let suffix = url.substring(url.indexOf(paramName + "="));
            suffix = suffix.substring(suffix.indexOf("=") + 1);
            suffix = (suffix.indexOf("&") >= 0) ? suffix.substring(suffix.indexOf("&")) : "";
            url = prefix + paramName + "=" + NameValuePairs[ParamNameKey] + suffix;
        }
        else
        {
        if (url.indexOf("?") < 0)
            url += "?" + paramName + "=" + NameValuePairs[ParamNameKey];
        else
            url += "&" + paramName + "=" + NameValuePairs[ParamNameKey];
        }
      }
    }
    if(newTab == true){
      window.open((url + hash), '_blank');
    }else if(get_string === true){
      return (url + hash);
    }else{
      window.location.href = url + hash;
    }
    
}

by გიორგი უზნაძე

JavaScript

URL

INSERT statement

Inserts a single record into students table. In the

VALUES

clause we list the values in the same order as the columns are defined in the first clause

INSERT INTO `students` (
    `id`, 
    `first_name`, 
    `last_name`, 
    `points`, 
    `mail`, 
    `santa_id`
) 
VALUES (
    NULL, 
    'დავით', 
    'ბერიძე', 
    '54', 
    'david@gmail.com', 
    '10'
);

Inserts several rows with one INSERT statement

INSERT INTO `students` (
    `id`, 
    `first_name`, 
    `last_name`, 
    `points`, 
    `mail`, 
    `santa_id`
) 
VALUES 
	(NULL, 'დავით', 'ბერიძე', '54', 'david@gmail.com', '10'),
	(NULL, 'გელა', 'თავაძე', '54', 'david@gmail.com', '10'),
	(NULL, 'თამარ', 'დავითაშვილი', '54', 'david@gmail.com', '10')
;

We can exclude nullable fields and pass only the necessary fields

INSERT INTO `students` (
    `first_name`, 
    `last_name`, 
    `points`
) 
VALUES (
    'დავით', 
    'ბერიძე', 
    '54'
)

INSERT & SELECT statement

INSERT INTO `students` (`first_name`, `last_name`, `points`) 
SELECT first_name, last_name, points * 1.2 FROM students WHERE id = 3

We can use

sub-query

in INSERT statement. Before inserting the record,

sub-query

gets

gender_id

based on the provided gender name

INSERT INTO students (
    first_name, 
    last_name, 
    points,
    gender_id
) 
VALUES (
    'ილია', 
    'დავითაშვილი', 
    '84',
    (SELECT id FROM genders WHERE name = 'Male')
)

```

by Valeri Tandilashvili

MySQL

INSERT

CREATE keyword

Using

CREATE

`keyword we can create different database objects. Creates database

university

CREATE DATABASE university

Creates table:

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(50)
)

Creates database

user1

which will have password

some-pass

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'some-pass';

by Valeri Tandilashvili

MySQL

SELECT statement

Selects all the fields from

students

table (

means - all fields)

SELECT * 
FROM students

Selects only

first_name

and

last_name

from

students

table (fields are separated by comma

)

SELECT first_name, last_name 
FROM students

WHERE

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
WHERE id = 5

WHERE

clause (combining several conditions with logical AND)

SELECT *
FROM students
WHERE points > 75 AND last_name = 'გელაშვილი'

WHERE

clause (combining several conditions with logical OR)

SELECT *
FROM students
WHERE last_name = 'გელაშვილი' OR last_name = 'გოგუაძე'

Logical OR with logical AND

SELECT *
FROM students
WHERE points > 75 AND (last_name = 'გელაშვილი' OR last_name = 'გოგუაძე')

ORDER BY

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
ORDER BY id DESC

ORDER BY

last_name

ascending

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC

ASC

is optional because it's default value

ORDER BY

with several fields

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC, first_name ASC

ASC

SELECT

with some primitive values

SELECT first_name, last_name, 20 AS age 
FROM students

SELECT

with only primitive values

SELECT 20, 40 AS age 
FROM students

Math operations in

SELECT

statement

SELECT 
    first_name, 
    last_name, 
    points * 1.1 /* + - * / % */ AS points
FROM students

Math operations in

WHERE

clause

SELECT *
FROM students
WHERE points * 1.5 >= 80

Order of the Math operations matters

SELECT 
    first_name, 
    last_name, 
    (points + 10) * 2 AS points
FROM students

Advantages of quotes in alias

SELECT 
    first_name, 
    last_name, 
    (points + 10) * 2 AS 'points calculated'
FROM students

DISTINCT

keyword in SELECT statement

SELECT DISTINCT
    first_name, 
    last_name
FROM students

GROUP BY

clause with several fields (results of the two queries are identical)

SELECT 
    first_name, 
    last_name
FROM students
GROUP BY first_name, last_name

GROUP BY

clause with one field and

COUNT()

function

SELECT 
    last_name,
    COUNT(last_name)
FROM students
GROUP BY last_name

GROUP BY

and

ORDER BY

clauses in one select

SELECT 
    last_name,
    COUNT(last_name) last_name_counter
FROM students
GROUP BY last_name
ORDER BY last_name_counter DESC

LIMIT

clause

SELECT 
    last_name,
    COUNT(last_name) last_name_counter
FROM students
GROUP BY last_name
ORDER BY last_name_counter DESC
LIMIT 2

LIMIT

clause for pagination

SELECT 
    first_name
    last_name
FROM students
ORDER BY points DESC
LIMIT 4, 2

Student with minimum points

SELECT *
FROM students
ORDER BY points
LIMIT 1

Student with minimum points using

MIN()

function

SELECT *
FROM students
WHERE points = (SELECT MIN(points) FROM students)

Student with maximum points

SELECT *
FROM students
ORDER BY points DESC
LIMIT 1

Student with maximum points using

MAX()

function

SELECT *
FROM students
WHERE points = (SELECT MAX(points) FROM students)

Average points grouped by lastname

SELECT 
    last_name,
    AVG(points) average_points
FROM students
GROUP BY last_name
ORDER BY average_points DESC

Select all students with points greater than 70 (comparison operators:

>=

<=

!=

<>

)

SELECT *
FROM students
WHERE points > 70

Select all students with points less than or equal to 70 (using NOT logical operator)

SELECT *
FROM students
WHERE NOT points > 70

Select all students with points between 80 and 90

SELECT *
FROM students
WHERE points >= 80
    AND points <= 90

Select all students with points between 80 and 90 (using BETWEEN AND operator)

SELECT *
FROM students
WHERE points BETWEEN 80 AND 90

Select all students with points more than average

SELECT *
FROM students
WHERE points > (SELECT AVG(points) FROM students)

Select all students where lastname is either 'გელაშვილი' or 'გოგუაძე' with

IN

operator

SELECT *
FROM students
WHERE last_name IN ('გელაშვილი', 'გოგუაძე')

Select all students which lastname is not any of the listed lastnames ('გელაშვილი', 'გოგუაძე') with

NOT IN

operator

SELECT *
FROM students
WHERE last_name NOT IN ('გელაშვილი', 'გოგუაძე')

Select all students which lastname contains 'უა' with

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა%'

Select all students which

lastname

ends with 'უა' using

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა'

Select all students which

lastname

does not contain 'უა' with

NOT LIKE

operator

SELECT *
FROM students
WHERE last_name NOT LIKE '%უა%'

Select all students where

lastname

starts with any two character followed with

უა

and we don't care what the other following symbols are (with

)

SELECT *
FROM students
WHERE last_name LIKE '__უა%'

Select all students where

lastname

is exactly 5 characters long and the second symbol is

უ

SELECT *
FROM students
WHERE last_name LIKE '_უ___'

Select all students where

lastname

is exactly 5 characters, starts with

and ends with

and has exacly any three letters between them

SELECT *
FROM students
WHERE last_name LIKE 'a___y'

Select all students where

lastname

ends with either

ძე

or

შვილი

SELECT *
FROM students
WHERE last_name LIKE '%ძე' OR last_name LIKE '%შვილი'

Select all students where

last_name

contains

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა'

Select all students where

last_name

starts with

გე

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP '^გე'

Select all students where

last_name

ends with

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა$'

Select all students where

last_name

contains

უა

or

ია

SELECT *
FROM students
WHERE last_name REGEXP 'უა|ია'

Select all students where

last_name

contains

უა

or

ია

using square brackets

SELECT *
FROM students
WHERE last_name REGEXP '[იუ]ა'

Select all students where

last_name

ends with any one letter from the range

[ა-უ]

followed by

ა

using square brackets with range

SELECT *
FROM students
WHERE last_name REGEXP '[ა-უ]ა$'

Select all students where

last_name

contains

ვა

or ends with

ია

or starts with

ცი

SELECT *
FROM students
WHERE last_name REGEXP '^ცი|ია$|ვა'

Select all students which has no mail with

IS NULL

operator

SELECT *
FROM students
WHERE mail IS NULL 
    OR mail = ''

Select all students which has no mail using

IFNULL()

function

SELECT *
FROM students
WHERE IFNULL(mail, '') = ''

Select all students which has mail using

IS NOT NULL

operator

SELECT *
FROM students
WHERE mail IS NOT NULL
    AND mail != ''

Select all students which has mail using

NULLIF()

function

SELECT *
FROM students
WHERE NULLIF(mail, '') IS NOT NULL

Select notes with author names (using INNER JOIN)

SELECT students.first_name, notes.*
FROM `students` 
JOIN notes ON notes.student_id = students.id

Select notes with author names (from students using LEFT JOIN)

SELECT students.first_name, notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id

Select notes with author names (from students using RIGHT JOIN)

SELECT 
    students.first_name, 
    notes.*
FROM `notes` 
RIGHT JOIN students ON notes.student_id = students.id

Select notes with author names (from students using LEFT JOIN)

SELECT students.first_name, notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
WHERE notes.id > 0

Select notes with author names (from notes)

SELECT students.first_name, notes.*
FROM `notes` 
LEFT JOIN students ON notes.student_id = students.id
WHERE notes.id > 0

Select only students with notes (using INNER JOIN)

SELECT students.*, COUNT(students.id) notes_count 
FROM `students` 
JOIN notes ON notes.student_id = students.id
GROUP BY students.id
ORDER BY notes_count DESC

Selects only students with notes (using LEFT JOIN and HAVING)

SELECT students.*, COUNT(notes.id) notes_count 
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
GROUP BY students.id
HAVING notes_count > 0
ORDER BY notes_count DESC
LIMIT 2

Select popular notes with its authors and likes counts

SELECT 
    students.first_name, 
    notes.id AS note_id, 
    notes.note, 
    COUNT(note_likes.id) AS likes_count
FROM `notes` 
JOIN students ON notes.student_id = students.id
LEFT JOIN note_likes ON note_likes.note_id = notes.id
GROUP BY notes.id
ORDER BY likes_count DESC

Select only liked notes

SELECT 
    students.first_name, 
    notes.id AS note_id, 
    notes.note, 
    COUNT(note_likes.id) AS likes_count
FROM `notes` 
JOIN students ON notes.student_id = students.id
JOIN note_likes ON note_likes.note_id = notes.id
GROUP BY notes.id
ORDER BY likes_count DESC

Select only liked notes (without JOIN keywords)

SELECT 
    students.first_name, 
    notes.id AS note_id, 
    notes.note, 
    COUNT(note_likes.id) AS likes_count
FROM `notes`, students, note_likes
WHERE notes.student_id = students.id
    AND note_likes.note_id = notes.id
GROUP BY notes.id
ORDER BY likes_count DESC

Select liked notes with likes counts and authors (using sub-query)

SELECT 
    students.first_name, 
    note_id, 
    notes.note,
    liked_notes.likes_count
FROM (  
    	SELECT 
            note_likes.note_id AS note_id,
            COUNT(note_likes.id) AS likes_count
        FROM note_likes
        GROUP BY note_likes.note_id
	) AS `liked_notes`
JOIN notes ON liked_notes.note_id = notes.id
JOIN students ON notes.student_id = students.id
ORDER BY likes_count DESC

by Valeri Tandilashvili

MySQL

compressed .css file generated by VS code live-sass-compiler extension

live-sass-compiler

extension can generate either

compressed

or

expanded

version of

.css

files. Configuration for

expanded

version:

"liveSassCompile.settings.formats": [
    {
        "format": "expanded",
        "extensionName": ".css",
        "savePath": "/css/"
    }
]

Configuration for

compressed

version:

"liveSassCompile.settings.formats": [
    {
        "format": "compressed",
        "extensionName": ".min.css",
        "savePath": "/dist/css/"
    }
]

by Valeri Tandilashvili

Sass

Short Circuiting(&& and ||)

Use ANY data type, return ANY data type, short-circuiting

console.log('---- OR ----');
console. log(3 || 'Jonas');     //result:3
console.log('' || 'Jonas');     //result:Jonas
console.log(true || 0);         //result:true
console.log(undefined || null); //result:null

console.log(undefined || 0 || '' || 'Hello' || 23 || null); //result:Hello
console.log('---- AND ----');
console. log(0 && 'Jonas');     //result:0
console. log(7 && 'Jonas');     //result:Jonas

console.log('Hello' && 23 && null && 'Jonas'); //result:null

by გიორგი ბაკაშვილი

JavaScript

Rest Pattern and Parameters

//SPREAD, because on RIGHT side of =
const arr = [1, 2, ...[3, 4]]

//REST because on LEFT side of =
const [a, b, ...others] = [1, 2, 3, 4, 5];

console.log(a, b, others);   //result: 1 2 (3)[3, 4, 5]

by გიორგი ბაკაშვილი

JavaScript

Arrays

set footer background after only scroll down event

Sets background image only when a user scrolls down (not to load every image at first)

document.addEventListener("scroll", setBG);
function setBG() {
    if (!bgSet) {
        var bgSet=1;
        id('table_footer').style.background='url(/images/footer.jpg?tg)';
        document.removeEventListener("scroll", setBG);
        console.log('fired');
    }
}

by Valeri Tandilashvili

JavaScript

issues when upgrading MySQL from 5.5 to 8.0

Row with datetime value '0000-00-00 00:00:00' is not allowed

INSERT INTO `t_cal_mention` VALUES 
(1, 4, 1426, '0000-00-00 00:00:00', 0)

The insert query will produce the following MySQL error

Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column inserted_at at row 14

Row with date value '0000-00-00' is not allowed

INSERT INTO `t_design` VALUES (5,0,'წმ. დიდმოწამე მარინე','წმ. დიდმოწამე მარინე','წმ. დიდმოწამე მარინე','oi8io_104355.jpg','0000-00-00','')

The insert query will produce the following MySQL error

Error Code: 1292. Incorrect date value: '0000-00-00' for column 'date' at row 14

by Valeri Tandilashvili

MySQL

error: The following untracked working tree files would be overwritten by merge

When trying to pull the latest commits, the error was

error: The following untracked working tree files would be overwritten by merge:
        app/Log.php

The solution that fixed the problem:

git add * 
git stash
git pull

by Valeri Tandilashvili

Git

errors