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?
- 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
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
-
mysql:
- indicated used DBMS -
host=localhost;
- Where is database located -
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
- 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 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
Top comments (0)