PHP PDO
Jump to navigation
Jump to search
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
- Improving the performance of queries (query creates templates - parse once!)
- Increased security against SQL Injection
- Increased readability
- Easier finding error
- 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'];
}
?>