PHP Database Access: Employ PHP Database Access Correctly

This PHP Database tutorial is very simple and easy to comprehend distinct component of PHP Database and PHP access Database. This PHP Access Database post is quite obvious as it falls under PHP Database category. This PHP database tutorial will make you learn correctly employment of PHP Database Access.

Database accessibility is an earnest part of web-based applications as well as creating web-pages. However, the PHP developers has transformed this language more efficient especially in this area. Though, it offers several commands for database operability, the PHP still goes on expanding its database support. However, if you are still employing old MySQL application program interface towards connecting to your database, then you must keep on reading!

What?

Now, at this point, it might be probable that one question may strike your mind that “What is PDO?” Let me explain this. Actually, it is considered as one amongst PHP’s three available Application Program Interface which are employed to connect with MySQL database. However, many people are not acquainted about this. These three APIs are as follows:

  • MySQL
  • MySQLi – MySQL Improved
  • PDO – PHP Data Objects

The traditional MySQL API is very efficient that ensure your job to be accomplished. However, it become famous as it transform the pis very easy and affable to fetch the identical records from a database. For example lets consider this.

The above provided snipped is quite simple, but it has some noticeable downsides:

  • Deprecated: As it is being in use at wide length, hence it hasn’t been deprecated officially. It might be proved as quite significant in context of well practice and education.
  • Escaping: The escaping user input process is referred as the left of developer. However, most of them don’t comprehend or know about sanitizing the data.
  • Flexibility: The API is not extensile, thus the code which sits above has been tailor-made in context of working with a MySQL database.

Then it might be confounded when you supposed to switch.

(PHP Data Object) PDO offer a robust API which is not influenced by the driver you have employed; it’s database is doubtful. Moreover, it enables you implement prepared statements as well eradicate all worries of injecting SQL virtually.

How?

When I encountered with PDO API for the first time, it seems to be slightly intimidating not because it was supposed to be confounded but due to the old MySQL API was very easy to employ.

Now you don’t have to worry any more, simply you are supposed to follow these simple steps to be up and sporting without investing additional time.

Connect

You would be already aware with the legacy method of connecting with MySQL database:

Now, in account of PDO, we would attempt to build a new instance of the class and define the database name, driver, usernmae and password.

Errors

But, what in the case when an error exist there and denied to connect with the database. Now, you are supposed to wrap all things within a try/catch block:

Next, one thing is noticeable that by default, the PDO’s default error mode is PDO::ERRMODE_SILENT. Left this setting unchanged, after accomplishing a query you are required to retrieve the errors manually.

However, in the midst of development, it would be a perfect choice to update the setting to PDO::ERRMODE_EXCEPTION, that will result in firing the exceptions as they appear. Thus, any undesignated exceptions will halt the script. Check here some available options being presented for your reference:

PDO::ERRMODE_SILENT
PDO::ERRMODE_WARNING
PDO::ERRMODE_EXCEPTION

Fetch

Here, we have established a connection with database; so let’s retrieve some data from it. However, we can accomplish this task in two core ways either by query or execute. Let’s proceed further by reviewing both.

Query

Eventually it works well, do mark it that still we are escaping the user’s data manually in account of PDO:: quote method. Lets consider this method, almost PDO is equivalent to employing mysql_real_escape_string; both will escape and quote the string you already provide. At the time, when you are going to congregate user-supplied data to a SQL query. You are hardly required to employ prepared statements instead. It says, in the case when your SQL queries are not rely upon form data, the choice of query method will be helpful as well as the procedural of looping via results become very simple and affable foreach statement.

Prepared Statements

In the above example, we are employing prepare method in order to create the query prior to attachment of user’s data. In account of this technique, almost the insertion of SQL is seems to be quiet impossible because the data never get incorporated in the SQL query itself. So, pay attention that, in order to define placeholders we have employed named parameters (:id).

Next, we will attempt to execute the query in the midst of passing an array, which incorporate those data which are to be bounded with those placeholders.

Alternatively, the employment of bindParam method is perfectly acceptable towards this approach, just like this:

Specifying the Ouput

Just after employing the execute method, several distinct method are available to congregate the data: by default an array, an object and miscellaneous. In the above example, the response employed by default is PDO::FETCH_ASSOC. However, if it is necessary, it can be overridden easily.

Now, we have assigned that we wish to have more object-oriented fashion result set. It also include various available choices but is not limited.

  • PDO::FETCH_ASSOC: Returns an array.
  • PDO::FETCH_BOTH: It returns an array which is indexed by both column-name and 0-indexed.
  • PDO::FETCH_BOUND: This provide TRUE and assigned values of columns in the set of result of PHP variables they were bound with.
  • PDO::FETCH_CLASS: It delivers a new instance of the designated class.
  • PDO::FETCH_OBJ: This provide an anonymous object incorporating the names of property concerned with columns.

The above snippet meet one hindrances where we can’t provide any feedback in the case when not any result is returning. So, we are supposed to fix that.

Here, our code fragment should appear like this:

Multiple Executions

When PDO extension executes the identical SQL query for more than one or two times, it becomes specifically powerful but with distinct parameters.

However, once the query created, it is capable to execute multiple times with distinct parameters. The above snippet will include two rows into database: one incorporating the name “Keith” and another with “Steven”

CRUD

Now, you have put some basic process in place, you are required to review the several CRUD tasks immediately. As you would designate, the required code for each is identical.

Create (Insert)

Update

Delete

Object Mapping

It enables us to map the results of query to a class instance or object, thus referred as one amongst neatest aspect of PDO (mysqli). Consider this example:

Jason Larue

As a qualified PHP developer, Jason Larue is working with PHPDevelopmentServices to replenish the clients with excellent services at competitive rates. He has years of experience of working as a PHP web developer that truly reflects from the solutions that are delivered to the clients in the form of related projects. His experience has enabled him to emerge as an author who contributes in spreading the information regarding PHP web development through well-written blog posts.