Juhej MySQL

Vir za večino predavanja je mysqltutorial.org

V prednameščenem okolju, v katerem delamo je nameščen tudi strežnik MySQL - no v resnici je naložen MariaDB (zakaj, na povezavi).

Delo v terminalu / MariaDB odjemalcu

Strežnik je sotopen na naslovu "localhost".

Na strežnik se prijavite z uporabniškim imenom in geslom, s katerim dostopate do "virtualke." Neposredno do strežnika pridete prek terminala (ctrl-alt-t).

     mysql -h localhost -u kroneggerl -p
     Enter password:

Glede na to da delamo lokalno se lahko prijavite tudi samo z mysql brez vsega - ali pa celotno prijavo speljete od začetka do konca

     mysql --host localhost --user kroneggerl --database  SP_kroneggerl -p
     Enter password:

V mySQL okolju, lahko pregledamo katere baze že obstajajo:

     MariaDB [(none)]> show databases;

     +--------------------+
     | Database           |
     +--------------------+
     | information_schema |
     | SP_kroneggerl         |
     +--------------------+
     2 rows in set (0,00 sec)

Uporabljali bazo pripravljeno za nas (pri meni je to SP_kroneggerl)

MariaDB [(none)]> use SP_kroneggerl; Database changed MariaDB [SP_kroneggerl]>


      *** Če delamo na svojem strežniku***, moramo najprej ustvariti bazo. Za to, na vzpostavljenem strežniku nimamo pravic.
     
     mysql> create database kroneggerl;
     Query OK, 1 row affected (0.004 sec)

     

     Brisanje obstoječe baze:
          mysql>   DROP DATABASE IF EXISTS kroneggerl;
     Query OK, 0 rows affected (0.009 sec)


     


     Napolnimo s podatki:
          USE SP_kroneggerl;
     CREATE TABLE imenik(
         ime VARCHAR(30) NOT NULL,
         priimek VARCHAR(30) NOT NULL,
         mesto VARCHAR(40) NOT NULL,
         telefon VARCHAR(20) NOT NULL
     );


     


     Za ponovitev posameznih tipov zapisov:
      
		     ------------ ŠTEVILSKI TIPI ------------
		
		TINYINT: Število z vrednostjo največ 127 ali najmanj -128  
		SMALLINT: Število z vrednostjo največ 32.768 ali najmanj -32.767  
		MEDIUMINT: Število z vrednostjo največ 8.388.608 ali najmanj -8.388.608  
		INT: Število z vrednostjo največ 2^31 ali najmanj -2^31  
		BIGINT: Število z vrednostjo največ 2^63 ali najmanj -2^63  
		FLOAT: Število z decimalnimi mesti, z vrednostjo največ 1.1E38 ali najmanj -1.1E38  
		DOUBLE: Število z decimalnimi mesti, z vrednostjo največ 1.7E308 ali najmanj -1.7E308  
		
		------------ NIZI (STRINGI) ------------
		
		CHAR: Niz znakov s fiksno dolžino  
		VARCHAR: Niz znakov s spremenljivo dolžino  
		BLOB: Lahko vsebuje do 2^16 bajtov podatkov  
		ENUM: Niz znakov z omejenim številom možnih vrednosti, ki jih morate vnaprej določiti  
		SET: Seznam dovoljenih nizov znakov. Za razliko od ENUM lahko SET vsebuje več vrednosti hkrati, medtem ko ENUM omogoča le eno  
		
		------------ DATUM IN ČAS ------------
		
		DATE: Datum v formatu (YYYY-MM-DD)  
		TIME: Čas v formatu (HH:MM:SS)  
		DATETIME: Datum in čas v formatu (YYYY-MM-DD HH:MM:SS)  
		TIMESTAMP: Datum in čas, pogosto uporaben za samodejno beleženje časa spremembe zapisa (YYYYMMDDHHMMSS)  
		YEAR: Leto v formatu (YYYY)
     


     
     Kako sedaj to izgleda?

     
     MariaDB [SP_kroneggerl]>   DESCRIBE imenik
     -> ;
     +---------+-------------+------+-----+---------+-------+
     | Field   | Type        | Null | Key | Default | Extra |
     +---------+-------------+------+-----+---------+-------+
     | ime     | varchar(30) | NO   |     | NULL    |       |
     | priimek | varchar(30) | NO   |     | NULL    |       |
     | mesto   | varchar(40) | NO   |     | NULL    |       |
     | telefon | varchar(20) | NO   |     | NULL    |       |
     +---------+-------------+------+-----+---------+-------+
     4 rows in set (0.060 sec)



     

     Še pobrišimo tabelo
          MariaDB [SP_kroneggerl]> drop table imenik;
     Query OK, 0 rows affected (0.045 sec)

     

   Če delamo na svojem strežniku poskrbimo za varnost!!! 


   Ustvarimo uporabnika baze  s čim manj pravicami (v realnosti je to naša spletna stran, ki se bo povezovala na bazo)

   Tako kot vedno gre za varnost. Spodnje ukaze izvedete direktno na strežniku mysql.
   Na pripravljenem strežniku nimamo pravic za ustvarjanje novih profilov.


   
   GRANT INSERT, SELECT, DELETE, UPDATE ON SP_kroneggerl.*
   TO 'studentweb'@'localhost'
   IDENTIFIED BY 'Živel 1. maj!';
   

   Uporabniku z imenom "studentweb"

   In geslom "Živel 1. maj!"

   V bazi "test2" dovolimo:

   
   SELECT : izbrati vrstice v tabelah
   
INSERT : dodajati vrstice v tabelah
   
UPDATE : spreminjati podatke v vrsticah
   
DELETE : izbrisati vrstice (tole dovoljenje je bolje NE UPORABITI če zanj ni potrebe)
   





   Priklop na bazo s PHP

Ustvarimo datoteko config.php, kamor bomo skrili nekaj pomembnih zadev:

<?php
// Tole je koda namenjena priklopu na bazo
// PAZI gre za osebne podatke, ki morajo ostati skriti
// Ker gre za php datoteko, podatki ne bodo prikazani v brskalniku
// vseeno mora biti datoteka spravljena izven "web" mape
// in zgolj klicana po potrebi shranili jo bomo v "config.php"

        $sql_host = "localhost";
        $sql_user = "kroneggerl";
        $sql_pass = "";
        $sql_db = "SP_kroneggerl";
        
       echo "<p>Config.php uspešno prebran</p>";
 ?>





Datoteko z gesli pokličemo z "require_once("config.php");"

<?php
	require_once("config.php");
?>


PDO je "vmesnik" (orodje), ki omogoča PHP-ju, da komunicira z bazo:

vzpostavi povezavo,
pošilja SQL ukaze,
vrača rezultate.


<?php
	require_once("config.php");
	$PDO = new PDO("mysql:host=". $sql_host .";dbname=" .$sql_db .";charset=utf8", $sql_user, $sql_pass);
 ?>





    Rezultat izvajanja:




		 Config.php uspešno prebran 




Tole je sicer fino, ampak kaj ko ne vidimo a smo priklopljeni ali ne. Uvedimo ukaz "try" {} catch {};  
PDOException je napaka, ki se pojavi pri povezavi z bazo ali izvajanju SQL poizvedb in jo lahko prestrežemo z try–catch.


Puščica "->" v PHP pomeni: "vzemi nekaj iz objekta"
Torej: $pe je objekt (ne navadna spremenljivka) 
getMessage() je funkcija (metoda) tega objekta




<?php
        require_once 'config.php';
        try {
            $PDO = new PDO("mysql:host=" .$sql_host .";dbname=" .$sql_db .";charset=utf8", $sql_user, $sql_pass);
            echo "Connected to $sql_db at $sql_host successfully.";
        } catch (PDOException $pe) {
            die("Could not connect to the database $sql_db :" . $pe->getMessage());
        }
 ?>






    Rezultat izvajanja:



       Connected to SP_obrezaa at localhost successfully.


        






   Vnos zapisov v bazo s PHP


Ko je povezava z bazo vzpostavljena, lahko začnemo vanjo tudi zapisovati podatke.

Najprej bomo pokazali preprost način vnosa, nato pa še varnejši način s pripravljenimi poizvedbami (prepared statements).

Pred vnosom podatkov moramo poskrbeti, da tabela obstaja. V bazo jo vnesemo z "SQL data definition ukazom", ki ga shranimo v spremenljivko (npr $sql).
Slednjo izvedemo z ukazom na $PDO: $PDO->exec($sql);  

<?php
require_once 'config.php';

$sql = "CREATE TABLE IF NOT EXISTS imenik(
    ime VARCHAR(30) NOT NULL,
    priimek VARCHAR(30) NOT NULL,
    mesto VARCHAR(40),
    telefon VARCHAR(20) NOT NULL
)";



try {
    $PDO = new PDO("mysql:host=" .$sql_host .";dbname=" .$sql_db .";charset=utf8", $sql_user, $sql_pass);
    $PDO->exec($sql);
    echo "Tabela imenik je ustvarjena oz. že obstaja.";
} catch (PDOException $e) {
    echo "Napaka: " . $e->getMessage();
}


?>



    Rezultat izvajanja:

    Tabela imenik je ustvarjena oz. že obstaja.

Ne preveč varnen način vnosa v bazo

Podatke lahko v bazo vnesemo neposredno z  SQL poizvedbo:

<?php
$ime = "Toni";
$priimek = "Res";
$telefon = "000-111-555";
$mesto = "Ljubljana";

$stmt = $PDO->query("INSERT INTO imenik (ime, priimek, telefon, mesto)
VALUES ('$ime', '$priimek', '$telefon', '$mesto')");
?>



Tak način je problematičen iz dveh razlogov:

    pri pisanju se hitro zapletemo z narekovaji,
    tak zapis je nevaren, ker omogoča SQL injection.




Varnejši način vnosa v bazo

Varnejši način je uporaba pripravljenih poizvedb (prepared statements).

Pri tem najprej pripravimo SQL poizvedbo z označenimi mesti za podatke, nato pa podatke podamo ločeno.

<?php
$ime = "Joj";
$priimek = "Mene";
$telefon = "000-111-666";
$mesto = "Maribor";

$stmt = $PDO->prepare("INSERT INTO imenik (ime, priimek, telefon, mesto)
VALUES (:ime, :priimek, :telefon, :mesto)");

$stmt->execute([
    'ime' => $ime,
    'priimek' => $priimek,
    'telefon' => $telefon,
    'mesto' => $mesto
]);
?>



    prepare() pripravi SQL poizvedbo.
    :ime, :priimek, :telefon in :mesto so nadomestna mesta.
    execute() vstavi dejanske vrednosti in poizvedbo izvede.



   Imena nadomestnih mest (na primer :ime) lahko izberemo poljubno, vendar se morajo ujemati s ključi v polju, ki ga podamo funkciji execute(). 


    Rezultat izvajanja:

    Zapis je bil uspešno dodan v tabelo.

 Branje podatkov iz baze

Ko imamo podatke zapisane v tabeli, jih lahko iz baze tudi preberemo. Za to uporabimo SQL ukaz SELECT.

Tudi pri branju podatkov lahko uporabljamo preprost način ali pa varnejši način s pripravljenimi poizvedbami.

Če želimo prebrati vse podatke iz tabele, lahko uporabimo naslednjo enostavno poizvedbo:


<?php
$stmt = $PDO->query("SELECT * FROM imenik");
?>


Pogosto želimo izvesti dinamično iskanje, na primer vse osebe iz določenega mesta:

<?php
$mesto = "Maribor";

$stmt = $PDO->prepare("SELECT ime, priimek, telefon FROM imenik WHERE mesto = :mesto");
$stmt->execute(['mesto' => $mesto]);
?>



    prepare() pripravi SQL poizvedbo.
    :mesto je nadomestno mesto za vrednost.
    execute() poizvedbo izvede in vanjo vstavi dejanski podatek.


Rezultat poizvedbe moramo nato pretvoriti v obliko, s katero lahko delamo v PHP-ju.

<?php
$podatki = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>




    fetchAll() prebere vse vrstice rezultata.
    PDO::FETCH_ASSOC pomeni, da je vsaka vrstica predstavljena kot asociativno polje.
    Do posameznih vrednosti potem dostopamo z imeni stolpcev, na primer $zapis['ime'].










 Prikaz  podatkov 

Podatke nato izpišemo po vrsticah z zanko foreach:

<?php
$podatki = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($podatki as $zapis) {
    echo "Ime: " . $zapis['ime'] .
         ", priimek: " . $zapis['priimek'] .
         ", telefon: " . $zapis['telefon'] . "<br>";
}
?>


Podatke nato izpišemo po vrsticah z zanko foreach:

<?php
$podatki = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($podatki as $zapis) {
    echo "Ime: " . $zapis['ime'] .
         ", priimek: " . $zapis['priimek'] .
         ", telefon: " . $zapis['telefon'] . "<br>";
}
?>



    $podatki vsebuje vse vrstice rezultata.
    $zapis predstavlja eno vrstico.
    Iz posamezne vrstice preberemo vrednosti po imenih stolpcev.



    Rezultat izvajanja:

    Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666
Ime: Joj, priimek: Mene, telefon: 000-111-666



Še za konec

    Za branje podatkov iz baze uporabimo ukaz SELECT.
    Če uporabljamo parametre, poizvedbo pripravimo z prepare() in izvedemo z execute().
    Rezultate preberemo z fetchAll(PDO::FETCH_ASSOC).
    Podatke nato izpišemo z zanko foreach.