Using PHP Data Objects

int(9180)

The PHP Data Objects library provides a unchanging substructure for database entrance and connectivity. Daniel Lewis explains how to put it into practice

This essay initial seemed in emanate 231 of .net repository – a world’s best-selling repository for web designers and developers.

Dynamic website and concentration growth seems some-more hackneyed than immobile website origination these days, and with energetic growth comes a need to store data.

A renouned database government complement used alongside a PHP denunciation is a MySQL database, with Microsoft SQL, PostgreSQL and Oracle also being sincerely common. The PHP organisation of developers essentially eased joining between PHP and a database systems regulating database-system specific functions such as:

  1. MySQL: resource mysql_query ( string $query [, resource $link_identifier ] )
  2. Microsoft SQL: mixed mssql_query ( string $query [, resource $link_identifier [,
  3. int $batch_size = 0 ]] )
  4. PostgreSQL: resource pg_query ([ resource $connection ], string $query )
  5. Oracle Database: bool oci_execute ( resource $statement [, int $mode = OCI_
  6. COMMIT_ON_SUCCESS ] )

As we can see from a definitions they have no stereotyped form, so if we had to change your database complement from Oracle to MySQL you’d have to work by your formula and change how we bond to your database. It’s also a thorn in a side of training about database connectivity in PHP: we can’t customarily send your believe from, for example, PostgreSQL to Microsoft SQL.

The PHP Data Objects (PDO) philosophy

Thankfully a database connectivity saviour does exist in PHP – and this is in a form of a 3 minute acronym PDO, that stands for PHP Data Objects. The suspicion of a PDO library is that it provides a stereotyped substructure on that we can bond to any relational database government complement (RDBMS) that can be queried regulating SQL.

The PHP Data Objects (PDO) philosophy

With a small fitness your PDO origination book should emanate a stream table, like a one above, in your database

At a time of essay this includes CUBRID, Firebird, Interbase, IBM DB2, Informix, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite, 4D – and any database that’s connectable around ODBC.

Taking a discerning unpractical instance of a changes, we would formerly have seen:

  1. <name-of-rdbms>_query($sql);

We now see a stereotyped set of functions, that looks a bit like this:

  1. $conn->query($sql);

But that’s utterly adequate speculation for now – let’s have a demeanour during this absolute library in action! There are dual graphic aspects to PDO, a initial being a tie – that is clearly a compulsory component – while a second side is a querying element.

Connecting to a database government system

Before we query any information we contingency bond to an already commissioned and set-up database government system. We’ll bond to a MySQL database, that is regulating on localhost, for a initial example:

  1. $conn = new PDO(‘mysql:host=localhost;dbname=yourdbname’, ’username’, ’password’);

Let’s review that with joining to a PostgreSQL database:

  1. $conn = new PDO(‘pgsql:host=localhost;dbname=yourdbname’, ’username’, ’password’);

The connectors are stereotyped interjection to a initial instance regulating a same function, while a second employs a stereotyped information source name (DSN) system. You can start to see how easy it is if we customarily wish to switch from one database government complement to another.

Connecting to a database government system

Drupal 7 has PDO behind a database condensation layers. The central Drupal support on these is a good approach to find out some-more about a integration

Querying and reading results

Querying information is essentially some-more engaging than formulating data, and as this is a educational on information connectivity rather than on SQL, we’ll get true to querying and revisit creation, insertion, updating and deletion later.

We’re going to assume we have a list called profile, that lists several sum about users of a suppositious web application. As an example, let’s run a discerning form fetch in SQL by PDO and thereafter simply relate out a full names. Notice how a query duty can be used as an iterator – a duty that stores a pointer, in memory, to a stream component of an array (or outcome set in this case). When this is total with a foreach loop it allows for a discerning and easy routine of accessing rows:

  1. foreach ($conn->query(“SELECT * FROM profile”) as $row) echo
  2. $row['fullname'];

Of course, this is discerning and easy, though we frequency ever wish to fetch all rows; therefore let us supplement some conditionals by non-static injection. Here we utilize a some-more strong querying method, that prepares a query and injects a variables:

  1. $query = $conn->prepare(“SELECT * FROM form WHERE username =
  2. :username LIMIT 1″);
  3. $query->bindParam(“:username”, ”knightofarcadia”);
  4. $query->execute();
  5. $profile = $query>fetch( PDO::FETCH_ASSOC );
  6. echo $profile['fullname'];

The formula above will shorten a form list hunt to customarily one form with a user name knightofarcadia. Like a initial instance it’ll simply imitation out a full name after that – though we can positively suppose building adult an whole XHTML page, that has a information upheld to them.

Querying and reading results

The PHP Data Objects library helps energy Facebook’s HipHop Engine, that can revoke CPU use on a web servers by adult to 50 per cent

We might, however, have some-more than one quarrel returned in a query, and therefore we have a capability of regulating a fetch method as an iterator. Here we see a opposite query that earnings a mixed quarrel outcome set:

  1. $query = $conn->prepare(“SELECT * FROM form WHERE hometown = :hometown”);
  2. $query->bindParam(“:hometown”, ”Wessex”);
  3. $query->execute();
  4. foreach($query->fetch(PDO::FETCH_ASSOC) as $row) {
  5.   echo $row["fullname"];
  6. }

The instance above will hunt by a form database and lapse all profiles that have a hometown set to Wessex. The fetch method is thereafter used to lapse all those results, and for a candid instance we can customarily imitation out a full name to shade – nonetheless this could be a some-more formidable XHTML page operation.

Creating

Now, nonetheless we would disciple a origination of a database structure being finished in SQL directly into a database government system, it is probable to boldly emanate database tables regulating SQL prepared by PDO:

  1. $createsql = $conn->prepare(“CREATE TABLE profiles (username VARCHAR(64), fullname VARCHAR (128), hometown VARCHAR(128)”));
  2. $conn->query($createsql);

Please do remember that a query object in this instance won’t lapse anything of value since it is a origination command. As already mentioned, it’s value perplexing to equivocate a use of boldly formulating tables in this approach in a web application, nonetheless we can suppose it being used in ‘run-once’ web-based systems (such as server web applications installers), as good as in elementary nonweb formed server scripts.

Creating

Here’s an intent indication that corresponds to a instance PHP formula used in this tutorial

Inserting

Inserting information is really critical in a energetic system, and utterly in contemporary Web 2.0 and Web 3.0 systems, that are orientated towards partnership and co-operation – how can users combine if they have no capability to store and share data? Therefore, let’s insert some information into a form list thus:

  1. $insertsql = ”INSERT INTO profiles (username, fullname, hometown) VALUES (:username, :fullname, :hometown)”;
  2. $query = $conn->prepare($insertsql);
  3. $query->bindParam(“:username”, ”knightofarcadia”);
  4. $query->bindParam(“:fullname”, ”Arthur Pendragon”);
  5. $query->bindParam(“:hometown”, ”Wessex”);
  6. $query->execute();

Like a query function in a origination of tables, this execute function won’t lapse anything of value since it’s simply an insert authority to a database. You’ll also notice that we’re regulating a ‘prepare, bond and execute’ technique in method to inject a variables into a SQL.

Updating

Updating, like inserting and deleting, is essential in a collaborative complement and PDO creates this easy. Updating is utterly matching to insertion:

  1. $query = $conn->prepare(“UPDATE profiles SET fullname = :fullname WHERE
  2. username = :username”);
  3. $query->bindParam(“:fullname”, ”Arthur Pendragoon”);
  4. $query->bindParam(“:username”, ”knightofarcadia”);
  5. $query->execute();

The above retard of formula merely replaces a full name of a user, though you’ll notice that it’s substantially matching to a insertion code. We bond a redeeming result, in this box a username, and we bond a environment result, in this instance a new full name.

Updating

A elementary relational database indication of a normalised many-to-many propinquity – roughly a kind of structure Doctrine will build from an ORM definition

Deletion

Finally let’s take a discerning demeanour during deletion, that is mostly a easier routine than inserting or updating.

  1. $query = $conn->prepare(“DELETE FROM profiles WHERE “username” =
  2. :username );
  3. $query->bindParam(“:username”, “knightofarcadia”);
  4. $query->execute();

The above SQL simply deletes a form where we compare on a username. We simply bond a username to a redeeming variable.

Switching database government system

Now, providing that a database list structure is matching and that we aren’t regulating anything non-standardised within a exclusive SQL system, we can simply change a information source name to indicate from one RDBMS – in a initial instance a Microsoft SQL Server – to another (IBM DB2, for instance). The whole formula that we’ve finished from thereafter will work – though a need of changing any SQL.

We will start with a tie fibre looking like this:

  1. $conn = new PDO(“sqlsrv:server=localhost;database=yourdbname”,
  2. “username”,
  3. “password”);
  4. $conn = new PDO(“ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=yourd
  5. bname;HOSTNAME=localhost;PORT=56789;PROTOCOL=TCPIP;”,”username”,
  6. “password”);
Switching database government system

Another of a PDO library’s pivotal users is MediaWiki, a app that powers all of a Wikipedia Foundation’s projects

Transactions

A transaction, in database terms, is where we save adult a set of queries to collection routine during a after time. PDO provides a resource for building adult exchange – though as these are rarely contingent on a database government system, PDO exchange customarily work when joining to a subset of a RDBMS that PDO supports. If we try to start a transaction on an RDBMS that doesn’t support exchange thereafter you’ll get a rather nasty PDO transaction. So let’s analyse some transaction code:

  1. try {
  2.   $conn->beginTransaction();
  3.   $insertsql = $conn->prepare(“INSERT INTO profiles (username, fullname,
  4. hometown) VALUES (‘wilfred’, ‘Wilfred Jones’, ‘Scarborough’)”);
  5.   $deletesql = $conn->prepare(“DELETE FROM profiles WHERE username =
  6. ‘username’” );
  7.   $conn->exec($insertsql);
  8.   $conn->exec($deletesql);
  9.   $conn->commit();
  10. } catch (Exception $e) {
  11.   $conn->rollBack();
  12.   // summary permitted with: $e->getMessage();
  13. }

First we start a try-catch so that we can locate any bad exceptions, including those we might get by perplexing to bond to an RDBMS that does not have transaction support. We start a transaction with $conn-beginTransaction() before stability building adult a query executions, though these won’t be entirely executed in a database until a $conn-commit() duty is run, and they’ll be finished in an fit method sequence – definition we can do several other PHP processes between a execution commands with no impact on a database.

If we find that a database doesn’t support exchange thereafter a transaction simply won’t happen; if any other difference is thrown thereafter we govern $conn-rollBack(), that will hurl behind any changes finished by a transaction. It’s value observant that when joining to a transactions-supported RDBMS, PDO will enter into an ‘auto-commit’ state, where any exec authority is itself a possess committed transaction; however, if we would like to work in a protected approach thereafter we can always use beginTransaction, and have entrance to a collection dedicate and a rollback functionality.

Problems with PDO

There are no genuine problems from a PHP viewpoint in regulating PDO. It’s intent oriented definition it is extensible and flexible, and it works with many systems in connectable fashion. The problem comes when we cruise that while a infancy of relational database government systems follow SQL standardisation (so assisting us switch from one complement to another), many systems have their possess exclusive syntax and functions, that are not common other systems.

Therefore it is essential that in method for a well-spoken transition from one complement to another we follow a SQL standards and customarily use ordinarily found functions. To reflect this there is a duty ordinarily used in queries: we can demeanour during a duty for randomisation in SQL. Here are a duty specifications for several languages:

  1. MySQL: SELECT RAND([seed]);
  2. MS SQL: SELECT RAND([seed]);
  3. PostgreSQL: SELECT RANDOM(); (to set a seed we contingency run SETSEED([seed])
  4. beforehand)
  5. Oracle DB: SELECT dbms_random.random FROM dual;
  6. SQLite: SELECT RANDOM();

So we contingency keep this in mind, and work out possibly we can possibly occupy a stereotyped SQL technique instead of a exclusive function, or use a PHP procession and inject a outcome into a SQL query (in a randomisation instance we could use a rand() duty that PHP provides).

Problems with PDO

The gedit app on Linux is one of a fastest ways to cgange code, and has syntax highlighting for many languages. It uses a Doctrine ORM system

Object relational mapping by example

Of course, we can go serve in condensation by entering into a modelling universe – no, not by exhibiting a plan on a catwalk, though around mapping Plain Old PHP Objects to database tables. There’s a problem with this beacuse PHP objects have an object-oriented model, since SQL databases have a relational one. This is where intent relational mapping (ORM) comes into play: it enables we to map objects to tables, mostly regulating a bit of sorcery and some sparkle.

You’re substantially seeking what a advantage of ORM is. Quite simply, we don’t have to understanding with any of a database connectivity or SQL querying; we simply use PHP objects and their methods directly, and a ORM complement deals with all a connectivity and a create-read-update-delete exchange in a background. There are utterly a few ORM libraries for PHP out there, PdoMap, Propel and Redbean being a few of a good ones, though a best I’ve used is Doctrine 2 – it has a advantage of being possibly serviceable on a possess or as partial of an MVC setup such as Symfony, CodeIgniter or Zend.

With Doctrine 2, and a few other ORM systems, customarily we conclude a set of objects and a kind of relations that they have with any other (such as one-to-many; many-to-one; many-to-many), with special sum about a tie between properties and a methods. Of course, there’s no need to conclude normalised integrate tables since these are irrelevant in a intent model. This human-executed routine is customarily finished regulating tradition syntax, for instance in XML or YAML, and Doctrine 2 allows for intent relational definitions within PHP doc-blocks. The following indication and formula describes a real-world example.

  1. <?php
  2. use Doctrine\Common\Collections\ArrayCollection;
  3. /** Description of Member
  4.  * @Entity
  5.  */
  6. class Member {
  7.   /**
  8.    * @Id @GeneratedValue
  9.    * @Column(type=”integer”)
  10.    * @var int
  11.    */
  12.   protected $id;
  13.   /** @Column(type=”string”)
  14.    * @var string
  15.    */
  16.   protected $firstname;
  17.   /** @Column(type=”string”)
  18.    * @var string
  19.    */
  20.   protected $surname;
  21.   /** @Column(type=”string”)
  22.    * @var string
  23.    */
  24.   protected $email;
  25.   /** Many members have a membership of many groups
  26.    * @ManyToMany(targetEntity=”Group”)
  27.    * @var Group[]
  28.    **/
  29.   protected $groups;
  30.   /**
  31.    * Constructor
  32.    */
  33.   public function __construct() {
  34.     $this->groups = new ArrayCollection();
  35.     // …
  36.   }
  37.   // — Basic getter and setter examples — //
  38.   /** Gets a (internal) ID of a member
  39.    * @return int
  40.    */
  41.   public function getId() {
  42.     return $this->id;
  43.   }
  44.   /** Gets a Firstname of a member
  45.    * @return string
  46.    */
  47.   public function getFirstname() {
  48.     return $this->firstname;
  49.   }
  50.   /** Sets a firstname of a member
  51.    * @param fibre $firstname
  52.    */
  53.   public function setFirstname($firstname) {
  54.     $this->firstname = $firstname;
  55.   }
  56.   // — More formidable getter and setter examples — //
  57.   /** Gets a groups array of a member
  58.    * @return Group[]
  59.    */
  60.   public function getGroups() {
  61.     return $this->groups;
  62.   }
  63.   /** Assigns a organisation to a member
  64.    * @param Group $group
  65.   */
  66.   public function assignToGroup(Group $group) {
  67.     $this->groups[] = $group;
  68.   }
  69.   /** Removes a member from a group
  70.    * @param Group $group
  71.    */
  72.   public function removeFromGroups(Group $group) {
  73.     $this->getGroups()->removeElement($group);
  74.   }
  75.   // …
  76. }
  77. ?>

A machine-executed routine can thereafter beget a SQL to emanate a database (this can be in a accumulation of SQL syntaxes, such as for MySQL, for SQL Server or for PostgreSQL); it can do this by transfer a SQL, or by joining with a database and executing it itself. It will emanate a many-to-many integrate tables itself.

  1. doctrine orm:schema-tool:create

or

  1. doctrine orm:schema-tool:create –dump-sql

You will find you’ve now got a PHP objects and a tables in place. Now we can understanding with a objects though wanting to know possibly how to bond with a database government system, or a structure of a relational tables.

  1. $group = new Group();
  2. // set organisation details, insist and flush (as below)
  3. $member = new Member();
  4. $member->setFirstname(“”);
  5. $member->setSurname(“”);
  6. $member->setEmail(“”);
  7. $member->assignToGroup($group);
  8. $entityManager->persist($member);
  9. $entityManager->flush();
  10. echo ”Created Member with ID “ . $member->getId() . ”\n”;

Of march there’s a bit some-more to it than a formula examples above, essentially per tie sum and bootstrapping, though we suspicion that we should give we a essence of a energy of ORM and Doctrine in particular. we should supplement that Doctrine 2, and a integrate of a other PHP ORM libraries, use PDO in method to grasp support for several opposite RDBMSes.

Object relational mapping by example

Usefully, a Doctrine complement includes endless in-built assistance whenever we need it

Conclusion

I have used PDO in blurb projects, and from my possess knowledge it severely increases a palliate of joining to a database – and traffic with a information thereafter – interjection to a object-orientation. There’s no need to use PDO customarily for a complement switching capability; a functions are there for we to use, have good potency and are ideal for contemporary programming pattern patterns.

There are many systems out there still regulating a procedural-style systemspecific functions, though we positively have seen a change in concentration to PDO. We can also see PDO in movement in a wild, since Drupal, MediaWiki, WordPress and many other renouned open source web applications now entirely or partially support a PDO complement for joining to databases. we have a prophesy in that information is no longer closely-coupled with databases, and databases are no longer closelycoupled with code, and we see some-more ubiquitous purpose systems being configured to specific applications. we consider that ORM systems, NoSQL and related information are 3 things to demeanour out for in a benefaction and in a future. The torpedo facilities of destiny web applications will, firstly, have a foundations of a connectivity of data, and secondly, by a potency of a strategy of that data.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Leave a Comment

Comments are moderated. Please no link dropping, no keywords or domains as names; do not spam, and do not advertise!


six + = 11