Working with databases in php. PDO module

In the previous note, we familiarized ourselves with the deprecated mysql module. Here we will provide an example of working with a more modern one. PDO module allows you to work with various databases, such as MySQL, PostgreSQL, SQLite, ODBC, DB2, Oracle, Firebird, etc. It is faster compared to the mysql module and more secure. There is no need to call something like mysql_real_escape_string() - protection against injections is done differently (more on this later).

Let's provide the necessary minimum for working with a MySQL database, similar to the previous note.

Connect to the database. This can be done in the constructor:

$DBH = new PDO("mssql:host=$host;dbname=$dbname", $user, $pass);

Then it is possible to directly execute a query, for example, like this:

$STH = $DBH->query('CREATE TABLE `table`')

But this method is unsafe when substituting parameters. In general, it is better to use the query method only for DDL queries (modifying the database structure), and separately validate the parameters if they exist.

When using DML queries (for data manipulation), it is preferable to use so-called placeholders - parts of the query where parameters can be substituted. Placeholders can be anonymous or named:

Anonymous:

$STH = $DBH->prepare("INSERT INTO table (field1, field2, field3) values (?, ?, ?)");

Named:

$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");

In the first case, parameters are substituted in place of "?", in the second case in place of ":*"

After that, parameters can be bound:

$STH->bindParam(1, $name);
$STH->bindParam(2, $addr);
$STH->bindParam(3, $city);

(In the case of anonymous placeholders, numbers determine the placeholder).

In the case of named placeholders:

$STH->bindParam(':name', $name);

After substituting the placeholders, execute the query:

$STH->execute()

Parameters, substituted in placeholders, are automatically escaped. You can do without bind() by passing an array of parameters to execute():

$STH->execute(array('field1', 'field2', 'field3'));

That is, to execute a query, you need to take 3 steps:

Furthermore, to retrieve data as an array, you can use the fetch() method or fetchAll() to immediately get the entire result of the query.

while($res = $STH->fetch()) {
    echo $row->name;
}

That's it for now - we have familiarized ourselves with the basics of working with the PDO module.