Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@yablko

yablko/webdev-mysql-ulohy.md

Last active December 12, 2016 14:24
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yablko/50d0607ef2df60b2f3986f21e636dd01 to your computer and use it in GitHub Desktop.
Save yablko/50d0607ef2df60b2f3986f21e636dd01 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
webdev-mysql-ulohy.md

Stiahni si mysql-movies.zip.
Vytvor v databaze tabulku movies, encoding utf8mb4, collation utf8mb4_general_ci.

1) Vytiahni z databazy a do takejto (index.php) tabulky vypis vsetky filmy
- ak nevies spravit tabulku (tabulky su hovada) vypis iba do ul>li zoznamu
- ak nevies spocitat vyslednu sumu, nechaj na neskor
- (btw peniaze sa daju mozno jednoduchsie zratat cez SQL)
- dolezite je vytiahnut vsetky filmy a vypisat udaje o nich
- ak vies, skus k nim vypisat aj meno rezisera (pouzi JOIN)
- skus ceny naformatovat cez SQL

2) Vytiahni z databazy vsetkych reziserov, vypis ich pod seba
- kazde meno rezisera bude link, na ktory ked kliknem, zobrazia sa mi iba jeho filmy
- skus to z linkov prerobit na html select https://developer.mozilla.org/en/docs/Web/HTML/Element/select
- aby select fungoval, musi byt vo formulari (rovnako ako input alebo textarea)
- skus nad tabulku napisat meno rezisera, ktoreho filmy zobrazujes

3) Skus spravit maly formular s inputom, do ktoreho ked napisem meno rezisera, najdu sa mi vsetky jeho filmy
- idealne aby aj v <head> title elemente svietilo meno daneho rezisera (teraz je tam Edgar Wright na tvrdo)

4) Skus pridat linky na usporiadanie zostupne/vzostupne podla stlpca
- napr. v adrese bude ?order_by=title&order=asc a tak podobne

5) Do formularu z bodu 3 pridat nove policka na:
- rozmedzie rokov (policko od roku X, policko do roku Y)
- a vylistuju sa iba tie filmy, ktore spadaju do rozmedzia
- a aby to fungovalo aj ak zadam iba jednu hodnotu
- podobne pre zarobky (gross) - zadanie limutu (aby som si mohol najst len prve 3 filmy)

6) Skus spravit strankovanie
- ze niekde v premennej zadas kolko maximalne filmov sa moze zobrazit na stranke a potom sa vyrobia linky na nasledovnu/predoslu stranku - pripadne sa vykreslia linky na page 1, page 2, page 3, atd...

7) Vyrob HTML formular pre:
- pridanie noveho rezisera
- pridanie noveho filmu
- pri pridavani filmu sa vytvori html <select> element, v ktorom si budem moct vybrat meno rezisera, ku ktoremu sa ten film priradi

8) Mini ulohy:
- skus spravit <select> aj pre zanre a pouzi ho vo formulari pre pridanie noveho filmu (ktore zanre existuju si musis vytiahnut z databazy)
- skus upravit <select> pre reziserov aby zobrazoval iba reziserov, ktori maju nejaky film

9) Vyrob edit a delete pre filmy aj reziserov:
- jeden pre film, druhy pre rezisera
- na edit mozes upravit formular pre pridavanie noveho filmu/rejzu
- pozor, aby si vzdy upravoval iba jednu hodnotu a neprepisal/nevymazal vsetko (LIMIT 1)

10) Profil pre rezisera:
- na hlavnej stranke sa v tabulke filmov zobrazuje aj meno rezisera - zmen to meno na link na filmy rezisera
- tento zoznam reziserovych filmov budeme volat "profil" rezisera
- pri jeho mene v nadpise budu linky na editaciu a vymazanie rezisera
- uprav SQL, aby sa na profile rezisera v zozname filmov uz nezobrazoval prvy stlpec s jeho menom

11) Stranka pre film:
- do databazovej tabulky movies pridaj cez adminer/phpmyadmin novy stlpec summary, ktory bude typu text
- v zozname filmov kliknutim na nazov sa dostanem na stranku filmu
- na nej budu vylistovane podobne udaje, ako v tabulke, ale pribudne k nim aj sumar/popis filmu
- samozrejme vo formulari pre pridanie/editovanie filmu pribudne nove policko (najlepsie <textarea>)
- sprav to tak, aby v _inc adresari zostali iba formulare pre editovanie/pridavanie novych zoznamov a subory pre vylisovanie reziserov a filmov a podobne, budu v hlavnom adresari appky (tam, kde je index.php pribudne aj movie.php a director.php atd.)

12) Vyrob funkcie:
- pridaj do aplikacie novy subor (napr. functions.php) do ktoreho pridu funkcie
- sprav funkcie get_movies() a get_movie($id), ktore vratia pole vsetkych filmov/konkretneho filmu
- sprav funkcie get_directors() a get_director($id), ktore vratia pole vsetkych reziserov/konkretneho rejzu
- upravu funkciu get_movies() alebo si sprav novu (napriklad get_movies_by_director($id)) do ktorej ked hodim idcko rezisera, vrati sa mi pole vsetkych filmov
- cize namiesto toho, aby sme mali SQL kod rozstruseny po mnohych suboroch, vsetky SQL queries budu upratane na jedinom mieste

13) Rozdel subor s funkciami na 3 samostatne:
- subor pre funkcie co pracuju s filmami, subor pre reziserov, subor pre zvysne, vseobecne funkcie
- do nich pridaj funkcie ako edit_movie($id, $data) alebo delete_movie($id) atd.
- sprav funkciu pre zistenie, ci bol vybrany nejaky reziser alebo ci sa maju zobrazovat vsetky filmy
- sprav funkciu pre vykreslenie vyberacieho <selectu>, ktory sa pouzije aj v hlavicke stranky a v editacnom formulari pre filmy
- sprav funkciu, do ktorej hodim pole filmov a ona spocita, kolko zarobili

14) Prerobit aplikaciu na filmy tak, ze:
- na hlavnej stranke vedla seba budu 2 zoznamy linkov: najnovsich 5 filmov a najnovsich 5 reziserov
- kazdy link bude odkazovat na konkretny film alebo profil rezisera
- adresy budu v tvare /movie.php?id=3 a /director.php?id=4
- pod zoznamom filmov bude odkaz na "vsetky filmy" (ta ista tabulka, ako mame teraz)
- budu tam odkazy na pridanie noveho filmu a rezisera
- na reziserovom profile bude odkaz na pridanie filmu s tym, ze sa prednastavi - kedze na zozname vsetkych filmov a na profile rezisera su ine tabulky, stalo by za zvazanie zjednodusit kod tabuliek a spravit iny kod pre obe podstranky

15) Ak mas hotovu aplikaciu pre filmy:
- prerob TODO appku na databazu
- aj s pridavanim, upravovanim a mazanim zaznamov
- sprav si subor a don vyrob funkcie na vsetko SQL (napr. create_item(), edit_item(), delete_item())

16) Sprav dalsi <select>:
- pre vyber filmov podla zanru
- pripominam ulohu 5;) vyskusaj si tie veci - na tejto stranke to sice nedava velky zmysel, ale ide o precvicenie si spravenia formularu, odoslanie hodnot a upravenie SQL query podla tychto hodnot - na strankach sa to robi stale, treba to mat zmaknute

17) V tabulke filmov sprav usporiadanie podla stlpcov:
- nadpisy stlpcov sa zmenia na linky
- ked kliknem na "year", usporiadaju sa filmy od najnovsieho a ked kliknem znova, od najstarsieho
- rovnako ostatne stlpce - abecedne
- pozri na ulohu 4

18) Pripominam ulohu cislo 6:
- spravit strankovanie;)

19) Na stranke mame funkcie url() a anchor(), ktore vyrobia spravne linky:
- sprav funkcie ako edit_url('movie', 4) a delete_url('director', 2)
- edit a delete linky na stranke vyrabaj pomocou tychto novych funkcii
- pod editacne formulare pridaj nenapadny "vymaz rezisera" link

20) Vyrob partials:
- header.php a footer.php
- include cestu sprav cez APP_PATH
- zariad, aby sa zobrazoval spravny <title> text

21) Validacia:
- sprav validaciu pre filmy (pri pridavani aj editovani)
- uprav vypis errorov, aby kazdy nespravne vyplneny input svietil na cerveno a bola vedla neho (alebo pod nim) error hlaska

22) Pridat popis filmu:
- viac v ulohe 11
- samozrejme to treba pridat do new/edit formularov a do validacie
- môže to vyzerať takto

 

Stiahni movies appku:
http://cl.ly/0W0h3b2l3x1Y/movies.zip (toto je stara verzia, novu najdes vzdy v diskusii na learn2code.sk)

http://cl.ly/2M3w1i2J413c/movies-12-maj.zip (appka aj so zacatymi funkciami)

@yablko
Copy link
Author

yablko commented Apr 28, 2016 •
edited 

SELECT * FROM movies

SELECT * FROM movies
WHERE director_id = 4

SELECT * FROM movies
WHERE director_id = 2
AND year >= 2000 AND year <= 2010

SELECT * FROM movies
WHERE director_id IN (2, 3)
AND year BETWEEN 2000 AND 2010 AND year != 2004

SELECT first_name, last_name, YEAR(birthdate)
FROM directors

SELECT first_name, last_name
FROM directors
WHERE MONTH(birthdate) = 1
AND DAY(birthdate) <= 15

SELECT NOW()

SELECT first_name, last_name, country, YEAR(NOW()) - YEAR(birthdate) AS age
FROM directors

SELECT first_name, last_name, country,
    YEAR(NOW()) - YEAR(birthdate) AS age,
    CONCAT(first_name, ' ', last_name) AS name
FROM directors
ORDER BY age ASC

(DESC)

SELECT * FROM movies
LIMIT 5

SELECT * FROM movies
LIMIT 5 OFFSET 10

SELECT MAX(gross) FROM movies

SELECT FORMAT(MAX(gross), 2) FROM movies

SELECT CONCAT('$', FORMAT(MAX(gross), 0)) AS gross FROM movies

SELECT
    CONCAT('$', FORMAT( MAX(gross), 0) ) AS max,
    CONCAT('$', FORMAT( MIN(gross), 0) ) AS min,
    CONCAT('$', FORMAT( AVG(gross), 0) ) AS avg
FROM movies
WHERE director_id = 3

---

INSERT INTO directors (first_name, last_name, country, birthdate)
VALUES ('Paul Thomas', 'Anderson', 'United States', '1970-07-26')

INSERT INTO directors
VALUES ('Ingmar', 'Bergman', 'Sweden', '1918-07-14')

alebo

INSERT INTO directors
VALUES (NULL, 'Ingmar', 'Bergman', 'Sweden', '1918-07-14')

alebo

INSERT INTO directors
VALUES (DEFAULT, 'Andrei', 'Tarkovsky', 'Russia', '1932-04-04')

INSERT INTO directors (first_name, last_name, birthdate)
VALUES ('Krzysztof', 'Kieslowski', '1941-06-27')

INSERT INTO directors (first_name, last_name, birthdate)
VALUES ('Abbas', 'Kiarostami', '1940-06-22')

SELECT * FROM directors
WHERE country IS NOT NULL

SELECT * FROM directors
WHERE country IS NOT NULL AND country != ''

SELECT * FROM directors
WHERE country IS NULL OR country = ''

SELECT * FROM directors
WHERE NULLIF (country, '') IS NULL

INSERT INTO movies
    (director_id, title, year, gross)
VALUES
    (9, 'The Wind Will Carry Us', 2000, 259510),
    (9, 'Certified Copy', 2011, 1373975),
    (9, 'Like Someone in Love', 2013, 239056)

---

SELECT * FROM directors
WHERE country = 'United States'

SELECT * FROM directors
WHERE country LIKE 'United States'

SELECT * FROM directors
WHERE country LIKE 'United%'

SELECT * FROM directors
WHERE first_name LIKE 'A%' OR first_name LIKE 'J%'

SELECT * FROM movies
WHERE title LIKE '%Avengers'

SELECT * FROM movies
WHERE title LIKE '%World%'

SELECT title FROM movies
SELECT 'balls' FROM movies
SELECT 1 FROM movies

SELECT COUNT(1) FROM movies

SELECT
    first_name, last_name
    CONCAT('$', FORMAT(MAX(gross), 0)) AS max,
    CONCAT('$', FORMAT(MIN(gross), 0)) AS min
FROM movies
WHERE director_id = 4

SELECT
    first_name, last_name
    CONCAT('$', FORMAT(MAX(gross), 0)) AS max,
    CONCAT('$', FORMAT(MIN(gross), 0)) AS min
FROM movies
JOIN
    directors ON directors.id = director_id
WHERE
    director_id = 4

SELECT CONCAT(first_name, ' ', last_name) AS name, title, year
FROM directors
JOIN movies ON directors.id = movies.director_id
WHERE last_name LIKE 'W%'

SELECT CONCAT(first_name, ' ', last_name) AS name, title, year
FROM directors
JOIN movies ON directors.id = movies.director_id AND last_name LIKE 'W%'

...
LEFT JOIN movies ON directors.id = movies.director_id
...
RIGHT JOIN movies ON directors.id = movies.director_id

---

SELECT * FROM movies
GROUP BY genre

SELECT genre FROM movies
GROUP BY genre

SELECT DISTINCT genre FROM movies

SELECT genre, SUM(gross) FROM movies
GROUP BY genre

SELECT genre, FORMAT(SUM(gross),0) FROM movies
GROUP BY genre

@yablko
Copy link
Author

yablko commented Apr 28, 2016 •
edited 

SELECT title, year, gross, genre, first_name, last_name, country 
FROM movies
JOIN directors ON directors.id = movies.director_id
WHERE CONCAT(first_name, ' ', last_name) = 'Jim Jarmusch'

@yablko
Copy link
Author

yablko commented May 3, 2016 •
edited 

UPDATE directors SET country = 'Poland'
WHERE id = 8
LIMIT 1

UPDATE directors SET
    first_name = 'Andrej',
    last_name  = 'Tarkovskij',
    country    = 'Russia'
WHERE id = 7

UPDATE movies SET gross = gross + 0.50
WHERE id > 10

DELETE FROM movies
WHERE id = 12
LIMIT 1

---

SELECT \* FROM movies
GROUP BY genre

SELECT genre FROM movies
GROUP BY genre

SELECT DISTINCT genre FROM movies

SELECT genre, SUM(gross) FROM movies
GROUP BY genre

SELECT genre, FORMAT(SUM(gross),0) FROM movies
GROUP BY genre

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment