PHP 0 to hero pt.12 - Using PDO (SQL pt.4 )

Welcome again - today we continue our journey with using SQL databases in PHP

Today I'll show you pre-installed extension to PHP called PDO

It's (in my opinion) better way to connect to the database
So what will we learn today?

1. What is PDO
2. Why should you use it
3. A little bit about exceptions and their handling

And as I said in the previous part - we'll create the login script again - but today with PDO.

So, with this said - let's go!

What is PDO and why should you use it

To make it short and clear.
It's an interface for accessing databases in PHP. Unlike mysqli or pg, that are meant to be used with specific DBMS, PDO works with every more popular one.

So, no matter if we use MySQL, PostgreSQL or Oracle - the code is exactly the same in every single one.
That's the biggest benefit - we don't have to re-write the entire codebase just to migrate from ex. MySQL to PostgreSQL. We just change a few settings and we are good to go

Another thing are the exceptions - with native interfaces we have to detect that something it wrong and then throw excepition
But PDO does it on it's own - our role is to catch, and handle it

But, what are those exceptions?

Quick intro to exceptions

This won't be long - for more info and deeper understanding I encourage you to check out this article purely about them

Exceptions are built-in mechanism that allows us to perfectly deal with every possible error, that might occur

For example it we write a calculator and somehow value passed as divisor is equal to 0, instead of dividing and crashing the whole website - code just throws an exception and another block handles it in the way we want to

// Let's say somewhere before we declarated variable called $divider

try{
    $dividedValue = $val/$divider;
    // Rest of the code...
}catch(DivisionByZeroError $e){
    echo "Warning. Division by zero - execution terminated";
    die();
}

// OR
try{
    if($divider == 0){
        throw new DivisionByZeroError();
    }
    // Rest of the code...
}catch(DivisionByZeroError $e){
    echo "Warning. Division by zero - execution terminated";
    die();
}

This snippets do exactly what we talked about

Again - to learn more, check out linked article and now let's write our login script in PDO

Writing login script

The only thing that is different is DB connection - we can copy the rest

Copy index.php, secret.php and this from login.php

<?php
session_start();

if(empty($_POST['username'])){
    $_SESSION['error'] = "No username specified";
    header("Location: index.php");
    die();
}

if(empty($_POST['password'])){
    $_SESSION['error'] = "No password specified";
    header("Location: index.php");
    die();
}

$username_s = htmlentities($_POST['username'], ENT_QUOTES, "UTF-8");
$password_s = htmlentities($_POST['password'], ENT_QUOTES, "UTF-8");

try{
   // rest of the code
}catch(PDOException $e){
    $_SESSION['error'] =  "Server error - ".$e;
    header("Location: index.php");
}

I've also added try-catch block for eventual errors and exceptions
Right now, we want message displayed. On typical (production) server it should never happen - for security reasons

We'll write rest of the code in place of comment - so inside try block

With this said let's start coding

Creating DB connection

First - create DB connection. But today with PDO

$db = new PDO("mysql:host=localhost;dbname=firstproject", "root", "");

You see, instead of 4 it takes only 3 arguments - but the first one looks strange - let's analyze it

This is DSN (Data source name) - that's a fancy name for a data structure (here string) that contains info necessary for PDO to connect to database
(To be more specific, it's needed for ODBC driver - the actual API for DB connection, which is used by PDO)

So let's see what contains this string -mysql:host=localhost;dbname=firstproject
I'll divide it into 3 parts

1. mysql: - indicated used DBMS
2. host=localhost; - Where is database located
3. dbname=firstproject - database name

There are more of those declarations - you can check them here

These are the only necessary ones for us - I hadn't specify port. We don't need it, as XAMPP (and your manual installation) uses default one - 3306 if you want to know

Right now, we don't need to check errno inside PDO, as if something goes wrong - PDO will automatically throw an exception
Comfy, isn't it

Executing query

First - write the query (or copy it - as it's the same one)

$sql = "SELECT * FROM `users` WHERE `username` = ?";

We use prepared statements again - so let's write it

Start with preparing the query

    $stmt = $db->prepare($sql);

Then let's execute it

    $stmt->execute([$username_s]);

But hold on a second - don't we have to bind the parameters?

Actually, we do - but not like before. Let's have a look at previously called function

As you see, we pass as it's parameter $username_s - so we do bind that parameters, but in execute(), not with bindParam()

And why it's in square brackets? Because, execute() only takes one parameter - and it's an array of arguments

Wrapping $username_s in square brackets we create an array with only one element - username

But if our query required more parameters - we can add something to it, like this:

$query->execute([$arg1, $arg2, $arg3]);

(that's just example - not our code element)

Of course, there is a function in PDO such as bindParam, and we can do regular binding

In this case - I'd look like this

$stmt->bindParam(1, $username_s, PDO::PARAM_STR);

First parameter means to which ? should this be bind
Second is actual value
And third - type of an argument, but using PDO constants. You can check their list here

Obtaining results

So now, we have something left

1. Check if we got something returned by query
2. Check if passwords match

First - we should get the results

    $results = $stmt->fetch(PDO::FETCH_ASSOC);

This function (fetch) will get us 1 resuls from the query in a format specified as an argument

And an argument is another PDO constant - here I've chosen associative array. But it can be

• PDO::FETCH_NUM - an array indexed with numbers, not column names
• PDO::FETCH_OBJ - and object with variables inside, named correspondingly to column names
• PDO::FETCH_ASSOC - an associative array.

Now, let's check if we even got something

    if(!$results){
        $_SESSION['error'] =  "invalid username or password ";
        header("Location: index.php");
        die();
    }

We do we negate an array? It's not an expression.

Yeah, in some way it it.
Every value - no matter what type - has it's boolean value.
For 0 and empty - it's false
For not 0 - it's true

So when we negate the array - interpreter "thinks" something like this

So, we need to get boolean value of $results - 0 for empty, 1 for not empty. Then, I negate it. So when $results' value is 0 - if statement is true.

With this checked - let's look at the password

    if(!password_verify($password_s, $results['password'])){
        $_SESSION['error'] =  "invalid username or password ";
        header("Location: index.php");
        die();
    }

I think you saw it - I just copied the previous one changing $row to $result

With this checked - we can redirect user to the secret page

$_SESSION['isLoged'] = true;
header("Location: secret.php");

Does it work? - It works

And that's it - we finished this script

Conclusion

Thanks for reading - I hope you enjoyed it.
To be fair, I thought this article will be published somewhere on Sunday/Monday but I managed to finish it on time - so you see both of those at the same time

Whole code is available on my Github - today I also included the dump from database we created a while ago

You can import it using import functionality in PHPMyAdmin

And that's it - share your feedback in the comments. Also remember to regularly check my blog so you have access to my articles earlier. And not only to programming-based ones

See you in next articles