realLifeinfo

PHP data object explained in details

Nerd, Solutions 

In Short PDO, this is the one of PHP database drivers. PDO can be used to access many RDBMS, this includes MYSQL/MARIADB, SQLITE and MS SQL server to name a few. PDO is an Object-Oriented interface, this means it exposes its functionality via method call.

Sponsored

How to Connect to the Database using PDO

To connect to the database using PDO, first we must define what's called Database Source Name. This is the string with name value pair separated by semicolon, that used by PDO to locate the location of the database and its name. Every RDBMS has its own database source name syntax. Down below are three examples of three popular databases database source name:

The port option is optional, you are required to write the port number if the server does not use the standard port. As you see in the example above, every RDBMS has its own syntax for its database source name.

Code Example:

$dsn = "mysql:host=localhost;dbname=test_db";

try{

$db_connection= new PDO($dsn, "username","password");

}catch(PDOException $e){

echo "error: {$e->getMessage()}";

}

After we have defined database source name, we need to instantiate PDO object. PDO constructor is the one that used to connect to the database. PDO constructor takes four arguments, but three are optional.

These arguments are dsn, username, password and options. The only required argument is dsn.

When PDO failed to connect to the database, the constructor will throw an exception, PDOException. So, for better error management we need to instantiate PDO constructor inside a try...catch block.

When the connection has successfully established, we can issue SQL commands into the database. We can use either exec or query methods to issue commands into the database. Both methods can be used to issue commands into the database, but there is a small difference on how they behave.

Difference between Exec and Query Methods of PDO

The difference between these two methods are: Exec method is useful when is used to issue update, delete and insert statements into the database. Because this method will return a number that represent how many rows has been affected by the issued SQL command. In other hand, the Query method is useful when is used to issue select statement. This is because the method will return the set of results returned from the issued SQL command.

Handle the returned result

When we issue SQL command to retrieve records from the database we expect a returned result that we need to process and extract information from. In order to achieve this, PDO makes this process very easy, we can accomplish this by either using one of these approaches. The first one will only work with foreach loop.

First Approach:

Sponsored

We issue query method inside the foreach loop. Code Example:

foreach( $db_connection->query($sql) as $row){

echo $row['value'];

}

Second Approach:

In this approach, we issue query method and then store the result into the variable. Loop the returned result and then extract information from the result. Code Example:

$result = $db_connection->query($sql);

while($row = $result->fetch()){

echo  $row['value'];

}

Both approaches will produce the same outcome. But, the first approach uses less code and it's easier to read.

You can read more about PDO methods and their uses in PHP official website.

PDO prepared statement

This is the template for SQL query that uses values from user input. This is the best way to process user input, because all user input will be sanitized first for SQL injection before data has been entered into the database. This is the best way to avoid SQ injection attacks on the database.

First step in prepared statement is to prepare the SQL statement. This is achieved by using PDO method, prepare. This method will prepare the statement and return it.

The second step is to attach or bind any user input to the prepared statement. This can be achieved by using one of these methods, bingParam, bindValue or by passing array of values into the execute method of prepared statement.

Difference between bindParam and bindValue

There is a slightly difference between bindParam and bindValue. The difference is bindParam can only accept variables and bindValue can accept both variables and expression, which means you can pass a valid expression to bindValue to bind it into prepared statement placeholder.

PDO prepared statement in action

Sponsored

$sql = "SELECT name, password FROM user WHERE username=:username";

In the above SQL statement :username is act as the placeholder for user input. The syntax used is known as named parameter syntax. Also, we can write this SQL statement in other syntax, like this:

$sql = "SELECT name, password FROM user WHERE username=?";

The above syntax is known as anonymous parameter syntax.

$stmt = $db_connection->prepare($sql);

To bind parameters to the prepared statement, it depends on which SQL statement syntax had been used.

For named parameter:

$stmt->bindParam(":username", $username);

Here $username is the variable that store user input, and :username is the parameter that we are going to bind user input that is stored in the variable $username.

For anonymous parameter:

Here we use the position of anonymous parameter in the SQL statement. In our example, we have one parameter, but his can work with more parameters.

$stmt->bindParam(1, $username);

The third step is to execute the statement. This is achieved by issuing the execute method of the prepared statement.

$stmt->execute();

The last step is to use the returned result. We can access the returned results directly from the prepared statement. Here is code example. 

While( $row = $stmt->fetch()){

echo $row['value'];

}

 Another way we can process the returned results is by binding the column to the variable. Here is the code example:

$sql = "SELECT song_name FROM song";

all prepared statement code goes here

$stmt->bindColumn("song_name", $song_name);

While($stmt->fetch()){

echo $song_name;

}

This will achieve the same effect as the above code. The first argument of bindColumn method must be the same name passed in the SQL statement as column name. The second argument is the name of the variable that will store that column value.

You can read more about PDO prepared statement in the PHP official manual page.

Sponsored