Complete PHP Course - PDO

Welcome everyone to the PDO – PHP Data Objects course

Requirement: Before starting the PDO course, I recommend that you watch all the classes from the course on
HTML, php, MySQL Database.
Class Titles + links below:
PDO Course - Class 01 - Introduction
PDO Course – Class 02 – Connection with PDO
PDO Course - Class 03 - Selecting Data with PDO
PDO Course - Class 04 - Registering Data with PDO
PDO Course - Class 05 - Deleting Data with PDO
PDO Course - Class 06 - Updating Data with PDO

Class 01 - Introduction
Today we will start our exclusive PDO (PHP Data Objects) course.
PDO has been dominating the market in the world of PHP programmers, and for this reason, it cannot
to fall behind, correct?
In this course we will learn how to make a CRUD (create | read | update | delete).
delete) complete with PDO, and more. We will also make comparisons with the primitive mode of
PHP, this way we will all address the differences between both.
What is the advantage of using PDO?
Imagine that you created a system using the primitive mode of PHP, and the type of database
the MYSQL was used.
After a long time, your client tells you that the database will now be of the ORACLE type.
Particularly, you would have to change several pages, right?
With PDO it's different, as it works with database drivers, that is, for
solve your client's problem using PDO, you just need to modify the file of
connection to the database, informing the desired driver.
With PDO, we can feel more secure regarding vulnerabilities, as with the use of
By doing so, we can avoid SQL Injection situations.
Are you enjoying it? Keep following the course, as this is just the beginning.
Would you like to buy an online course with us, but do not know our virtual campus? Visit:
http://www.escolacompleta.com/campus
Continuation… let's go to a quick introduction to PDO.
Introduction
PHP Data Objects (PDO) defines a lightweight interface in PHP. Each database driver that
implementing the PDO interface can expose specific resource data. Note that you cannot
execute any database functions using the PDO extension, by itself, you must use a
specific PDO driver database to access a database server.
PDO provides an abstract data access, which means that, regardless of which database
The data you are using, you use the same functions to issue queries and retrieve data (CRUD).
Lesson 02 - Connection with PDO
In this lesson, we will learn how to create a connection to the database using PDO, remembering
we will make comparisons between the primitive mode and PDO mode.
In fact, a primitive connection without using PDO would look like the example below:
<?php
localhost

root
pass

test_bank

Connection = mysql_connect($host,$user,$pass) OR DIE ("Error connecting to the

.mysql_error());

if($connection){
mysql_select_db($bd) OR DIE ("Error selecting database:
.mysql_error());

?>

In a certain way, the primitive mode is less complex and faster to develop, however
there are disadvantages.
One of them is vulnerability;
Another issue is the famous spaghetti code;
And one more is the long time issue for changes in types of databases.
See now how to create your database connection using PDO, based on the example
above.
<?php
localhost

root
pass

test_bank
What does PDO ask for in the connection?

ex: PDO($dsn,$user,$pass);
but what is dsn?

database_type:host='your_host';dbname='your_database_name';
*/

mysql:host=
try{

$connection = new PDO($dsn, $user, $pass);

$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

return $connection;
}catch(PDOException $error){

Error connecting to the database:

}

?>

What does PDO require differently in the connection?

ex: PDO($dsn, $user, $pass);
How does the structure of the DSN work?
The structure of the dsn works as follows:
db_type:host=your_host;dbname=your_database_name
$dsn = "mysql:host=localhost;dbname=test_bank";
So far so good... but what does this line mean:
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

setAttribute – Defines an attribute in the declaration. To see the list of attributesclick here.
PDO::ATTR_ERRMODE - error report.
PDO::ERRMODE_EXCEPTION - Child attribute of the attribute ATTR_ERRMODE, this represents
an error generated by PDO.
Understanding the attributes
When you define an attribute in PDO, they have default values and unique children.
The syntax for using it works as follows:
setAttribute(attribute, attribute_child);
For this, see the list below, which sets the attributes and their outside children.
1. PDO::ATTR_CASE – column names force to a specific case.
oChildren of the attribute PDO::ATTR_CASE
o PDO::CASE_LOWER - Forces column names to lowercase.
oPDO::CASE_NATURAL - keep column names as returned by
database driver.
oPDO::CASE_UPPER - column names force to uppercase.
2. PDO::ATTR_ERRMODE – error reporting.
oChildren of the attribute PDO::ATTR_ERRMODE
o PDO::ERRMODE_SILENT - Just define error codes.
oPDO::ERRMODE_WARNING - Triggers a warning E_WARNING.
oPDO::ERRMODE_EXCEPTION - Throw exceptions.
3. PDO::ATTR_ORACLE_NULLS – (available with all drivers, and not just with the
Oracle): Conversion of null and empty strings.
oChildren of the attribute PDO::ATTR_ORACLE_NULLS
oPDO::NULL_NATURAL – No conversion.
oPDO::NULL_EMPTY_STRING - empty string is converted to NULL.
oPDO::NULL_TO_STRING – NULL is converted to an empty string.
There are many attributes and their counterparts, for this learn more on the official PHP website.clicking here.
Now that we know more about the attributes, we should learn about this line:
catch(PDOException $error){

What is PDOException?
It represents an error generated by PDO.
In this way, we are assigning o error generated by PDO à variable $error.
In this way, we should only call the error message using the variable $error.
Note that to display the generated error message, I used:
$error->getMessage();

But what is getMessage()?

getMessage() — Gets the exception message.

Lesson 03 - Selecting Data with PDO

In this lesson, we will learn how to make the selection, or listing of data with PDO.
Remembering that we will make the necessary comparisons and observations with the primitive mode of PHP.
First, we have the first example, where we will select the data using the mode.
primitive of php.
See the example below:
<?php
include_once("connection.php"); /*including connection file from class
past

SELECT * FROM students

$query = mysql_query($sql);

if(mysql_num_rows($query) > 0){

while($linha_alunos = mysql_fetch_array($query)){
echo $linha_alunos['student_name'].'<br />';

?>

Simple, right?
Remember that we included the connection file from the last class.
If you did not attend the last classclick here.
Now that you know how data selection works using the primitive mode, we will now
do the same using PDO.
First, we will make a regular selection without using conditions (WHERE).
COMMON SELECTION WITH PDO
<?php
include_once("connection.php");

function list_students() {
try{

$listar = $connection->query("SELECT * FROM students");

$listar->execute();

if($listar->rowCount() > 0){

return $listar->fetchAll(PDO::FETCH_ASSOC);
else

There are no students registered in the database.

}

catch(PDOException $error){
Error listing students:

}
}

list_students();

foreach($students as $student):
echo $student['student_name']."<br />";

endforeach;
?>

You could also use FETCH_OBJ instead of FETCH_ASSOC, however our foreach
it would be like this:
foreach($students as $student):
echo $student->student_name . "<br />";

endforeach;

The result of the entire code using FETCH_OBJ would be:

<?php
include_once("connection.php");//inclusion of the connection file from the class
passed

function list_students() {
try{

$list = $connection->query("SELECT * FROM students");

$listar->execute();

if($listar->rowCount() > 0){

return $listar->fetchAll(PDO::FETCH_OBJ);
}

There are no students registered in the database.

}

}catch(PDOException $error){
Error listing students:

}
}
$students = list_students();

foreach($students as $student):
echo $student->student_name."<br />";

endforeach;
?>

COMPOSITE SELECTION WITH PDO

The composed selection is already different, as we will use

conditions, and we must use the prepare of
PDO.
The <strong>query</strong> method is used only for common selections that do not
involve conditions (WHERE).

Example of composite selection:

<?php
include_once("connection.php"); // inclusion of the connection file from the class
past

function list_students_with_condition() {
try{

$idade = 15;/*aqui defini a idade desejada para condicao*/

$listar = $connection->prepare("SELECT * FROM students WHERE age = :age");

The bindValue protects the data that

you are passing, set the value for the parameter.
$listar->execute();

if($listar->rowCount() > 0){

return $listar->fetch(PDO::FETCH_ASSOC);
else

There are no students registered in the database.

}

}catch(PDOException $error){
Error listing students:

}
}

$students = list_students_with_condition();

foreach($students as $student):
echo $student['student_name'] . "<br />";
endforeach;
?>

Note that in the example above, I did not use fetchAll, but rather just fetch, because I am
wanting to return just a specific value stated in my 'condition'.
Remembering also that instead of using bindValue, we can also use bindParam, which will
return the same value, however you can also specify the type of the parameter (type)
the past and the size (length).
Example:
$listar->bindParam(":idade", $idade, PDO::PARAM_INT, 3);

In this way, I am specifying that the value passed is of the integer type (PARAM_INT), and the size is
3. That is, there is no person older than 3 digits, correct? (Please do not include the
mummies in this roll).
And for string types? How would it be?

$listar->bindParam(":aluno_nome", $aluno_nome, PDO::PARAM_STR, 255);

In the above example, I am informing that the parameter student_name is of the type
string(PARAM_STR) and the maximum length is 255 characters.
If you want to know more about bindParam'sclick here
Now, I will show a situation that many students have doubts about. The famous question mark.
When we pass parameters, we can also use the question mark, and right after it
position of the index that we intend to assign the current value.
Example:
<?php
include_once("connection.php");

function list_students_with_condition() {
try{

$idade = 15;
$listar = $connection->prepare("SELECT * FROM students WHERE age = ?");

$listar->bindValue(1,$age);
$listar->execute();

if($listar->rowCount() > 0){

return $listar->fetch(PDO::FETCH_ASSOC);
else

There are no students registered in the database.

}

}catch(PDOException $error){
Error listing students:

}
}

list_students_with_condition();

foreach($students as $student):
echo $student['student_name'] . "<br />";

endforeach;
?>

Note that the modified lines are the ones listed below:
$listar = $conexao->prepare("SELECT * FROM students WHERE age = ?");

$listar->bindValue(1, $age);/*The bindvalue protects the

However, the question remains. What if I wanted to pass two parameters in the condition? How
would it stay?
See the example below:
<?php
include_once("connection.php");

function list_students_with_condition() {
try{

$idade = 15;
Alberto

The list = $connection->prepare("SELECT * FROM students WHERE age = ? AND

last name = ?
$listar->bindValue(1,$idade);

$listar->bindValue(2,$lastName);
$listar->execute();

if($listar->rowCount() > 0){

return $listar->fetch(PDO::FETCH_ASSOC);
else

There are no students registered in the database.

}

}catch(PDOException $error){
Error listing students:

}
}

students = list_students_with_condition();
 foreach($students as $student):
echo $student['student_name'] . "<br />";

endforeach;
?>

Concluding our example, look at the changed lines.

Last name
$listar = $connection->prepare("SELECT * FROM students WHERE age = ? AND last_name ="
?");

$listar->bindValue(1,$idade);/*The bindvalue protects the data that you are

passing.
$listar->bindValue(2, $lastName);

Do you see how simple it is to perform a selection with PDO?

Class 04 - Registering Data with PDO

In this class, we will learn how to make registrations using PDO.
First, we will see an example using the primitive mode of PHP.

REGISTERING WITHOUT USING PDO

<?php
include_once("connection.php");/*Includes connection file with the db*/

Carlos
Alberto

INSERT INTO students (first_name, last_name) VALUES ('$first_name','$last_name')

mysql_query($sql);

if($query){
Student successfully registered!

else
Error registering student.

}
?>

See that the registration of the primitive becomes totally vulnerable.

See now, the above example using PDO.
REGISTERING USING PDO

<?php
include_once("connection.php");
Carlos
Alberto

try{

$register = $connection->prepare("INSERT INTO students (name, surname)")

VALUES (:name, :surname)

$cadastrar->bindValue(":name", $name);
$cadastrar->bindValue(":last_name", $last_name);

$cadastrar->execute;
if($cadastrar->rowCount() > 0){

Student successfully registered!

}

Error registering student.

}

}catch(PDOException $error){
Error registering student:

}
?>

Remembering that with the use of PDO, I did not need to put the parameter in quotes.
simple, as it was used in primitive mode.
See:
$cadastrar = $conexao->prepare("INSERT INTO students (first_name,last_name)");
VALUES (:first_name,:last_name);

We also saw that I used a different conditional, see:

if($register->rowCount() > 0){
Student registered successfully!

else
Error registering student.

The question remains: But does rowCount not only work for selecting data?
Answer: No! The rowCount, in this case, is functioning like the old mysql_affected_rows, which
returns the number of affected rows.
In our case, 1 registration was successfully executed, resulting in the message 'Student
successfully registered!
Remember that we could also register using the question marks.
Example:
<?php
include_once("connection.php");
Carlos
Alberto

try{

$register = $connection->prepare("INSERT INTO students (name, surname)")

VALUES (?,?)

$cadastrar->bindValue(1,$name);
$cadastrar->bindValue(2,$lastName);

$cadastrar->execute;
if($cadastrar->rowCount() > 0){

Student successfully registered!

}else{

Error when registering student.

}

catch(PDOException $error){
Error registering student:

}
?>

REGISTERING WITH PDO USING ARRAY

<?php
include_once("connection.php");

Carlos
Alberto

$data = array(

$name
$sobrenome

);

try{
$register = $connection->prepare("INSERT INTO students (first_name, last_name)")

VALUES (:name,:surname)
foreach($data as $index => $value):

$cadastrar->bindValue(:$index, $value);
endforeach;

$cadastrar->execute;
if($cadastrar->rowCount() > 0) {

Student registered successfully!

} else {

Error registering student.

}

}catch(PDOException $error){
Error registering student:

}
?>

REGISTERING WITH PDO USING ARRAY AND QUERY

<?php
include_once("connection.php");

Carlos
Alberto

array(

$name
$lastname

);

try{
$cadastrar = $conexao->prepare("INSERT INTO students (first_name, last_name)")

VALUES (?,?)
foreach($data as $index => $value):

$cadastrar->bindValue($index, $value);
endforeach;

$cadastrar->execute;
if($cadastrar->rowCount() > 0){

Student registered successfully!

}else{

Error registering student.

}

}catch(PDOException $error){
Error registering student:

}
?>

It's simple, easy, and safe to use PDO to perform our INSERT, right?

Class 05 - Deleting Data with PDO

In today's class, we will learn how to delete data from the database using the super PDO =)
First, like every lesson, let's look at an example using the primitive or simple mode of
php, as many say.

DELETING WITHOUT USING PDO

<?php
include_once("connection.php");

$matricula = $_GET['matricula'];
DELETE * FROM students WHERE registration = '$registration'

mysql_query($sql);

if($query){
Student deleted successfully!

else
Could not delete the student.

}
?>

Do you remember how the primitive mode of PHP was used? Now, see the same using PDO.
DELETING USING PDO

<?php
include_once("connection.php");

try{
matricula = $_GET['matricula'];

$delete = $connection->prepare("DELETE * FROM students WHERE enrollment =

:enrollment
$delete->bindValue(":registration", $registration);

$delete->execute();
if($deletar->rowCount() > 0){
Student deleted successfully!

else
Could not delete the student.

}
}catch(PDOException $error){

Error deleting student:

}

?>

REMEMBERING ALSO, THAT WE COULD USE THE FAMOUS POINTS OF

INTERROGATION TO DELETE.
Example:
< ?php
include_once("connection.php");

try{
$matricula = $_GET['matricula'];

$delete = $connection->prepare("DELETE * FROM students WHERE enrollment = ?");

$delete->bindValue(1,$registration);

$deletar->execute();

if($deletar->rowCount() > 0){

Student deleted successfully!

else
It was not possible to delete the student.

}
}catch(PDOException $error){

Error deleting student:

}

?>

Simple and uncomplicated.

Class 06 - Updating Data with PDO

Today we will then finish our CRUD (create - read - update - delete = create - read - update -
delete).
In today's class, we will learn how to update data using PDO. Keeping the routine exposed in
All the course classes, first I will show the traditional or primitive way of PHP. Soon.
after, the same example using PDO.

UPDATING DATA WITHOUT USING PDO

<?php
include_once("connection.php");/*Includes connection file with the database*/

$matricula = $_POST['matricula'];
$name = $_POST['name'];

$lastName = $_POST['lastName'];

UPDATE students SET

name = '$name',

$lastName
WHERE

$matricula
";

$query = mysql_query($sql);

if($query){
Student updated successfully!

else
Error updating student.

}
?>

UPDATING DATA USING PDO

<?php
include_once("connection.php");/*Includes database connection file*/

$matricula = $_POST['matricula'];
$name = $_POST['name'];

$surname = $_POST['surname'];

UPDATE students SET

:name

last name
WHERE

:matricula
");

$atualizar->bindValue(':nome', $nome);
$atualizar->bindValue(":sobrenome", $sobrenome);

$atualizar->bindValue(":matricula", $matricula);
$atualizar->execute;

if($atualizar->rowCount() > 0){

Student updated successfully!

else

Error updating student.

}

?>

QUESTION: COULD WE USE QUESTION MARKS? YES.

SEE:

<?php
include_once("connection.php");/*Includes database connection file*/

$matricula = $_POST['matricula'];
_POST['nome']

$surname = $_POST['surname'];

$update = $connection->prepare("UPDATE students SET

name

surname = ?
WHERE

enrollment = ?
$atualizar->bindValue(1,$name);

$atualizar->bindValue(2, $lastName);
$atualizar->bindValue(3, $matricula);

$updater->execute;

if($atualizar->rowCount() > 0){

Student updated successfully!

else
Error updating student.

}
?>

Question: Could we use arrays to make such an update? It would be great for making multiple.
data updates correct?
Yes, we can use arrays. And, yes, it would be great for multiple data manipulation.
EXAMPLE USING ARRAY – WITHOUT USING QUESTION MARKS.
<?php
include_once("connection.php");/*Includes database connection file*/

$matricula = $_POST['matricula'];
$name = $_POST['name'];

$lastName = $_POST['lastName'];

array(
$nome

$sobrenome
$matricula

);

$update = $connection->prepare("UPDATE students SET

:name

:sobrenome
WHERE

:matricula
");

foreach($data as $index => $value):

$atualizar->bindValue(":$indice", $valor);

endforeach;
$atualizar->execute;

if($atualizar->rowCount() > 0){

Student successfully updated!

}

Error updating student.

}

?>

EXAMPLE USING ARRAY - USING QUESTION MARKS.

<?php
include_once("connection.php");/*Includes database connection file*/

$matricula = $_POST['matricula'];
$name = $_POST['name'];

$surname = $_POST['surname'];

array(
$name

2 => $lastname,
$matricula

);

UPDATE students SET

?

last name = ?
WHERE

enrollment = ?
foreach($dados as $index => $value):

$atualizar->bindValue($index,$value);
endforeach;

$updater->execute;

if($update->rowCount() > 0){

Student updated successfully!

else
Error updating student.

}
?>

Do you see how simple it is to make updates using PDO?