PHP Database

Last Updated on Mar 22, 2023

Introduction

Before we start

Connect to database
INSERT
UPDATE & DELETE
SELECT
Conclusion

Key takeaways

Introduction

Today we are going to learn about working with MySQL database because MySQL is one of most popular databases with PHP.

There are 2 ways to get connected to MySQL

MySQLi
PDO

You can choose whatever you like. They are very similar. But today I will work with PDO because PDO can work with 12+ databases but MySQLi can work only with MySQL.

If you work with PDO and later you decide to change your database to something other than MySQL it’s very simple.

Before we start

PDO is a class and here we're creating a PDO object. we will talk about object oriented programming in details but for now you just need to know that in php when you want to call a method of an object or get the value of an attribute we should use ->

For example if we call the array like this $example[‘key’] then if it was an object we would get the value by doing this

$example->key

Or if want to call a method of that object we call it like this

$example->methodName()

Now let’s continue with our database

Connect to database

The very first step is to get connected to a database. I have a database called exmpledb

$pdo = new PDO('mysql:host=localhost;dbname=exampledb', "root", "");

The first argument is the database information as a string

Here we say we are using mysql the host is localhost and the database name is exampledb

The second argument is the username

The third argument is the password

For me is root user and the password is empty

INSERT

Now let’s see how we can insert, update, delete and select the data from our database

I have a table called sample with id (auto increment) and name

We can use exec method and write raw sql

This function will return the number of affected rows.

For example

$pdo = new PDO('mysql:host=localhost;dbname=exampledb', "root", "");
// INSERT
$count = $pdo->exec("INSERT INTO `sample`(`name`) VALUES ('Amir')");
echo $count . ' rows were affected';
// 1 rows were affected

You can get the last inserted row’s id like this:

$lastId = $pdo->lastInsertId();
echo $lastId;

If you want to insert multiple rows at once you can use transactions. You need to start the transaction do the things you want to do and then commit.

$pdo->beginTransaction();
$pdo->exec("INSERT INTO `sample`(`name`) VALUES ('Pratham')");
$pdo->exec("INSERT INTO `sample`(`name`) VALUES ('Simon')");
$pdo->commit();

If you don’t want to have any variable or user input inside your sql statements you can use exec but if you want to use user input then DO NOT USE exec. Sql injection is one of the most common way websites get hacked.

First of all validate your input and check everything then when you want to

add it to your sql use prepared statements

use prepare method and for every value that is external use ?.
bind values using bindValue method.
execute the sql

Here is an example

$name = 'test';
$stm = $pdo->prepare("INSERT INTO `sample`(`name`) VALUES (?)");
$stm->bindValue(1, $name); // bind the first question mark to the name variable
$stm->execute();

UPDATE & DELETE

Update and delete are exactly like insert. Again make sure to use prepared statements .

// UPDATE
$count = $pdo->exec("UPDATE `sample` SET `name`='Amir Updated' WHERE `name`='Amir'");
echo $count . ' rows were updated'; // 1 rows were affected

// SAFE UPDATE
$oldName = 'Pratham';
$newName = 'Pratham updated';
$stm = $pdo->prepare("UPDATE `sample` SET `name`=? WHERE `name`=?");
$stm->bindValue(1, $newName);
$stm->bindValue(2, $oldName);
$stm->execute();

// DELETE
$count = $pdo->exec("DELETE FROM `sample` WHERE `name`='Simon'");
echo $count . ' rows were deleted'; // 1 rows were affected

// SAFE DELETE
$deleteName = 'Simon';
$stm = $pdo->prepare("DELETE FROM `sample` WHERE `name`=?");
$stm->bindValue(1, $deleteName);
$stm->execute();

SELECT

Instead of exec you should use query Then you need to get the rows by calling the method fetchAll. fetchAll gets a mode as argument I personally prefer PDO::FETCH_ASSOC but you can use whatever you want.

$stm = $pdo->query("SELECT * FROM `sample`");
// PDO::FETCH_ASSOC     returns an associative array
// Array ( [id] => 1 [name] => Amir Updated)
// PDO::FETCH_NUM       returns an indexed array
// Array ( [0] => 1 [1] => Amir Updated )
// PDO::FETCH_BOTH      returns an array with indexed and associative keys
// Array ( [id] => 1 [0] => 1 [name] => Amir Updated [1] => Amir Updated )
// default is PDO::FETCH_BOTH
$rows = $stm->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row){
    echo 'id of ' . $row['id']. ' belongs to the name ' . $row['name'];
    echo "<br>";
}
// id of 1 belongs to the name Amir Updated
// id of 2 belongs to the name Pratham updated
// id of 4 belongs to the name test

With select you can also use prepared statements.

// safe select
$stm = $pdo->prepare("SELECT * FROM `sample` where name = ?");
$stm->bindValue(1, 'Amir Updated');
$stm->execute();
$rows = $stm->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row){
    echo 'id of ' . $row['id']. ' is Amir Updated';
    echo "<br>";
}
// id of 1 is Amir Updated

and here is everything we learnt

https://youtu.be/KsRAHM2LNwg

Conclusion

Now you know about working with database in PHP.

I recommend you to open a PHP files, create a database and add a table to it. then try to insert data to your table and read from it. update the data and delete them.

If you have any suggestions, questions, or opinions, please contact me. I’m looking forward to hearing from you!