In here I will be explaining the use of PDO and why you must use it..?This is a beginner's look in to PDO. For a more in depth look at some of the powerful features PDO provides, such as class extension and transactions, please look at PDO: Connections and Abstraction.
Many of the you learned on how to communicate into the database using the MYSQL or maybe the MYSQLi extensions. Since the release of PHP 5.1 they put up a new function that you can use to connect and manipulate your database and for ME it's the best way they call it PHP Data Object (PDO) it provide methods for prepared statements and working from objects will make you more productive.
You can also use PDO to connect to various databases ...
Database Support
- PDO_FIREBIRD ( Firebird/Interbase 6 )
- PDO_INFORMIX ( IBM INFORMIX DYNAMIC SERVER )
- PDO_DBLIB ( FreeTDS / MICROSOFT SQL SERVER / Sybase )
- PDO_IBM ( IBM DB2 )
- PDO_MYSQL ( MySQL 3.x / 4.x / 5.x )
- PDO_ODBC ( ODBC v3 )
- PDO_OCI ( Oracle Call Interface )
- PDO_PGSQL ( PostgreSQL )
- PDO_SqLite ( SQLITE 3 and SQLITE 2 )
- PDO_4D ( 4D )
NOTE : All of this drivers are not necessarily available on your system; PDO provides function to check what are the available databases into your system.
print_r(PDO::getAvailableDrivers());
Connecting into your database
Since many of you are using MYSQL for database , my example below is for MYSQL database connection only.
<?php
//define some contstant
define( "DB_DSN", "mssql:host=localhost;dbname=databaseName" );
define( "DB_USERNAME", "databaseUsername" );
define( "DB_PASSWORD", "databasePassword" );
//connect
try {
$con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); //our new PDO Object
} catch (PDOException $e) {
echo $e->getMessage(); //catch and show the error
}
?>Brief Explanation of the code above
I declare some constant to handle our database type, and database connection string. We are going to use those constants all over the tutorial.
If you can see I use try/catch, always do wrap your pdo operations because this is a BEST PRACTICE to catch the error message and display it for debugging purposes
connection - I created a new PDO object and assigned it into the variable $con short for Connection, we will be using this variable throughout the tutorial.
To close the connection you can use
$con = null;
Assigning NULL into our connection object will close the connection.
To know more connection strings for different databases you can always go here http://www.php.net/m...pdo.drivers.php
Handling Exceptions
Now that you know on how to use try/catch to catch the Exceptions we will now Handle what message or error it will display. You can force PDO into one of three Error Modes using the following.
$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT ); $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
NOTE: Never forget to wrap your PDO operations with try/catch
SILENT
This is the default error mode. Using this method is like MYSQL and MYSQLi used to show errors. Using the other two error modes are more ideal than the default.
WARNING
This will display a default PHP warning and will allow the program to continue. It's good for debugging purposes.
EXCEPTION
This is the most secured mode where it fires an exception and hide the data that might help someone to exploit your system.
Here's an example code for using the error modes
try {
$con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORLD ); //oooopss I mistyped PASSWORD into PASSWORLD..
$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$con->prepare( "SELECT * FROM Users" );
} catch ( PDOException $e ) {
echo "I'm sorry there is a problem with your operation..";
file_put_contents( 'dbErrors.txt', $e->getMessage(), FILE_APPEND );
}If you've noticed I intentionally typed WORLD instead of WORD . The Exception will send the Error message into a file named "dbErrors.txt" and display a friendly message to the user instead of showing your full error.
INSERT AND UPDATE
In this section we will learn on how to Insert and Update database records.
Here is an example of a basic insert
$stmt = $con->prepare( "Insert Into users ( username ) Values ( 'papabear' )" ); $stmt->execute();
How easy is that?
Here's an example for update
$stmt = $con->prepare( "UPDATE users SET username = 'papabear'" ); $stmt->execute();
The examples above are called "Prepared Statements"
Prepared Statements - is a precompiled SQL statements that can be executed multiple times by sending just the data to the server. It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks.
Example of placeholders
Named Placeholders
$stmt = $con->prepare( "UPDATE users SET username = :user" );
$stmt->bindParam(":user", 'Papabear');This method of placehold is what I've always use for code readability purposes
Unnamed Placeholders
$stmt = $con->prepare( "Insert Into users ( firstname, lastname ) Values ( ?, ? )" ); $stmt->bindParam(1, 'Alleo'); $stmt->bindParam(2, 'Indong');
I used this method if I'm lazy to type named placeholders
NOTE: $stmt for STATEMENT HANDLE
placeholders are just like variables that contain values.. and those values can be found on the bindParam method.
SELECTING DATA
You can obtain the data using ->fetch(), a method of your statement handle. There are 8 modes on how do you want to FETCH your method. You can always tell PDO what fetching method you want.
- PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
- PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set
- PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method
- PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.
- PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class
- PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed
- PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0
- PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set
In order to set the fetch mode the following code is used.
$stmt->setFetchMode(PDO::FETCH_ASSOC);
here is a basic tutorial using the FETCH ASSOC MODE
$stmt = $con->prepare("SELECT firstname, lastname FROM users WHERE firstname = :name");
$stmt->bindParam(":name", 'Alleo');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();
while( $row = $stmt->fetch()) {
echo $row['lastname'] . ", " . $row['firstname'] . "\n";
}Tutorial Ended..
Thank you for reading my simple tutorial about PDO. You can pm me if you encounter some trouble
Here's a more advance tutorial about PDO by Alexander -> Advance PDO
Will this tutorial help me with that?
you can exclude your configuration file from searchengines as well as throw people away from it using the htaccess, there's a lot of security techniques that you can use, you can also encrypt this using an encrypter.
