Temporary tables
A temporary table holds results for the rest of the session, then disappears.
Keep the totals per customer for later queries.
CREATE TEMPORARY TABLE temp_sales AS
SELECT customer_id, SUM(order_amount) AS total_sales
FROM orders
GROUP BY customer_id;Common Table Expressions (CTE)
A CTE names a result set for one SELECT, INSERT, UPDATE or DELETE.
Customers who spent more than 1000.
WITH sales AS (
SELECT customer_id, SUM(order_amount) AS total_sales
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_sales
FROM sales
WHERE total_sales > 1000;Recursive CTE
A recursive CTE walks a tree, like an org chart or folders.
Everyone under the top manager, with their level.
WITH RECURSIVE hierarchy AS (
SELECT employee_id, manager_id, employee_name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name, h.level + 1
FROM employees AS e
INNER JOIN hierarchy AS h ON e.manager_id = h.employee_id
)
SELECT * FROM hierarchy;Window functions
A window function calculates over rows related to the current row, without grouping them away.
A running total of sales per customer.
SELECT customer_id, order_date, order_amount,
SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;The latest order of each customer.
SELECT *
FROM (
SELECT orders.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
) AS ranked
WHERE rn = 1;Stored procedures
A stored procedure keeps SQL with logic in the database, to call again later.
Add a customer and get the new id back.
DELIMITER //
CREATE PROCEDURE add_customer (IN p_name VARCHAR(50), IN p_email VARCHAR(50), OUT p_customer_id INT)
BEGIN
INSERT INTO customers (name, email) VALUES (p_name, p_email);
SET p_customer_id = LAST_INSERT_ID();
END //
DELIMITER ;
CALL add_customer('Ann', 'ann@example.com', @id);
SELECT @id;Triggers
A trigger runs SQL by itself when rows in a table change.
Log every status change of an order.
DELIMITER //
CREATE TRIGGER log_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF NOT (OLD.status <=> NEW.status) THEN
INSERT INTO order_audit (order_id, old_status, new_status, changed_at)
VALUES (OLD.order_id, OLD.status, NEW.status, NOW());
END IF;
END //
DELIMITER ;Functions
Your own function, usable in queries like a built-in one.
A 10% discount on orders above 500.
DELIMITER //
CREATE FUNCTION calculate_discount (total_amount DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
IF total_amount > 500 THEN
RETURN total_amount * 0.1;
END IF;
RETURN 0;
END //
DELIMITER ;
SELECT order_id, calculate_discount(order_amount) FROM orders;Event scheduler
An event runs SQL at set times. Several statements need BEGIN ... END: without it only the first belongs to the event, and the rest runs right away.
Move orders older than a year to an archive, every month.
DELIMITER //
CREATE EVENT archive_old_orders
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
DECLARE cutoff DATE DEFAULT CURDATE() - INTERVAL 1 YEAR;
START TRANSACTION;
INSERT INTO archived_orders
SELECT * FROM orders WHERE order_date < cutoff;
DELETE FROM orders WHERE order_date < cutoff;
COMMIT;
END //
DELIMITER ;
-- Events only run with the scheduler on (the default in MySQL 8):
SHOW VARIABLES LIKE 'event_scheduler';Partitioning
Partitioning splits a big table into parts, so queries on one range read less. The partition column must be in every unique key, the primary key included.
Orders split by year.
CREATE TABLE orders (
order_id INT NOT NULL,
customer_id INT,
order_amount DECIMAL(10, 2),
order_date DATE NOT NULL,
PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2025),
PARTITION p2 VALUES LESS THAN MAXVALUE
);Indexes
An index makes lookups on a column fast, at the cost of slower writes.
An index on email, for searches by email.
CREATE INDEX idx_email ON customers (email);One index for queries that filter on customer_id and sort by order_date.
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);Query optimization
EXPLAIN shows how MySQL runs a query: which index it uses and how many rows it reads.
Check that a query uses an index.
EXPLAIN SELECT * FROM orders WHERE order_date > '2026-01-01';Run it and show where the time went (MySQL 8.0.18+).
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2026-01-01';Query hints
An optimizer hint tells MySQL how to run one query.
Stop a query after 1 second.
SELECT /*+ MAX_EXECUTION_TIME(1000) */ *
FROM orders
WHERE order_amount > 100;Generated columns
A generated column is computed from other columns. VIRTUAL computes it when read, STORED saves it on write (and can be indexed like any column).
The price with tax, computed by MySQL.
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
price DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);Full-text search
A full-text index searches words in long text, faster and smarter than LIKE '%word%'.
Index the product descriptions and search them.
CREATE FULLTEXT INDEX idx_description ON products (description);
SELECT * FROM products
WHERE MATCH(description) AGAINST('search term');Spatial data
MySQL has types and functions for points, lines and areas.
Store a point and read it back.
CREATE TABLE locations (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
coordinates POINT
);
INSERT INTO locations (name, coordinates)
VALUES ('Location A', ST_GeomFromText('POINT(1 1)'));
SELECT id, name, ST_AsText(coordinates) FROM locations;Caching
MySQL 8 has no query cache any more (removed in 8.0). Cache results in the application (Redis, files, APCu) and give InnoDB enough memory for its buffer pool.
See and set the InnoDB buffer pool size.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1 GBNormalization
Normalization stores each fact once: no repeated customer data in every order row, but a reference to the customer.
Customers and orders in separate tables, linked by a foreign key.
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(255)
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(10, 2),
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);Users and privileges
Give each application its own user with only the rights it needs. Since MySQL 8, CREATE USER first, then GRANT.
A user that can only read and insert in one database.
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';Encrypted connections
TLS protects data between the application and MySQL. These lines go in the server config file (my.cnf).
Turn on TLS and require it.
# my.cnf
[mysqld]
ssl_ca=/path/to/ca.pem
ssl_cert=/path/to/server-cert.pem
ssl_key=/path/to/server-key.pem
require_secure_transport=ONBackup and restore
A backup is only a backup once you have restored it once.
Back up one database with mysqldump, then restore it.
mysqldump -u username -p --single-transaction database_name > backup.sql
mysql -u username -p database_name < backup.sqlSlow query log
The slow query log lists queries that take longer than a limit.
Log queries slower than 2 seconds.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;SELECT
SELECT gets rows from one or more tables.
All columns of all customers.
SELECT * FROM customers;The names and emails of all customers.
SELECT name, email FROM customers;Filter, sort and limit
WHERE filters rows, ORDER BY sorts them and LIMIT takes the first few.
The 10 newest orders above 100.
SELECT order_id, order_amount, order_date
FROM orders
WHERE order_amount > 100
ORDER BY order_date DESC
LIMIT 10;GROUP BY and HAVING
GROUP BY makes one row per group for functions like COUNT, SUM and AVG. HAVING filters the groups.
Customers with more than 5 orders.
SELECT customer_id, COUNT(*) AS order_count, SUM(order_amount) AS total
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;INSERT
INSERT adds new rows to a table.
Add a new customer.
INSERT INTO customers (name, email, date_of_birth)
VALUES ('John Doe', 'john.doe@example.com', '1980-05-15');UPDATE
UPDATE changes existing rows. Without WHERE it changes every row.
Change the email address of one customer.
UPDATE customers
SET email = 'john.newemail@example.com'
WHERE customer_id = 42;DELETE
DELETE removes rows. Without WHERE it removes every row.
Remove one customer.
DELETE FROM customers
WHERE customer_id = 42;Subqueries
A subquery is a SELECT inside another query.
Customers with an example.com email address, with the name of their best friend.
SELECT
customers.name,
customers.email,
(
SELECT best_friend.name
FROM customers AS best_friend
WHERE best_friend.customer_id = customers.best_friend_id
) AS best_friend_name
FROM customers
WHERE SUBSTRING_INDEX(customers.email, '@', -1) = 'example.com';Customers who ordered something in 2026.
SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2026
);JSON data
MySQL can store JSON in a column and query inside it. Here customer_details is a JSON column.
The city from the JSON data.
SELECT customer_details->>'$.address.city' AS city
FROM customers;Search with LIKE and dates
% in LIKE matches any text, _ one character.
Customers whose names start with 'J' and who are older than 30.
SELECT name, email
FROM customers
WHERE name LIKE 'J%'
AND date_of_birth < CURDATE() - INTERVAL 30 YEAR;Joins
Joins combine rows from two or more tables on a related column. A plain JOIN is an INNER JOIN.
Customers who placed orders, with those orders.
SELECT customers.name, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;LEFT JOIN
LEFT JOIN keeps every row of the left table. Where the right table has no match, its columns are NULL.
All customers, with their orders if they have any.
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;RIGHT JOIN
RIGHT JOIN keeps every row of the right table. Where the left table has no match, its columns are NULL.
All orders, with the customer if there is one.
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;FULL JOIN
A full join keeps every row of both tables. MySQL has no FULL JOIN: combine a left and a right join with UNION (PostgreSQL, SQL Server and SQLite 3.39+ do have FULL JOIN).
All customers and all orders, matched where possible.
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;CROSS JOIN
CROSS JOIN combines each row of the first table with each row of the second.
Every combination of customer and order.
SELECT customers.name, orders.order_id
FROM customers
CROSS JOIN orders;Self join
A self join joins a table with itself, under two aliases.
Pairs of customers in the same city (each pair once).
SELECT a.name AS customer1, b.name AS customer2, a.city
FROM customers AS a
JOIN customers AS b ON a.city = b.city AND a.customer_id < b.customer_id;NULL
NULL means missing or unknown. Compare with IS NULL and IS NOT NULL: = NULL is never true.
Customers who have not placed any orders.
SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;Orders without a customer.
SELECT order_id
FROM orders
WHERE customer_id IS NULL;Customers who gave an email address.
SELECT name
FROM customers
WHERE email IS NOT NULL;A default for NULL.
SELECT name, COALESCE(city, 'unknown') AS city
FROM customers;Basic
HTML document
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title></title>
<link rel="stylesheet" href="style.css">
<script src="script.js" defer></script>
</head>
<body>
</body>
</html>jQuery
<script
src="https://code.jquery.com/jquery-3.7.1.min.js"
integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo="
crossorigin="anonymous"></script>Elements
<link rel="stylesheet" href=""><script src="" defer></script>defer: runs after the page is parsed<div></div><h1></h1><p></p><span></span><a href=""></a><strong></strong>important (bold)<em></em>emphasis (italic)<b></b>bold, no meaning<i></i>italic, no meaning<u></u>underline<s></s>strike-through<img src="" alt="">alt describes the image; empty for decoration<hr>horizontal linePage layout
<header></header>
<nav></nav>
<main></main>
<footer></footer>Lists
Ordered list
<ol>
<li></li>
<li></li>
<li></li>
</ol>Bullet list
<ul>
<li></li>
<li></li>
<li></li>
</ul>Media
Video
<!-- autoplay only works with muted -->
<video controls autoplay muted loop playsinline poster="img.jpg" preload="metadata">
<source src="video.mp4#t=4" type="video/mp4">
</video>Forms
<form action="" method="post"></form><button type="submit"></button><label for="id"></label>every input needs a label<output></output>Input text
<label for="firstname">First name</label>
<input
id="firstname"
type="text"
name="firstname"
pattern="[A-Za-z ]+"
placeholder=""
size="40">Input number
<input
type="number"
name="amount"
value="33"
min="0"
max="100"
step="1">
<!-- readonly: not editable, still sent with the form -->
<!-- disabled: not editable and not sent -->Select
<select name="cars">
<option value="volvo">Volvo</option>
<option value="saab">Saab</option>
<option value="fiat">Fiat</option>
<option value="audi">Audi</option>
</select>Textarea
<textarea name="message" rows="10" cols="30">
The cat was playing in the garden.
</textarea>Slider
<input
id="slider"
type="range"
min="0"
max="100"
step="10">Checkbox and radio
<label><input type="checkbox" name="news" checked> Newsletter</label>
<label><input type="radio" name="size" value="s"> Small</label>
<label><input type="radio" name="size" value="l"> Large</label>Functions
attr()The value of an attribute, e.g. content: attr(title);calc(100% - 100px)Mix unitsclamp(1rem, 2.5vw, 2rem)Min, preferred, maxmin(100%, 600px)The smaller one (max() for the larger)var(--color1)With :root{ --color1: red; }Selectors
X Y{ }Y anywhere inside XX > Y{ }Y directly inside XX + Y{ }Y right after XX ~ Y{ }Any Y after X, same parenta[title]{ }Has the attributea[href*="google"]{ }Attribute containsa[href^="http"]{ }Attribute starts witha[href$=".jpg"]{ }Attribute ends withp::first-line{ }First line of a blockdiv:has(> img){ }A div with an img childState selectors
a:link{ }Unvisited linksa:visited{ }a:hover{ }a:active{ }a:focus-visible{ }Focus from the keyboard::selection{ }The part the user selected#news:target{ }The element the #hash points toInput selectors
input:focus{ }input:in-range{ }Within min and maxinput:out-of-range{ }Outside min and maxinput:indeterminate{ }JS: checkbox.indeterminate = true;input:invalid{ }input:valid{ }input:enabled{ }input:checked{ }input:optional{ }Without the required attributeinput::placeholder{ }The placeholder textinput:read-only{ }With the readonly attributeRelation selectors
p:first-of-type{ }p:first-child{ }p:last-child{ }p:last-of-type{ }p:nth-child(4){ }p:nth-child(odd){ }p:nth-child(even){ }p:empty{ }No children, no textp:only-of-type{ }The only p in its parentp:only-child{ }The only child of its parent:not(p){ }Every element but pSize units
pxPixelsemRelative to the element's font-sizeremRelative to the font-size of the root element%Relative to the parent (mostly)vw1% of the viewport widthvh1% of the viewport heightdvh1% of the visible height (mobile toolbars)vmin1% of the viewport's smaller sidevmax1% of the viewport's larger sidechThe width of the character "0"exAbout the height of lowercase lettersfrA share of the free space in a gridcmCentimetersmmMillimetersinInches: 96px, 2.54cmptPoints: 1.333pxpcPicas: 12ptColors
#ff0000Hexadecimal; #ff000080 with alphargb(255 0 0)rgb(255 0 0 / 0.3)With alpha (rgba() is the old name)hsl(0 100% 50%)Hue 0-360, saturation, lightnesshsl(0 100% 50% / 0.3)With alphaoklch(0.63 0.26 29)Lightness, chroma, hue: even stepsPosition properties
display: block;Pick one above: it lands here and is copiedposition: static|relative|absolute|fixed|sticky;float: left|right|none;inset: 0;top, right, bottom and left at onceleft: auto|10px|50%;right: auto|10px|50%;top: auto|10px|50%;bottom: auto|10px|50%;margin: 0 auto;Center a block with a widthFlex container
display: flex;
flex-direction: row|column;
flex-wrap: wrap;
justify-content: flex-start|center|space-between|flex-end;
align-items: stretch|center|flex-start|flex-end|baseline;
gap: 10px;Flex child
flex: 1; /* grow, shrink, basis */
align-self: stretch|center|flex-start|flex-end|baseline;
order: 2;Grid
display: grid;
grid-template-columns: repeat(3, 1fr);
grid-template-columns: repeat(auto-fill, minmax(200px, 1fr));
gap: 10px;
place-items: center;vertical-align (inline and table cells)
vertical-align:
baseline
sub
super
top
text-top
middle
bottom
text-bottom
-30px;Size properties
height: ;max-height: ;min-height: ;width: ;max-width: ;min-width: ;aspect-ratio: 16 / 9;box-sizing: border-box;Padding and border inside the widthpadding: ;Font properties
color: ;font-family: ;font-size: ;font-weight: ;line-height: ;text-align: center;text-decoration: underline;text-indent: 0;text-shadow: 2px 2px red;text-transform: uppercase;overflow-wrap: break-word;Break long words (word-wrap is the old name)white-space: nowrap;text-overflow: ellipsis;With overflow: hidden and white-space: nowrapOther properties
border: 1px solid #000;border-radius: 4px;border-top-left-radius: ;border-style
border-style:
none
dotted
dashed
solid
double
groove
ridge
inset
outset;cursor: text|pointer|grab;opacity: 1;overflow: hidden|auto|scroll;transform-origin: 0 0;transform: scale(2) rotate(45deg);box-shadow: 0 2px 4px 0 rgb(0 0 0 / .3);x y blur spread color, inset for insidevisibility: hidden|visible;z-index: ;user-select: none;Text can not be selectedpointer-events: none;Clicks go throughimage-orientation: from-image;Use the photo's EXIF rotation (the default)columns
columns: 100px 3; /* min width, max count */
column-gap: 40px;
column-rule: 4px dotted #ff00ff;
column-fill: balance|auto; /* auto: only if the parent has a height */Background properties
background-color: ;background-position: center top;background-repeat: no-repeat;repeat, repeat-x, repeat-y, no-repeatbackground-size: cover;auto, 200px, cover, containbackground-image: linear-gradient(red, blue), url("2.gif");Gradients
linear-gradient(direction, color1 5%, color2 15%, ...)repeating-linear-gradient(...)radial-gradient(shape size at position, color1, color2, ...)repeating-radial-gradient(...)linear direction
to rightto bottom left45degradial shape and size
circle|ellipse200pxA circle's radiusclosest-sidefarthest-sideclosest-cornerfarthest-cornerThe defaultAnimation
transition
transition: all 0.4s ease-in-out;
transition: opacity 0.3s ease, transform 0.3s ease;animation
@keyframes myMove{
0%{ background-color: red; }
100%{ background-color: blue; }
}
.element{
animation-name: myMove;
animation-duration: 4s;
animation-delay: -2s;
animation-iteration-count: 3;
animation-direction: normal|reverse|alternate|alternate-reverse;
animation-timing-function: ease;
}timing functions
easeSlow, fast, slow (the default)linearSame speedease-inSlow startease-outSlow endease-in-outSlow start and endcubic-bezier(n,n,n,n)make oneReduced motion
@media (prefers-reduced-motion: reduce){
*{ animation: none !important; transition: none !important; }
}Basics
const name = 1;Can not be reassigned (an object or array inside can still change)let name = 1;Can be reassigned, block scopedvar name = 1;Old: function scoped, avoidconst add = (a, b) => a + b;Arrow functionconst value = maybe ?? 'default';Default only for null or undefinedconst city = user?.address?.city;undefined instead of an error when a step is missing`Hello ${name}`Template stringDOM
element.style.color = '';Pick one above: it lands here and is copieddocument.querySelector('.class');The first match or nulldocument.querySelectorAll('.class');All matcheselement.closest('.class');Itself or the nearest parent that matcheselement.parentElementelement.addEventListener('click', event => { });element.classList.add('open');Also remove, toggle and containselement.textContentThe text, safe to setelement.innerHTMLThe HTML inside; never set it from user inputelement.outerHTMLThe element's own HTML tooelement.getAttribute('title');element.setAttribute('title', 'value');element.hasAttribute('title');true or falseelement.dataset.nameThe data-name attributeelement.insertAdjacentHTML('beforebegin', '');Before the elementelement.insertAdjacentHTML('afterbegin', '');Inside, firstelement.insertAdjacentHTML('beforeend', '');Inside, lastelement.insertAdjacentHTML('afterend', '');After the elementform.addEventListener('submit', event => { event.preventDefault(); });Key code tester
element.addEventListener('keydown', event => { if(event.key === 'Enter'){ } });Strings
"12345".slice(0, 3);"123""12345".slice(1);"2345""12345".slice(-1);"5""12345".slice(0, -1);"1234""12345".startsWith("123");true"12345".startsWith("234", 1);true, from index 1"12345".endsWith("345");true"12345".includes("234");true" text ".trim();"text": no spaces at the start and end"ab".repeat(3);"ababab""a,b".split(",");["a", "b"]"5".padStart(3, "0");"005"Arrays
for(const item of array){ }array.forEach((item, index) => { });Loop, returns nothingarray.map((item, index) => item * 2);A new array with every item changedarray.filter(item => item > 2);A new array with the items that passarray.find(item => item.id === 3);The first item that passes, or undefinedarray.some(item => item > 2);true if one passes (every: if all pass)array.reduce((sum, item) => sum + item, 0);One value from all itemsarray.includes(3);true or falsearray.push(item);Add to the endarray.pop();Remove the last item and return itarray.unshift(item);Add to the startarray.shift();Remove the first item and return itarray.at(-1);The last item[...array1, ...array2]Spread: a new array with bothRest parameters
const log = (...items) => {
for(const item of items) console.log(item);
};
log('a', 'b', 'c'); // a, b, cMath
Math.round(n);Also floor, ceil and truncMath.random();0 up to (not including) 1Math.floor(Math.random() * 6) + 1;A whole number from 1 to 6Math.min(a, b); Math.max(a, b);Math.sin(angle); Math.cos(angle);Angle in radians: x and y on a circleUseful
localStorage.getItem('name');Saved string, or nulllocalStorage.setItem('name', 'value');Save a string for this siteconst id = setTimeout(() => { }, 3000);Once, after 3 seconds; clearTimeout(id) cancelsconst id = setInterval(() => { }, 3000);Every 3 seconds; clearInterval(id) stopswindow.location.href = 'page.html';Go to a pagewindow.location.replace('page.html');Go there without a back-button entryawait navigator.clipboard.writeText('text');Copy to the clipboardJSON.stringify(value); JSON.parse(text);RegExp
const regex = /bob/g;Or new RegExp('bob', 'g') from a stringregex.test(text)true or falsetext.match(regex)An array of matches (with g), or nulltext.matchAll(regex)Every match with its groups (needs g)text.search(regex)Index of the first match, -1 for nonetext.replace(regex, 'OK')Use $1 for groupstext.split(regex)Like split with a stringexec
const match = regex.exec(text);
// match[0]: the full match
// match[1]: the first group
// match.index: where the match starts
// regex.lastIndex: where the next search starts (with g)Objects
for(const [key, value] of Object.entries(object)){ }Object.keys(object)Array of keysObject.values(object)Array of valuesconst { name, age } = object;Destructuringconst copy = { ...object, name: 'new' };A copy with one changestructuredClone(object)A deep copyFetch JSON
const response = await fetch(url);
if(!response.ok) throw new Error(response.status);
const data = await response.json();Post JSON
await fetch(url, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ name: 'value' }),
});Async function
const load = async () => {
try{
const data = await (await fetch(url)).json();
}catch(error){
console.error(error);
}
};Class
class Counter{
#count = 0; // private
add(){ return ++this.#count; }
}
const counter = new Counter();jQuery
Ajax
$.ajax({
url: "ajax/file.php",
method: "POST",
data: { name: "value" },
success: data => { },
});Each
$.each(object, (key, value) => {
console.log(key + ": " + value);
});On
$("p").on({
mouseenter(){ $(this).css("background-color", "lightgray"); },
mouseleave(){ $(this).css("background-color", "lightblue"); },
click(){ $(this).css("background-color", "yellow"); },
});$(() => { });Runs when the page is ready.on('click', function(){ });Also change, dblclick, mouseenter, mouseleave, focus, blur.on('click', '.item', function(){ });Also for .item elements added later.addClass('');.removeClass('');.hasClass('');.css('color', 'red');.text();Get or set the text.html();Get or set the HTML.val();Get or set the value of an input.find('.class');.children();.parent();.show(); .hide();.fadeTo('slow', 0.5, () => { });.slideDown('slow', () => { });.slideUp('slow', () => { });.delay(1000);Only delays animationsBasics
Objects are handles
<?php
// Types: bool, int, float, string, array, object, callable, iterable, resource, null
class A {
public int $foo = 1;
}
$a = new A();
$b = $a; // $b holds the same object, not a copy
$b->foo = 2;
echo $a->foo; // 2
$c = clone $a; // a real (shallow) copy
$c->foo = 3;
echo $a->foo; // still 2
function change(A $object): void {
$object->foo = 4; // changes the object that was passed in
}
change($a);
echo $a->foo; // 4match and switch
// match: strict (===), returns a value, error when nothing matches
$label = match ($status) {
'new', 'open' => 'To do',
'done' => 'Done',
default => 'Unknown',
};
// switch: loose (==), needs break
switch ($status) {
case 'new':
$label = 'To do';
break;
default:
$label = 'Unknown';
}if ($value !== '') { }$name = $input ?? 'default';Default when null or not set$city = $user?->address?->city;null instead of an error when a step is nullrequire __DIR__ . '/other.php';Stops with an error when the file is missing (include only warns)'It\'s easy to write a single quote'"Variables like $name work in double quotes""Also {$user->name} and {$items[0]}"$text = 'now this';$text .= ' and this';Appendecho "";print_r($array);Print an array readablyvar_dump($value);Print a value with its typeregister_shutdown_function('cleanUp');Runs when the script endsfile_exists($path);Does the file or folder existmemory_get_usage();Current memory use in bytesmemory_get_peak_usage();Peak memory use in bytesbin2hex(random_bytes(16));A random id, safe for tokens (uniqid() is not)serialize($value);A string that keeps type and structureunserialize($string);Back to the value; never on user inputjson_encode($value, JSON_THROW_ON_ERROR);To JSONjson_decode($json, true, flags: JSON_THROW_ON_ERROR);From JSON; true gives arrays instead of objectsgzcompress($string, $level);Compress, level 0-9gzuncompress($compressed);Decompressfile_get_contents($filename);morefile_put_contents($filename, $data);glob('*.php');Array of matching filesfunction sum(int ...$numbers): int { }Any number of arguments, as an arraysetcookie('name', 'value', time() + 3600);Expires in 1 hourparse_url()
parse_url('https://user:pass@example.com:9090/path?arg=value#anchor');
// [
// 'scheme' => 'https',
// 'host' => 'example.com',
// 'port' => 9090, // an int
// 'user' => 'user',
// 'pass' => 'pass',
// 'path' => '/path',
// 'query' => 'arg=value', // parse_str() splits it
// 'fragment' => 'anchor',
// ]getrusage()
getrusage();
// [
// 'ru_utime.tv_sec' => 0, // user CPU time: seconds
// 'ru_utime.tv_usec' => 0, // plus microseconds
// 'ru_stime.tv_sec' => 0, // system CPU time: seconds
// 'ru_stime.tv_usec' => 6269, // plus microseconds
// 'ru_maxrss' => 12692, // most memory used (KB on Linux)
// 'ru_minflt' => 94, // page reclaims
// 'ru_majflt' => 0, // page faults
// 'ru_inblock' => 0, // block input operations
// 'ru_oublock' => 0, // block output operations
// 'ru_nvcsw' => 67, // voluntary context switches
// 'ru_nivcsw' => 4, // involuntary context switches
// ...
// ]Array functions
is_array($value);true or falsein_array($needle, $haystack, true);true or false; true: strict comparearray_search($needle, $haystack);The key, or falsearray_key_exists($key, $array);true or falsecount($array);Number of elementsarray_unique($array);Without duplicatesarray_reverse($array, true);Reversed; true keeps the keysarray_map(fn ($item) => $item * 2, $array);Every item changedarray_filter($array, fn ($item) => $item > 2);Only the items that pass (keys kept)array_reduce($array, fn ($sum, $item) => $sum + $item, 0);One value from all itemsarray_diff($array1, $array2);Values in $array1 that are not in $array2array_intersect($array1, $array2);Values in bothmax($array);The highest valuemin($array);The lowest valuearray_sum($array);array_rand($array, $count);Random keysarray_count_values(['ducks', 'ducks', 'dogs']);['ducks' => 2, 'dogs' => 1]sort($array);Sorts in place, new keysasort($array);Sorts in place, keeps keysrsort($array);Reverse, new keysarsort($array);Reverse, keeps keysusort($array, fn ($a, $b) => $a <=> $b);Sort with your own compareimplode(', ', $array);Array to stringexplode(',', $string);String to arrayarray_keys($array);array_values($array);The values with new keys 0, 1, 2…$array[] = $value;Add to the endarray_push($array, $value1, $value2);Add several to the endarray_pop($array);Removes and returns the last valuearray_unshift($array, $value1, $value2);Add to the startarray_shift($array);Removes and returns the first valuearray_merge($array1, $array2);Merged; same string keys: the last wins[...$array1, ...$array2]The same with spreadforeach
$numbers = [1, 2, 3, 4];
foreach ($numbers as $key => $value) {
$numbers[$key] = $value * 2; // changes the array
}
foreach ($numbers as &$value) { // or by reference
$value = $value * 2;
}
unset($value); // always unset the reference after the loopSplice
$array = [0, 1, 2, 3];Each line below starts from this arrayarray_splice($array, 2);0, 1array_splice($array, 1, -1);0, 3array_splice($array, 1, 3, 'a');0, aarray_splice($array, 1, 3, ['a', 'b']);0, a, barray_slice($array, 1, 2);Returns 1, 2 and leaves $array aloneScope
Global
$a = 1;
$b = 2;
function test() {
echo $a; // Warning: Undefined variable $a
echo $GLOBALS['a']; // 1
global $b;
echo $b; // 2
}
test();static
function counter(): int {
static $count = 0; // set once, kept between calls
$count++;
$calls = 0; // a normal variable starts over on every call
return $count;
}
counter(); // 1
counter(); // 2Classes
Scope Resolution Operator ::
class One {
const NAME = 'One';
}
echo One::NAME; // One
class Two extends One {
const NAME = 'Two';
public static string $count = 'static property';
public static function describe(): void {
echo parent::NAME; // One
echo self::NAME; // Two
echo static::NAME; // Two, or the subclass's NAME when called on a subclass
echo self::$count; // static property
}
}
echo Two::$count; // static property
Two::describe();
$class = 'Two';
echo $class::NAME; // TwoConstructor promotion
class User {
public function __construct(
public readonly string $name,
private int $age = 0,
) {}
}
$user = new User('Ann');
echo $user->name; // AnnClass properties
class One {
public $public = 'Public';
protected $protected = 'Protected';
private $private = 'Private';
function printProperties() {
echo $this->public;
echo $this->protected;
echo $this->private;
}
}
$one = new One();
echo $one->public; // Public
echo $one->protected; // Error: Cannot access protected property
echo $one->private; // Error: Cannot access private property
$one->printProperties(); // PublicProtectedPrivate
class Two extends One {
// You can redeclare public and protected, not private
public $public = 'Public!!';
protected $protected = 'Protected!!';
function printProperties() {
echo $this->public;
echo $this->protected;
echo $this->private; // Warning: Undefined property
}
}
$two = new Two();
echo $two->public; // Public!!
echo $two->protected; // Error: Cannot access protected property
echo $two->private; // Warning: Undefined property
$two->printProperties(); // Public!!Protected!! and the warningClass methods
class Three {
public function goPublic() { echo 'Public'; }
protected function goProtected() { echo 'Protected'; }
private function goPrivate() { echo 'Private'; }
function goPublicAll() {
$this->goPublic();
$this->goProtected();
$this->goPrivate();
}
}
$three = new Three();
$three->goPublic(); // Public
$three->goProtected(); // Error: Call to protected method
$three->goPrivate(); // Error: Call to private method
$three->goPublicAll(); // PublicProtectedPrivateEnum
enum Status: string {
case Open = 'open';
case Done = 'done';
}
Status::from('open'); // Status::Open, error for an unknown value
Status::tryFrom('nope'); // null
Status::Open->value; // 'open'Superglobals
$GLOBALSAll global variables$_GETThe query string$_POSTPosted form fields$_FILESFiles uploaded with a POST$_COOKIECookies the browser sent$_SESSIONAfter session_start(): kept between requests$_REQUEST$_GET, $_POST and $_COOKIE together$_ENVEnvironment variables (often empty: use getenv())$_SERVERRequest and server details$_SERVER
[
'HTTP_HOST' => 'www.example.com',
'HTTP_USER_AGENT' => 'Mozilla/5.0 (Windows NT 10.0...',
'HTTP_ACCEPT' => 'text/html,application/xhtml+xml...',
'HTTP_ACCEPT_LANGUAGE' => 'en-US,en;q=0.7,nl;q=0.3',
'HTTP_COOKIE' => 'session=abc123',
'HTTP_X_FORWARDED_FOR' => '203.0.113.7', // client IP behind a proxy (can be faked)
'HTTP_X_FORWARDED_PROTO' => 'https',
'SERVER_SOFTWARE' => 'nginx',
'SERVER_NAME' => 'www.example.com',
'SERVER_ADDR' => '192.0.2.10',
'SERVER_PORT' => '443',
'REMOTE_ADDR' => '198.51.100.23', // client IP (or the proxy's)
'REMOTE_PORT' => '51234',
'DOCUMENT_ROOT' => '/var/www/html',
'SCRIPT_FILENAME' => '/var/www/html/folder/file.php',
'SERVER_PROTOCOL' => 'HTTP/1.1',
'REQUEST_METHOD' => 'GET',
'QUERY_STRING' => 'arg=value',
'REQUEST_URI' => '/folder/file.php?arg=value',
'SCRIPT_NAME' => '/folder/file.php',
'PHP_SELF' => '/folder/file.php',
'REQUEST_TIME' => 1762250000, // Unix time of the request
'REQUEST_TIME_FLOAT' => 1762250000.8289, // the same with microseconds
]Names and locations
__LINE__Line number__FILE__File path__DIR__Folder path__FUNCTION__Function name__CLASS__Class name__METHOD__Method name__NAMESPACE__Namespace$object::classClass name of an objectPDO
Connect
$pdo = new PDO(
'mysql:host=localhost;dbname=my_database;charset=utf8mb4',
'username',
'password',
[PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC], // errors throw by default since PHP 8
);
// SQLite: one file, no server
$pdo = new PDO('sqlite:' . __DIR__ . '/database.sqlite');Insert
$stmt = $pdo->prepare(
'INSERT INTO table_name (col1, col2) VALUES (:col1, :col2)'
);
$stmt->execute(['col1' => 'val1', 'col2' => 'val2']);
$id = $pdo->lastInsertId();Select
$stmt = $pdo->prepare(
'SELECT col1, col2 FROM table_name WHERE col1 = :col1'
);
$stmt->execute(['col1' => 'val1']);
$rows = $stmt->fetchAll(); // [] when nothing matched
if ($rows !== []) {
echo $rows[0]['col1'];
}
// One row: $stmt->fetch() (false when none)Update
$stmt = $pdo->prepare(
'UPDATE table_name SET col1 = :col1 WHERE col2 = :col2'
);
$stmt->execute(['col1' => 'val1', 'col2' => 'val2']);
$changed = $stmt->rowCount(); // rows changedTransaction
$pdo->beginTransaction();
try {
// several queries
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}Basics
What a regex is
A regex is a pattern: a small language to search, extract,
validate and transform text.
Every character is an instruction to match, so " " looks for
one space and " " for two.
In JavaScript a regex sits between slashes: /example/
PHP needs a delimiter too, in a string: '/example/'
PHP may use another delimiter, like '!example!' or '#example#'.
Handy when the pattern has slashes, because the delimiter
itself has to be escaped inside the pattern:
/http:\/\// the same as !http://!Example
Input: "Life is like a box" jimmy, '2022!'
Regex: /"(?<quoted_text>[^"]+).*?[^0-9](?<year>[0-9]{4})(?:[^0-9]|$)/s
Output:
match -> "Life is like a box" jimmy, '2022!
quoted_text -> Life is like a box
year -> 2022The example, piece by piece
" first a double quote
[^"]+ then one or more characters that are not a double quote (greedy)
(?<quoted_text> ) all inside a named capture group
.*? then as few characters as possible, of any kind
(the s flag lets . match newlines too)
[^0-9][0-9]{4} then a non-digit followed by 4 digits
(?<year> ) with the 4 digits in a named capture group
[^0-9]|$ followed by a non-digit or the end of the input
(?: ) that "or" sits in a non-capturing group: it stays out of the result,
and the | only applies inside the group (cat|dog vs cat|dog)greedy vs lazy
Input: wow a Tic-Tac-Toe
Greedy (the default): /a.*c/ -> a Tic-Tac
Lazy (add ?): /a.*?c/ -> a TicGroups and lookarounds
(example)Capturing group(?<name>example)Named group(?:example)Non-capturing group(?=example)Lookahead: followed by, not part of the match(?!example)Negative lookahead: not followed by(?<=example)Lookbehind: preceded by(?<!example)Negative lookbehind: not preceded by\2The text the second group matched, againa|bEither a or bCharacter classes
[abc]One of the characters in brackets[0-9a-z]One character in a range[^abc]One character NOT in bracketsAnchors
^Start of the text (of each line with the m flag)$End of the text (of each line with the m flag)\bWord boundary: between \w and \W\BNot a word boundaryQuantifiers
*0 or more+1 or more?0 or 1{3}Exactly 3{3,4}3 to 4{3,}3 or more*? +? ??Lazy: as few as possibleCharacters
.Any character except a newline (with the s flag: any)\dDigit (0-9)\DNot a digit\wWord character (a-z, A-Z, 0-9, _)\WNot a word character\sWhitespace (space, tab, newline)\SNot whitespace\tTab\nNewline\.A real dot: escape these with \ : . * + ? ^ $ ( ) [ ] { } | \ /\hHorizontal whitespace (PHP only)\vVertical whitespace (PHP only; in JavaScript \v is a vertical tab)Flags in both
iIgnore casemMultiline: ^ and $ match at every linesDotall: . matches newlines toouUnicode (PHP: treat the text as UTF-8)Flags in JavaScript only
gGlobal: all matches (PHP: use preg_match_all)ySticky: match only at lastIndexdIndices: start and end of each groupFlags in PHP only
xExtended: whitespace and # comments in the pattern are ignoredAAnchored: only match at the startDDollar end only: $ only at the very end, not before a final newlineUUngreedy: swaps greedy and lazyJAllow duplicate group namesnNo auto capture: only named groups capture (PHP 8.2+)Start
git initMake the current folder a git repogit clone https://example.com/repo.gitCopy a repo into a new folder named after itgit statusWhat changed, what is stagedgit help cloneHelp for a commandSave changes
git diffChanges not staged yetgit diff --stagedChanges that go into the next commitgit add file.txtStage one filegit add .Stage everything in this folder and belowgit add -uStage changed and deleted files, not new onesgit commit -m ""Commit what is staged, message in the quotesgit restore file.txtThrow away unstaged changes to a filegit restore --staged file.txtUnstage a file, keep the changesgit stashPut changes aside; git stash pop brings them backShare
git fetchGet the remote branches, change nothing localgit pullFetch and merge the remote branch into yoursgit pushUpload your commitsgit push -u origin my-branchFirst push of a new branchHistory
git log --onelineAll commits, one line eachgit log -p file.txtEvery change to a filegit show f6891c9One commit and its changesgit blame file.txtWho last changed each lineBranches
git branch -aAll branches, remote ones toogit switch my-branchGo to a branchgit switch -c my-branchMake a branch and go to itgit branch -d my-branchDelete a merged branch (switch away from it first)Merge a branch into main
git switch main
git pull
git merge my-branch
git pushRebase a branch onto main
git switch my-branch
git fetch
git rebase origin/main
# fix conflicts, then: git add <file> && git rebase --continue
git push --force-with-leaseUndo
git revert f6891c9A new commit that undoes that one (safe on shared branches)git commit --amendChange the last commit (only before you pushed it)git reset --soft HEAD~1Undo the last commit, keep its changes stagedgit reset --hard f6891c9Careful: deletes later commits AND all uncommitted changes:wqGit opened vim for a message: press Esc, type :wq and Enter to save and close