Learning database connections draws to an end

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

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 it’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 exception manually 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

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 declared 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 difference is DB connection - we can copy the rest

Copy index.php, secret.php and this part 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 that message displayed. - that’s why I’ve appended $e to Server error 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", "");

Instead of 4, it only takes 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 (Speaking of technicalities, 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’ll use prepared statements again

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

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

To execute, we pass as it’s parameter $username_s - so we do bind that parameters, but 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 - It’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 are we negating array? It’s not an expression.

Yeah, in some way it is. Every value - no matter what type - has it’s boolean value. For 0 and empty - it’s false For non-0 values - it’s true

So when we negate the array - interpreter “thinks”

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 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

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

Import in nav

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

See you in next articles