It is time for the "mysql_connect" and "mysql_query" functions to be removed from the curriculum, and PHP has started to signal this in its official documentation; We encounter a suggestion as follows on the “mysql” functions page: This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. In summary, we do not recommend using them anymore, it says switch to PDO or MySQLi.

So what are these?

When we look at the comparison here, you can see that there is not much difference between MySQLi and PDO. We can say that PDO allows the use of most database drivers, while MySQLi supports only MySQL and is a slightly more complex and comprehensive interface compared to PDO. In conclusion, both are recommended. I will try to talk about PDO here, I believe that once you understand how to use one of them, you will be able to use the other easily.

What is PDO?

PDO (PHP Data Objects) in brief; An interface that provides access to the database in a lightweight and consistent way. As you can understand from its name, it has an "Object Oriented Programming" interface and supports dozens of database drivers;

CubridFreeTDS /

Microsoft SQL Server /

SybaseFirebird/

Interbase 6IBM DB2IBM Informix Dynamic Server /

MySQL 3.x/4.x/5.x /

Oracle Call Interface /

ODBC v3 (IBM DB2, unixODBC and win32 ODBC) /

PostgreSQLSQLite 3 and SQLite 2Microsoft SQL Server /

SQL Azure

When you want to switch to a different database driver in the future, Instead of changing it permanently, you can do this with some minor modifications from where you left off with PDO. Apart from these, PDO comes from 5.1 onwards, so you will need updated versions for it to work.

Connecting to MYSQL with PDO

In general, when defining a PDO class, connection events are created using a DSN, i.e. “Data”. We indicate it with “Source Name”. We express which database driver we will connect to and our information with DSN. In the other two parameters, we enter our database username and password.

$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

For DSN statements of other drivers, see here. 

$dsn = 'mysql:host=localhost;dbname=test';
$user = 'dbuser';
$password = 'mypassword';

try {
 $db = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
 echo 'Connection failed: ' . $e->getMessage();
}

This way we catch connection errors. Managing query errors Methods to which we send queries, such as query or exec, return false if they encounter an error when they execute the query. We will use the errorInfo method to access the error messages of these unsuccessful queries. This method returns us an array containing the code and message of the error in the last query we made. In this array, which has 3 elements, the 0th and 1st element gives the error codes, and the 2nd element gives the error message.

if($users = $db->query('SELECT * FROM users WHERE'))
{
 // If the query runs successfully, we list the members
}
else
{
 echo 'An error occurred in the query.';
 $error = $db->errorInfo();
 echo 'Error message: ' . $error[2];
}

Since we did not specify any conditions after saying WHERE in the query in the example, it will give an error and the message of the relevant error will be written on the screen. Sending a query with PDO If we do not expect to get a result from the query we will make, we should use the “exec” method, and if a result is desired, we should use the “query” method. In summary; “exec” for queries such as “DELETE/UPDATE/INSERT” and “query” for queries such as “SELECT”.

try {
 $db = new PDO('mysql:host=localhost;dbname=test', $user, $password);
 $db->exec('SET NAMES `UTF-8`');
 $count = $db->exec('DELETE FROM messages WHERE old = 1');
 echo $count . 'messages deleted';
} catch (PDOException $e) {
 echo 'Connection failed: ' . $e->getMessage();
}

The exec method returns the number of rows it affects. Including variables in queries Thanks to the binding method, which is one of the most important features of PDO, we can place our variables safely and properly in the queries we prepare. To do this, we first prepare our query with the prepare method and add “?” to the places where we will give external values. We place (question mark). Afterwards, we can use the execute method to enter the query we have prepared.We are sending our future values ​​to places.// We are preparing our query.

// We prepare our query
$query = $db->prepare('INSERT INTO users (name, email) VALUES(?, ?)');

// We give the values ​​to the places we specify in the query
$query->execute(array('Musa', 'email@email.com'));

When we run this statement, the query to be run will be as follows: INSERT INTO users (name, email) VALUES('Musa', 'email@email.com') When using PDO, it is very important for the security and order of our application to include all external variables in our queries with this method. . Thanks to this method, we are free from SQL injection vulnerabilities. Listing data with PDO For this, we will use the query method I mentioned above.

foreach($db->query('SELECT * FROM users') as $row) {
 echo $row['name'] . '
';
}

To pull a row of data with PDO, we will get the first result with fetch after calling our query with query.

$row = $db->query('SELECT * FROM users WHERE id = 1')->fetch();
echo $row['name'];

Adding a new recordWe will use the exec method to add a new record. Afterwards, if we want to get the ID of the row we added, we will call the lastInsertId method. If a problem occurs while adding, the exec method will return false. Therefore, we can first check whether it has been added or not and then print the ID on the screen.

if($db->exec('INSERT INTO users (name) VALUES ("Musa")'))
{
 $id = $db->lastInsertId();
 echo 'ID of the newly added member: ' . $id;
}
else
{
 echo 'An error occurred while adding a new record.';
}

Ability to undo queries

Another important feature of PDO is the ability to undo or execute queries, called transactions, whenever desired. Before using the query expressions above, we need to point out that we have started writing queries that we can rollback by calling the beginTransaction method. After running the BeginTransaction method, all the insertion, editing and deletion queries we write will continue to work as we normally use.

If we want to roll back all the queries in this range while the queries are continuing, we call the rollBack method. When we run this method, all the add, edit and delete queries we wrote, from beginTransaction to rollBack method, are rolled back. An important issue you should know when using these expressions; Queries that completely delete tables or add new tables, such as DROP TABLE or CREATE TABLE, cannot be undone. However, you can undo any query you deleted with the DELETE query or edited with UPDATE. // We state that we have started the operations.

$db->beginTransaction();

$db->exec('INSERT INTO users (name) VALUES ("Ahmet")');
$db->exec('UPDATE users SET name = "Ali"');
$db->exec('DELETE FROM users WHERE name = "Hasan"');

// We took back our queries
$db->rollBack();

If we really want to execute the queries when they are successful instead of rolling back, we call the commit method.