PHP PDO

PHP PDO

Check your possibility connection

Use code and read with which database you can connect:

        <?php
             print_r(PDO::getAvailableDrivers());
        ?>

Create table in database

        Create table abc (
                id integer(11),
                name varchar(12));

Connect php with mysql by PDO

If all is right you see empty site:

        <?php
             $pol = 'mysql:host=localhost;dbname=test';
             $user = 'root';
             $pass = 'root1';
             $db_lacz = new PDO($pol, $user, $pass);
?>

PDO with exception

In this case i generate error with connection(bad dbname). Exception support this error.

<?php
    $pol = 'mysql:host=localhost;dbname=test1';
    $user = 'root';
    $pass = 'root';
 
 try {
     $db_lacz = new PDO($pol, $user, $pass);
     
}catch(PDOException $e){
        echo 'Error PDO: <br>'.$e->getMessage().' <br>'
                . '(number of '.$e->getCode().') in line: '
                .$e->getLine();
}
?>

Table as array

<?php
$pol = 'mysql:host=localhost;dbname=test';
    $user = 'root';
    $pass = 'root';
 
 try {
     $db_lacz = new PDO($pol, $user, $pass);
     
     $sql = 'SELECT * FROM abc ';
     $kod = $db_lacz->query($sql); //returns an instance of the class PDO STATMENT
     foreach($kod->fetchAll() as $row)
     {
         echo $row['id'].' || '.$row['name'];;
         echo '<br>';

} 

     
}catch(PDOException $e){
        echo 'Error  PDO: '.$e->getMessage().' (number '.$e->getCode().') in line: '.$e->getLine();
}?>

Table as multidimensional array

<?php
    $pol = 'mysql:host=localhost;dbname=test';
    $user = 'root';
    $pass = 'root';
 
 try {
     $db_lacz = new PDO($pol, $user, $pass);
     
     $sql = 'SELECT * FROM abc ';
     $kod = $db_lacz->query($sql); //returns an instance of the class PDO STATMENT
     print_r($kod->fetchAll());
     
}catch(PDOException $e){
        echo 'Error  PDO: '.$e->getMessage().' (number '.$e->getCode().') in line: '.$e->getLine();
}?>

SQL Statements

Why use SQL Statemants

1. Improving the performance of queries (query creates templates - parse once!)
2. Increased security against SQL Injection
3. Increased readability
4. Easier finding error
5. Easy to construct complex queries

Preapare SQL Statements (notation with :)

If you want use this, add different variable (prefix-> :) to support values from GET or POST. All variables need a diffrent name.

<?php
    //don't forget use code to connect with database

    $sql = 'SELECT * FROM abc WHERE id=:preare_id'; //:prepare_id is variable to prepare sql statements
    $kod = $db_lacz->prepare($sql); //returns an instance of the class PDO STATMENT 
     
     $kod->execute(array(
            'prepare_id' => $_GET['id'] //use prepare variable to take $GET value
        ));
     print_r($kod->fetchAll());
?>

Example indunce in browser: http://your_adress?id=5

Preapare SQL Statements (diffrent notation)

<?php
    //don't forget use code to connect with database

     $sql = 'SELECT * FROM abc WHERE id=?'; //? can be change to other char
     $kod = $db_lacz->prepare($sql); 
     
     $kod->execute(array(2)); //indunce second id from table
            
     foreach($kod as $row){
            echo $row['id'].' || '.$row['name'];
     }
?>

Insert to table

<?php
    //don't forget use code to connect with database
   
     $sql_inst = 'INSERT INTO abc VALUES(?, ?)';
     $kod = $db_lacz->prepare($sql_inst);
     echo 'Count of insert: '.$count= $kod->execute(array(6,"rrr")); //in instruction DML use excecute syntax
?>

This same code you can use with update or delete in SQL

bindParam

It gives you the possibility of connecting data to a particular variable with the change of its type

<?php
    //don't forget use code to connect with database
   
     $db_lacz = new PDO($pol, $user, $pass);
     $name = 222;
     $sql = 'SELECT * FROM abc WHERE name=?'; 
     $kod = $db_lacz->prepare($sql); 
     $kod->bindParam(1, $name, PDO::PARAM_STR); //bind $name variable to first "?" and change type this for string
     $kod->execute(); 
 
     foreach($kod as $row){
            echo $row['id'].' || '.$row['name'];
     }
?>

This is place where you find all parameters to change type.