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?
- What is PDO
- Why should you use it
- 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
mysql:
- indicated used DBMShost=localhost;
- Where is database locateddbname=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
- Check if we got something returned by query
- 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 namesPDO::FETCH_OBJ
- and object with variables inside, named correspondingly to column namesPDO::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
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