PHP 0 to hero pt.12 - Using PDO (SQL pt.4 )
Tags: webdev, php, beginners, tutorial
Published: 2024-02-01 15:00:00


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](https://wizarddos.github.io/blog/programming/2023/12/27/Quick_guide_for_PHP_exceptions.html) 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

```php
// 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
<?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
```php
$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](https://en.wikipedia.org/wiki/Open_Database_Connectivity) 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](https://www.php.net/manual/en/ref.pdo-mysql.connection.php)

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)
```php
$sql = "SELECT * FROM `users` WHERE `username` = ?";
```

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

Start with preparing the query

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

Then let's execute it
```php
    $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:

```php
$query->execute([$arg1, $arg2, $arg3]);
```
(that's just example - not our code element)

Of course, there is a function in PDO such as [bindParam](https://www.php.net/manual/en/pdostatement.bindparam), and we can do regular binding

In this case - I'd look like this
```php
$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](https://www.php.net/manual/en/pdo.constants.php)

### 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
```php
    $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
```php
    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
```php
    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
```php
$_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](https://github.com/wizarddos/Dev.to-scripts/tree/master/PHP%200%20to%20hero/Part%2012%20-%20SQL%20pt.%204) - 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oiudnskckycqb4h52git.png)

And that's it - share your feedback in the comments. Also remember to regularly check [my blog](https://wizarddos.github.io/blog/) so you have access to my articles earlier. And not only to programming-based ones

See you in next articles