Procedurální komunikace s databází v php

Profi programátoři používají pro komunikaci s databází samozřejmě výhradně objektově orientované příkazy. Já však nejsem „profi“ programátor, protože nemám už tolik času hrát si s kódem, takže musím jít cestou nejrychlejší a sice procedurální. Čas jsou peníze. Rychlá orientace v kódu umožňuje ušetřit hodně času. V následujícím příspěvku se pokusím shrnout nejpoužívanější příkazy pro komunikaci s databází tím nejjednodušším možným způsobem – tedy procedurálně.

Tvoříte-li mega weby s desítkami tisíc uživatelů denně, potom však raději používejte objektově orientované příkazy – jsou méně náročné na výkon serveru.

Připojení k databázi

$d = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME');
mysqli_query($d, "SET NAMES utf8");

1 2	$d = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME'); mysqli_query($d, "SET NAMES utf8");

Údaje pro připojení můžete nadefinovat takto:
define("DB_HOST", "url.vasehosuperhostingu.cz");
…

Povšimněte si, že je samozřejmostí používat mysqli_connect namísto mysql_connect. i značí improved a používá se vlastně již docela dost roků.

Databázové dotazy

$res = mysqli_query( $db, "SELECT * FROM tabulka WHERE 1" );
     
while($row = mysqli_fetch_assoc($res))
{
   $id_item  = $row['id'];
   $id_nazev = $row['nazev'];
   echo $id_nazev . '<a href="?akce=smazat&co='.$id_item.'">smazat</a><br>';
}

$res = mysqli_query( $db, "SELECT * FROM tabulka WHERE 1" );

while($row = mysqli_fetch_assoc($res))

{

$id_item = $row['id'];

$id_nazev = $row['nazev'];

echo $id_nazev . '<a href="?akce=smazat&co='.$id_item.'">smazat</a><br>';

}

mysqli_query()

První předávaný parametr této funkce je odkaz na databázi, druhým parametrem předáváme SQL příkaz. Pokud provádíme například jen UPDATE nebo smazání DELETE záznamu, žádný další příkaz nepotřebujeme spouštět. Avšak chceme-li získat data, musíme zpracovat návratové hodnoty, tzv. result set. K tomu slouží řada funkcí. Osobně nejčastěji používám mysqli_fetch_assoc().

mysqli_fetch_assoc()

Funkce vrací asociativní pole, tzn. v našem případě $row.
Jednotlivé prvky pole jsou asociovány s názvy sloupců v dané tabulce. V SQL příkazu SELECT jsme vybrali všechny sloupce zástupným znakem *. Stačí tedy vědět jaké jsou názvy sloupců a můžeme s nimi přímo pracovat jejich uvedením v hranaté závorce $row[‚id‘], $row[‚nazev‘], apod.

mysqli_fetch_row()

Někdy se může hodit načítání pomocí číselných indexů, zvláště chceme-li vykázat více hodin času stráveného kódováním (just kidding) anebo třeba pro načtení jednoho řádku.

$res = mysqli_query( $db, "SELECT id, nazev FROM tabulka WHERE id=232" );

$row = mysqli_fetch_row($res);
printf ("%s (%s)\n", $row[0], $row[1]);

$res = mysqli_query( $db, "SELECT id, nazev FROM tabulka WHERE id=232" );

$row = mysqli_fetch_row($res);

printf ("%s (%s)\n", $row[0], $row[1]);

mysqli_fetch_array()

Tato funkce ideální pro ty, kdo používají často jak číselné indexy, tak i spojení slovní.
$row[0] obsahuje stejnou hodnotu jako $row[‚id‘] – jde o ekvivalentní výrazy. Pokud byste chtěli vynutit používání pouze číselných indexů nebo slovních asociací, můžete tak učinit následujícím způsobem. V praxi jsem se však nesetkal s nutností něco takového činit.

mysqli_fetch_array($res, MYSQLI_NUM);//pracuje pouze s indexy

mysqli_fetch_array($res, MYSQLI_ASSOC); // pracuje pouze s názvy sloupců

Jak už možná tušíte, první z výše uvedených příkazů je ekvivalentní příkazu mysli_fetch_row a druhý příkazu mysqli_fetch_assoc.

Standardně se data ukládají do pole tak jako v předchozím případě (viz mysqli_fetch_row)

$row = mysqli_fetch_array($res);
echo $row['0'];
echo $row['nazev'];

$row = mysqli_fetch_array($res);

echo $row['0'];

echo $row['nazev'];

anebo je lze příkazem list() načíst rovnou do proměnných

list( $id_item , $id_nazev ) = mysqli_fetch_array($res);

1	list( $id_item , $id_nazev ) = mysqli_fetch_array($res);

mysqli_affected_rows()

Tímto příkazem zjistíme počet ovlivněných řádků přechozím dotazem na databázi.

$res = mysqli_query( $db, "DELETE * FROM tabulka WHERE id=232" );

if( mysqli_affected_rows($db) >= 1 ){
   echo "záznam smazán";
}

$res = mysqli_query( $db, "DELETE * FROM tabulka WHERE id=232" );

if( mysqli_affected_rows($db) >= 1 ){

echo "záznam smazán";

}

mysqli_num_rows()

Potřebujeme-li zjistit kolik záznamů vyhovilo dotazu, používá se většinou právě tato funkce.

<?php 
    $res = mysqli_query($db, "SELECT * FROM tabulka WHERE cena > 1000000 "); 
      
    if ($res) 
    { 
         $pocet = mysqli_num_rows($res); 
          
           if ($pocet) 
              { 
                 printf("Dotazu vyhovuje " . $pocet . " záznamů."); 
              } 
        mysqli_free_result($result); 
    } 
  
    mysqli_close($connection); 
?>

<?php

$res = mysqli_query($db, "SELECT * FROM tabulka WHERE cena > 1000000 ");

if ($res)

{

$pocet = mysqli_num_rows($res);

if ($pocet)

{

printf("Dotazu vyhovuje " . $pocet . " záznamů.");

}

mysqli_free_result($result);

}

mysqli_close($connection);

mysqli_free_result()

Nebudete-li tento příkaz používat, zdánlivě se nic neděje. Avšak čím více dotazů probíhá při jednom načtení stránky, tím vyšší je potom zatížení serveru. Neuvolníte-li vždy result set tímto příkazem, může dojít k viditelnému zpomalení načítání stránek už při několika desítkách simultálně klikajících uživatelů.

Zabránění SQL Injection

Nejmenší riziko napadení databáze pomocí příkazu vloženého na vstup lze zajistit pomocí prepared statements.
Níže je příklad s INSERT.
Tajemné ‚si‘ znamená, že první očekávaný parametr je string, druhý integer. (Pro úplnost: pracovat lze také s double d, blob b.)

$insertStatement = mysqli_prepare($datab,'INSERT INTO catch404 (url,num) VALUES (?,?)');
mysqli_stmt_bind_param($insertStatement, 'si', $nenalezena_stranka, $pocet);
mysqli_stmt_execute($insertStatement);
mysqli_close($datab);

$insertStatement = mysqli_prepare($datab,'INSERT INTO catch404 (url,num) VALUES (?,?)');

mysqli_stmt_bind_param($insertStatement, 'si', $nenalezena_stranka, $pocet);

mysqli_stmt_execute($insertStatement);

mysqli_close($datab);

Prepared Statements – ukázky

Další příklad s prepared statement je obyčejný dotaz. Avšak v případě, kdy neposíláme do databáze žádná data, je riziko injection nulové, takže jde hlavně o zajištění efektivnější práce s databází (je rychlejší).

$allStatement = mysqli_prepare($conn,'SELECT * FROM customers');
mysqli_stmt_execute($allStatement);
$res = mysqli_stmt_get_result($allStatement);

while($rows = mysqli_fetch_assoc($res))
{
	print_r($rows);
}

$allStatement = mysqli_prepare($conn,'SELECT * FROM customers');

mysqli_stmt_execute($allStatement);

$res = mysqli_stmt_get_result($allStatement);

while($rows = mysqli_fetch_assoc($res))

{

print_r($rows);

}

Získání záznamů s více parametry

$vek = 65;
$firstName = "Andrew";
$lastName = "Bab%";

$sql = 'SELECT * FROM agents WHERE vek = ? AND first_name = ? AND last_name LIKE ? ';

$userStatement = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($userStatement, 'iss', $vek, $firstName, $lastName);
mysqli_stmt_execute($userStatement);

$res = mysqli_stmt_get_result($userStatement);

while($rows = mysqli_fetch_assoc($res))
{
  print_r($rows);
}

$vek = 65;

$firstName = "Andrew";

$lastName = "Bab%";

$sql = 'SELECT * FROM agents WHERE vek = ? AND first_name = ? AND last_name LIKE ? ';

$userStatement = mysqli_prepare($conn, $sql);

mysqli_stmt_bind_param($userStatement, 'iss', $vek, $firstName, $lastName);

mysqli_stmt_execute($userStatement);

$res = mysqli_stmt_get_result($userStatement);

while($rows = mysqli_fetch_assoc($res))

{

print_r($rows);

}

Zápis více záznamů

$insertQry = 'INSERT INTO agents (first_name, last_name, address, email, phone, country, city) VALUES (?,?,?,?,?,?,?)';
$insertStatement = mysqli_prepare($datab,$insertQry);
mysqli_stmt_bind_param($insertStatement,'sssssss',$firstName, $lastName, $address, $email, $phone, $country, $city);

$firstName = 'Eliash';
$lastName = 'Vykuk';
$address = '90 Shelley Terrace';
$email = 'vykuk@ramram.tv';
$phone = '797-800-2312';
$country = 'China';
$city  = 'Dongling';
mysqli_stmt_execute($insertStatement);


$firstName = 'Ariela';
$lastName = 'Fuge';
$address = '2725 Manufacturers Hill';
$email = 'afuge1@rediff.com';
$phone = '494-246-3738';
$country = 'Russia';
$city  = 'Dzhubga';
mysqli_stmt_execute($insertStatement);


$firstName = 'Yolande';
$lastName = 'Gillbey';
$address = '338 Sunfield Center';
$email = 'ygillbey2@npr.org';
$phone = '869-198-7138';
$country = 'Poland';
$city  = 'Kamionka Wielka';
mysqli_stmt_execute($insertStatement);

mysqli_close($datab);

$insertQry = 'INSERT INTO agents (first_name, last_name, address, email, phone, country, city) VALUES (?,?,?,?,?,?,?)';

$insertStatement = mysqli_prepare($datab,$insertQry);

mysqli_stmt_bind_param($insertStatement,'sssssss',$firstName, $lastName, $address, $email, $phone, $country, $city);

$firstName = 'Eliash';

$lastName = 'Vykuk';

$address = '90 Shelley Terrace';

$email = 'vykuk@ramram.tv';

$phone = '797-800-2312';

$country = 'China';

$city = 'Dongling';

mysqli_stmt_execute($insertStatement);

$firstName = 'Ariela';

$lastName = 'Fuge';

$address = '2725 Manufacturers Hill';

$email = 'afuge1@rediff.com';

$phone = '494-246-3738';

$country = 'Russia';

$city = 'Dzhubga';

mysqli_stmt_execute($insertStatement);

$firstName = 'Yolande';

$lastName = 'Gillbey';

$address = '338 Sunfield Center';

$email = 'ygillbey2@npr.org';

$phone = '869-198-7138';

$country = 'Poland';

$city = 'Kamionka Wielka';

mysqli_stmt_execute($insertStatement);

mysqli_close($datab);

Smazání záznamu

$id = 14;

$deleteQry = "DELETE FROM agents WHERE id = ?";
$deleteStatement = mysqli_prepare($datab, $deleteQry);

mysqli_stmt_bind_param($deleteStatement, 'i', $id);
mysqli_stmt_execute($deleteStatement);

echo mysqli_affected_rows($datab);

$id = 14;

$deleteQry = "DELETE FROM agents WHERE id = ?";

$deleteStatement = mysqli_prepare($datab, $deleteQry);

mysqli_stmt_bind_param($deleteStatement, 'i', $id);

mysqli_stmt_execute($deleteStatement);

echo mysqli_affected_rows($datab);

Mohlo by vás zajímat:

Praktické využití UNION a JOIN v MySQL

mySQL - ON DELETE CASCADE

MySQL - složené dotazy na více tabulek najednou

PHP Developer

Připojení k databázi

Databázové dotazy

mysqli_query()

mysqli_fetch_assoc()

mysqli_fetch_row()

mysqli_fetch_array()

mysqli_affected_rows()

mysqli_num_rows()

mysqli_free_result()

Zabránění SQL Injection

Prepared Statements – ukázky

Mohlo by vás zajímat: