PHP Database
Last Updated on Mar 22, 2023
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
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!
Key takeaways
- working with database
- insert data
- insert with transaction
- update and delete
- safe insert and safe update and safe delete
- select data
- safe select
