M. Niyazi Alpay
M. Niyazi Alpay
M. Niyazi Alpay

I've been interested in computer systems since a very young age, and I've been programming since 2005. I have knowledge in PHP, MySQL, Python, MongoDB, and Linux.

 

about.me/Cryptograph

  • admin@niyazi.org
PHP Data Object - PDO

I had explained how to connect to a database and execute queries in PHP.
Classic database connection is done with the mysql_connect() command, and query execution is done with mysql_query(), but the PDO structure is a bit different. PDO is a database query class that came after PHP 5.2, which means it is an object-oriented structure.
Its most beautiful feature is its support for multiple databases. It allows connecting to different databases such as Oracle, SQL Server, MySQL. It is also a class with SQL injection protection.

 
$db = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');
 

We created the PDO class as connected to the db variable, and we will use methods attached to this class for our queries.

The query() method is generally used in all queries.
The exec() method is used for sending values to the database, such as data entry, update, deletion.
The prepare() method is used to execute all queries like query(), but it also requires an execute() method to be used to execute the prepared query.
The fetch() method is used to get a single result. It is used after the query is executed with query or after the execute method is called with prepare.

To get the count of data from the database, we use fetch(PDO::FETCH_NUM).

For example, let's execute a query, I will use the query to do this.

 
$query = $db->query('select * from blog where id=1')->fetch();
 

We wrote a query to fetch the topic with id value of 1 from the database. We could have done it like this as well:

 
$query = $db->query('select * from blog where id=1'); 
$query->fetch();
 

Both do the same thing.

echo $query['blog_title'];


To print the total number of all topics:

 
$topicCount = $db->query('select count(*) from blog')->fetch(PDO::FETCH_NUM);
echo $topicCount[0];
 

Since $topicCount is an array variable, we printed it as $topicCount[0].


If we do it with prepare(), our query will be like this:

 
$query = $db->prepare('select * from blog where id=1');
$query->execute();
$query->fetch();
echo $query['blog_title'];
 


If it is to be taken from the outside, it is better to provide the id information with a parameter, because we can control the property of that parameter being an integer or a string. We use the bindValue() method for the parameter.

 
$query = $db->prepare('select * from blog where id=:id');
$query->bindValue(':id', $id, PDO::PARAM_INT);
$query->execute();
$query->fetch();
echo $query['blog_title'];
  

When writing our query, I used :id, I passed the value of this with the bindValue() method.
We specified that the coming value would be an int type with PDO::PARAM_INT.

If we need to check that the coming value is a string, we should write PDO::PARAM_STR instead of INT.

So far, we always fetched information from the database, with the same queries we can also insert, update, delete.

For operations like delete, update, we can use exec, also if we are going to set the character set of the database, we should use exec again.

 
$query = $db->exec('delete from blog where id=1');
 

We deleted the topic with blog id value of 1. However, it would be better to do such operations with prepare.

Or if we are going to set the character set

 
$db->exec("set names 'utf8'");

we should use this query.

You may also want to read these

There are none comment

Leave a comment

Your email address will not be published. Required fields are marked *