Simply Cutting Edge
Welcome Guest [Log In] [Register]
Welcome to zetaNetwork. We hope you enjoy your visit.

You're currently viewing our forum as a guest. This means you are limited to certain areas of the board and there are some features you can't use. If you join our community, you'll be able to access member-only sections, and use many member-only features such as viewing zetaNetwork exclusive tutorials and articles, and access to our code snippets section. Registration is simple, fast, and completely free.


Click Here To Register


If you're already a member please log in to your account to access all of our features:

Username:   Password:
Add Reply
OOP For Noobs: Part 3
Topic Started: Mar 11 2013, 09:53 PM (10,778 Views)
Pro
Member Avatar
Underground Coding

Introduction to PHP5 PDO
Time: 20 Minutes
Difficulty: Moderate - Hard



Previously...
OOP For Noobs Part 1
OOP For Noobs Part 2



In the previous lesson you learned about interfaces and abstract classes. In today's lesson you will learn about a real world example of OOP in PHP. PDO, PHP Data Objects, is an extension of PHP introduced in PHP5. PDO was introduced as an attempt to make writing code easier and make accessing MySQL databases more consistent.

Now that we've got the introduction out of the way let's jump right in.

Code: PHP
 
<?php
$dbhost = 'somehost';
$port = 'someport';
$dbuser = 'someuser';
$dbpass = 'somepass';
$dbname = 'somedb';

$base = 'mysql:host=' . $dbhost . ';port=' . $someport . ';dbname=' . $dbname;
$conn = new PDO($base, $dbuser, $dbpass);

$search =$_GET['somequery'];
$query = $conn->prepare('SELECT * FROM sometable WHERE str = ? ORDER BY id ASC');
$query->execute(array($search));

for($n = 0, $row = $query->fetchObject(); $row = $query->fetchObject(); $n++){
echo $n . '. ';
echo $row->result;
echo '<br />';
}
?>

Right off the bat you can see some new things. It's not as scary as it seems though and I'm going to be showing you line by line what everything means.

The first thing you see in the above snippet is a couple of variable declarations. The usual things you need whenever you access a database, a host, port, username, password, and database name. This should not be anything new to you. If it is I would suggest you read Flavius' "PHP Basics" tutorial.

Next comes the following block of code.

Code: PHP
 
$base = 'mysql:host=' . $dbhost . ';port=' . $someport . ';dbname=' . $dbname;
$conn = new PDO($base, $dbuser, $dbpass);
The first line consists of a basic string. Looks simple right? Not really. That string is the DSN, Data Source Name. The DSN is a string consisting of almost everything PDO needs to connect to your database. It starts with the name of your PDO driver (usually mysql) followed by a colon and then your host, port, and database name. If the DSN doesn't work check out your PDO driver documentation for driver specific syntax as the syntax for your driver may be different from the one I use.

Once you're done constructing your DSN it's time for the moment of truth, creating your first ever PDO object. The PDO object can take 4 parameters but only the DSN is required. In my example I pass the DSN, database username, and database password.

Code: PHP
 
PDO::__construct(string dsn, string username, string password, array driver_options)
Now that you've learned how to connect to your database using PDO it's time to start using your connection. Most PDO operations consist of 2 steps: Prepare & Execute
Code: PHP
 
$search = $_GET['somequery'];
$query = $conn->prepare('SELECT * FROM sometable WHERE str = ? ORDER BY id ASC');
$query->execute(array($search));
We start off with a basic $_GET statement. After that is where be begin preparing our SQL statement for use.



Preparing SQL Statements
Code: PHP
 
$query = $conn->prepare('SELECT * FROM sometable WHERE str = ? ORDER BY id ASC');
So the first thing you might take notice of is the prepare method in PDO. Most of the time the prepare method will only need to take your SQL statement however it can also take an array containing your driver options of needed.
Code: PHP
 
PDO::prepare(string sql_statement, array driver_options)
Now looking at our SQL statement you may be wondering what the ? means. The ? is an unnamed placeholder for our SQL statement. Now how do you get to those unnamed placeholders? Well there are two ways to do it. The second way I will show you later on in this tutorial so for now I'll just show you the first, and in my opinion the easiest, way to get to your placeholders.

The first way actually consists of two more ways so I guess you could say there are actually three ways to get to your placeholders. The first method is bindValue.
Code: PHP
 
PDOStatement::bindValue(mixed placeholder, mixed value, int data_type = PDO::PARAM_STR
The first 2 arguments are required while the last is optional. The bindValue sets the value for our placeholder to the specified value. For example:
Code: PHP
 
$query = $conn->prepare('SELECT * FROM table WHERE val = ? AND another = ? ORDER BY id ASC');
$query->bindValue(1, 'hello');
$query->bindValue(2, 'world');
As you can see in this example I have two placeholders. You can access your unnamed placeholders by simply providing their index in your SQL statement (keep in mind that indices start at 1 for placeholders).

Now let's learn about the second easiest way to access placeholders. PDO also has a bindParam method which binds a variable to your specified placeholder. Keep in mind that you are binding a variable instead of a value so if your variable changes your statement changes.
Code: PHP
 
PDOStatement::bindParam(mixed placeholder, mixed value, int data_type = PDO::PARAM_STR, int length, array driver_options)
bindParam is pretty much the same as bindValue besides the difference stated above. The other difference is that bindParam takes a length argument which is basically the length of the data type being used.



Named Placeholders
In the previous section of this tutorial I talked about unnamed placeholders. In this section I will be talking about named placeholders. Named placeholders are exactly what they sound like. A placeholder that's named. Here's an example to show you what I mean.
Code: PHP
 
$query = $conn->prepare('SELECT * FROM table WHERE val = :value AND another = :another ORDER BY id ASC');
$query->bindValue(':value', 'hello');
$query->bindValue(':another', 'world');
Simple enough right?



Executing SQL Statements
Now that we've gone over how to prepare statements it's time to put our statements to use.
Code: PHP
 
$query = $conn->prepare('SELECT * FROM table WHERE val = :value AND another = :another ORDER BY id ASC');
$query->bindValue(':value', 'hello');
$query->bindValue(':another', 'world');

$query->execute();

Nothing new here right? Well that would be the case if that last line wasn't there. The execute method does exactly what it says. It executes the prepared SQL statement.
Code: PHP
 
PDOStatement::execute(array placeholders)
The placeholders parameter is optional. Now remember when I said that there was a second way to set your placeholders? Well that's what the argument in the execute method is for.
Code: PHP
 
$query = $conn->prepare('SELECT * FROM table WHERE val = :value AND another = :another ORDER BY id ASC');
$placeholders = array('value' => 'hello', 'another' => 'world');
$query->execute(placeholders);
As you can see we are passing in each placeholder as an index of our array. You can also use arrays to pass values for placeholders using unnamed placeholders.
Code: PHP
 
$query = $conn->prepare('SELECT * FROM table WHERE val = ? AND another = ?ORDER BY id ASC');
$placeholders = array('hello', 'world');
$query->execute(placeholders);



Getting To Our Selected Data
There are multiple ways to get to your selected data. I personally prefer using the fetchObject method but it's mostly up to your preference and the situation you're in.

The fetchObject method returns the next row as an object where each value in the row is an instance variable of the returned object. For example if I have a row with the columns one, two, and three. The method fetchObject would return an object with the instance variables one, two, and three.

PDO also has a method called fetch which fetches the next row. PDO has multiple fetch styles which is why I said there are multiple ways to access your data.

  • PDO::FETCH_ASSOC - Returns an array indexed by column name.
  • PDO::FETCH_BOTH - Returns an array indexed by column name and the column number.
  • PDO::FETCH_NUM - Returns an array indexed by column number.
  • PDO::FETCH_OBJ - Returns an object with instance variables that correspond to the columns in the row (Does the same thing as fetchObject).
To set your fetch style simply use this statement.
Code: PHP
 
$query->setFetchMode(PDO::FETCH_OBJ);
After that just use a simple loop to go through all of your rows.
Code: PHP
 
$query = $conn->prepare('SELECT * FROM table WHERE name = ? ORDER BY id ASC');
$query->execute(array('Bob'));
$query->setFetchMode(PDO::FETCH_OBJ);

while($row = $query->fetch()){
echo $row->id . ' ' . $row->name;
}
Now remember when I said that most operations will consist of two steps? Well some operations only consist of one and as such can be done using the query and exec methods in PDO. Both methods take one parameter which is the statement to be executed. The query method returns the result as a PDOStatement object while the exec method returns the number of affected rows. Operations that only consist of one step are things such as DELETE and operations that do not require the use of placeholders such as "SELECT * FROM table WHERE name = 'Bob' ORDER BY id ASC".



To Sum It All Up
PDO is definitely something you should start using. It makes it easier for developers to access databases which is a huge advantage for many people. Remember that PDO is not just limited to selection. PDO can be used for insertion, deletion, updated rows, and many other things.
Pro | zetaNetwork Instructor & Admin
I Coded Most Of The Smexy Stuff You See :P
PM Me Any Questions
Need live support? Click here.
Offline Profile Quote ^
 
1 user reading this topic (1 Guest and 0 Anonymous)
« Previous Topic · Tutorials · Next Topic »
Add Reply