Different types of Variables

$name = "George";
$age = 25;
$weight = 75; //kg
$height = 1.85; //m

Variables used in expressions

echo "His name is ".$name."\n";
echo "He is ".$age." years old\n";
echo "He weighs around ".$weight."\n";
echo "he is ".$height."m tall \n";

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

a

and ends with

y

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

Sets

timezone

using

php.ini

file

date.timezone = Asia/Tbilisi

Sets

timezone

inside php script file

ini_set('date.timezone', 'Asia/Tbilisi');

- A variable starts with the

$

sign, followed by the name of the variable - A variable name must start with a letter or the underscore character - A variable name cannot start with a

number

- A variable name can only contain alpha-numeric characters and underscores (

A-z

,

0-9

, and

_

)

// Correct Syntax
$variable_name = "Value";

// Incorrect Syntax
Variable_Name = "Value";
$5variable_name = "Value";
$variable-name = "Value";

String

$name = "George";

Integer

$weight = 85; //kg

Float

$height = 1.9; //m

Boolean

$male = true;

Array

$colors = ["Red", "Green", "Blue"];
// Instead of storing them in separate variables like this:
$color1 = "Red";
$color2 = "Green";
$color3 = "Blue";

gettype()

returns a type of a given variable

echo gettype($my_name); // string

Arithmetic operators work with:

integer

and

float

values to perform arithmetical operations. Let's define two variables:

number1

and

number2

and perform these operations

$number1 = 40;
$number2 = 9;

Addition

echo $number1 + $number2 . "\n";

Subtraction

echo $number1 - $number2 . "\n";

Multiplication

echo $number1 * $number2 . "\n";

Division

echo $number1 / $number2 . "\n";

Modulus

echo $number1 % $number2 . "\n";

Prints plain text

echo 'plain text'."\n";

Double quotes can parse variables

echo "Double quotes: $text"."\n";

Single quotes can not parse variables

echo 'Single quotes: $text'."\n";

Single quotes as well as double quotes can be concatenated with variables

echo 'Single quotes - '.$text." - Double quotes"."\n";

Text inside Single quotes can include double quotes as part of the text

echo 'The text includes "double" quotes as part of the text'."\n";

Text inside double quotes can include single quotes as part of the text

echo "Jane's surname is Smith"."\n";

echo can be used as a function

echo ('echo as a function')."\n";

echo can print number without quotes

echo 5 . $number;

echo

can take multiple parameters

echo 'one', 'two', 'three', "\n";

We can print multiple lines of text

echo 'Prints text 
    with multiple
lines';

Single line comment

// $variable = 'Value';

Another single line comment

# $variable = 'Value';

Comment for multiple lines

/*$variable = 'Value';
$second_variable = 'Value 2';
$third_variable = 'Value 3';*/

Inline comment

$number = 5 /* - 5  */ + 5;

set app time zone by configuring

app.php

file in

config

folder . To change

time zone

, modify the value of timezone in

 app.php

file. Here is the appropriate syntax :

 'timezone' => 'Asia/Tbilisi',

"git grep "example" git grep command allows you to search for specific patterns within your Git repository This command will search for the word "example" in all tracked files in your repository and display the file names and the lines where the word is found. Example: Create files

echo "This is an example file" > file1.txt
echo "Another example file" > file2.txt

Add the files

git add file1.txt file2.txt
git commit -m "Initial commit"

Search for the word "example"

git grep "example"

Use a regular expression to search for words starting with "ex":

git grep "ex\w*"

Perform a case-insensitive search for the word "example":

git grep -i "example"