Really Simple Syndication and PHP Data Objects - Web Programming for Business - PHP Object-Oriented Programming with Oracle

Web Programming for Business - PHP Object-Oriented Programming with Oracle

8 Really Simple Syndication and PHP Data Objects

Overview

Two topics are covered in this chapter – Really Simple Syndication (RSS) and PHP Data Objects (PDO). The topics are grouped together because, although very different, they offer advanced capabilities for working with the Oracle database.

RSS provides an easy way to share and view content on the Internet and is written in XML. It was designed to show selected data. RSS is often called a news feed or RSS feed. People can check a website at any time to see updates because an RSS feed is linked to a database.

The PDO extension defines a lightweight, consistent interface for accessing databases in PHP. PDO provides a data-access abstraction layer, which means that the same functions are used to issue queries and fetch data regardless of the database software.

Learning Objectives

After completing the chapter, you will gain skills in two areas. First, you will create an RSS feed. Second, you will use the PDO extension to communicate with the Oracle database. Skills are enhanced through explanation and code examples. The following objectives summarize the skills the chapter will help you develop:

1. Learn the definition and characteristics of RSS.

2. Learn how to create an RSS feed application.

3. Learn the definition and characteristics of PDO.

4. Learn how to display data using PDO.

5. Learn two ways to prepare a SELECT statement using PDO.

6. Learn two ways to prepare an INSERT statement using PDO.

7. Learn two ways to prepare an UPDATE statement using PDO.

8. Learn two ways to prepare a DELETE statement using PDO.

9. Learn how to use PDO methods with transactions.

Definition of Really Simple Syndication

Really Simple Syndication (RSS) is a dialect of XML and provides a format for web content syndication. I use RSS 2.0 format because it is newer and eliminates the need for namespaces.

Characteristics of Really Simple Syndication

An RSS feed must be a valid XML document. The root element must be ‘<rss>’ and contain the attribute ‘version="2.0"’. The root element must have a single child element called ‘<channel>’.

The ‘<channel>’ element has three required elements that contain information about the feed – ‘<title>’, ‘<link>’, and ‘<description>’. Element ‘<title>’ is the name of the channel (feed). Element ‘<link>’ is the URL of the website corresponding to the channel. Element ‘<description>’ describes the channel. Sixteen optional elements are available if desired.

A channel may contain any number of ‘<item>’ elements. These elements represent the data pulled from an Oracle database. The ‘<item>’ element must contain either a ‘<title>’ or ‘<description>’ element, but can also contain seven optional elements.

For Google Chrome users, install the extension to auto-detect RSS feeds available (at the time of writing) at the following URL:

https://chrome.google.com/webstore/detail/nlbjncdgjeocebhnmkbbbdekmmmcbfjd

Really Simple Syndication Feed Application

I begin by building an RSS application with three classes – ‘dbAggregation’, ‘dbIterator’, and ‘rss_feed’. The ‘dbAggregation’ and ‘dbIterator’ classes were introduced and explained in Chapter 6. The application creates an RSS feed using information from an Oracle database table to populate the ‘<item>’ elements.

The ‘dbAggregation’ class sets the username, password, and host server. It also connects to Oracle, gets SQL from the calling environment, instantiates a ‘dbIterator’ instance, and returns the result set based on the SQL.

The ‘dbIterator’ class implements an Iterator interface, accepts the connection to Oracle from ‘dbAggregation’, parses, and executes the SQL. The class also contains the Iterator interface logic.

The ‘rss_feed’ class builds an RSS feed. The class sets the result set, choice to either display or save the feed, and file name if the feed is to be saved. In addition, the class contains six methods. Four methods are simple getters for channel information. The ‘write()’ method contains logic for building an RSS feed. It relies on the ‘XMLWriter’ extension to generate XML for the RSS feed. The ‘flush()’ method contains logic for displaying or saving the feed.

Before introducing the PHP scripts for building the feed, I create a table in Oracle to hold feed information. I use ‘Oracle SQL Developer’ software to create the table. Be sure to remove the line numbers before running any SQL!

1 CREATE TABLE blog

2 (

3 article_id CHAR(3),

4 title VARCHAR2(25),

5 article VARCHAR2(30),

6 created DATE,

7 url VARCHAR2(40)

8 );

The ‘blog’ table includes five fields (lines 3–7). Fields ‘title’, ‘article’, and ‘url’ are variable-length strings. Field ‘article_id’ is a fixed-length string. Field ‘created’ is a date type.

Populate the ‘blog’ table. Each record spans two lines. For instance the first record (lines 1 and 2) place values into each of the fields. Line 9 saves the data to Oracle. Again, be sure to remove the line numbers before running the SQL.

1 INSERT INTO blog VALUES

2 ('001','Get er Done','Just Do It!', '12-JAN-10','http://www.usu.edu');

3 INSERT INTO blog VALUES

4 ('002','Where Yall At?','We Be Here!', '03-FEB-10','http://www.amazon.com');

5 INSERT INTO blog VALUES

6 ('003','Yoo Hoo','Owwie, Owwie!', '28-FEB-10','http://www.audiogon.com');

7 INSERT INTO blog VALUES

8 ('004','Home Run', 'Fly Out', '11-SEP-11','http://www.w3schools.com');

9 COMMIT;

PHP file ‘dbAggregation.php’ contains the ‘dbAggregation’ class. Add your username, password, and host server to the ‘setParms()’ method. These fields are left blank because users should each have their own connection information.

 1 <?php

 2 // File dbAggregation.php

 3 class dbAggregation

 4 {

 5 private $_schema;

 6 private $_password;

 7 private $_host;

 8 protected $_connection;

 9 public function __construct()

10 {

11 $this->setParms();

12 $this->connDB();

13 }

14 public function setParms()

15 {

16 $this->_schema = '';

17 $this->_password = '';

18 $this->_host = '';

19 }

20 public function connDB()

21 {

22 $this->_connection = oci_connect($this->_schema,

23 $this->_password, $this->_host);

24 }

25 public function getResultSet($sql)

26 {

27 $results = new dbIterator($sql, $this->_connection);

28 return $results;

29 }

30 }

31 ?>

The constructor (lines 9–13) automatically runs the ‘setParms()’ and ‘connDB()’ methods. Method ‘setParms()’ (lines 14–19) sets the appropriate connection information. Method ‘connDB()’ (lines 20–24) connect to Oracle. Method ‘getResultSet()’ (lines 25–29) invokes ‘dbIterator’ and returns the results generated from this class. For a more detailed explanation, review Chapter 6.

PHP file ‘dbIterator.php’ contains the ‘dbIterator’ class.

 1 <?php

 2 // File dbIterator.php

 3 require_once 'dbAggregation.php';

 4 class dbIterator implements Iterator

 5 {

 6 public $sql;

 7 public $connection;

 8 protected $_result = array();

 9 protected $_valid;

10 private $_stmt;

11 public function __construct($sql, $connection)

12 {

13 $this->sql = $sql;

14 $this->connection = $connection;

15 if(!$this->_stmt = oci_parse($connection, $sql))

16 { echo "failed to parse"; };

17 if(!oci_execute($this->_stmt))

18 {echo "failed to execute"; }

19 }

20 public function next()

21 {

22 $this->_result = oci_fetch_assoc($this->_stmt);

23 if(!$this->_result)

24 { $this->_valid = false; }

25 else

26 { $this->_valid = true; }

27 }

28 public function current()

29 {

30 return $this->_result;

31 }

32 public function key()

33 { }

34 public function valid()

35 {

36 return $this->_valid;

37 }

38 public function rewind()

39 {

40 if(!($this->_result))

41 {

42 oci_free_statement($this->_stmt);

43 if(!$this->_stmt = oci_parse($this->connection,

44 $this->sql))

45 { echo "failed to parse"; };

46 if(!oci_execute($this->_stmt))

47 {echo "failed to execute"; }

48 }

49 $this->_result = oci_fetch_assoc($this->_stmt);

50 if(!$this->_result)

51 { $this->_valid = false; }

52 else

53 { $this->_valid = true; }

54 }

55 }

56 ?>

The constructor (lines 11–19) parses the query. The remaining code implements the Iterator interface. The ‘next()’ method (lines 20–27) sets each row of data with API ‘oci_fetch_assoc’ as long as there are data left to be fetched. The ‘current()’ method (lines 28–31) returns the current row of data. The ‘key()’ method does nothing in Oracle. The ‘valid()’ method (lines 34–37) checks if there are any data left in the result set. Finally, the ‘rewind()’ method (lines 38–54) sets the pointer to the first record of the result set. Review Chapter 6 for a detailed explanation.

PHP file ‘rss_feed.php’ contains the ‘rss_feed’ class. This class contains quite a bit of code, but is relatively straightforward except for the ‘write()’ method.

 1 <?php

 2 // File rss_feed.php

 3 class rss_feed

 4 {

 5 private $_result;

 6 private $_rss;

 7 private $_choice;

 8 private $_rssfile;

 9 protected $_feedTitle;

10 protected $_feedLink;

11 protected $_feedDescription;

12 protected $_lastBuildDate;

13 public function __construct($result, $choice, $rssfile)

14 {

15 $this->_result = $result;

16 $this->_choice = $choice;

17 $this->_rssfile = $rssfile;

18 }

19 public function setFeedTitle($title)

20 {

21 $this->_feedTitle = $title;

22 }

23 public function setFeedLink($link)

24 {

25 $this->_feedLink = $link;

26 }

27 public function setFeedDescription($description)

28 {

29 $this->_feedDescription = $description;

30 }

31 public function setLastBuildDate($lastBuildDate)

32 {

33 $this->_lastBuildDate = $lastBuildDate;

34 }

35 public function write()

36 {

37 $this->_rss = new XMLWriter();

38 if($this->_choice == "see")

39 { $this->_rss->openMemory(); }

40 else

41 {

42 $this->_rss->openUri($this->_rssfile);

43 $this->_rss->setIndent(true);

44 }

45 $this->_rss->startDocument();

46 $this->_rss->startElement('rss');

47 $this->_rss->writeAttribute('version', '2.0');

48 $this->_rss->writeAttribute('xmlns:atom',

49 'http://www.w3.org/2005/Atom');

50 $this->_rss->startElement('channel');

51 $this->_rss->writeElement('title', $this->_feedTitle);

52 $this->_rss->writeElement('link', $this->_feedLink);

53 $this->_rss->writeElement('description',

54 $this->_feedDescription);

55 $this->_rss->writeElement('lastBuildDate',

56 $this->_lastBuildDate);

57 $this->_rss->writeElement('docs',

58 'http://www.rssboard.org/rss-specification');

59 foreach($this->_result as $row)

60 {

61 $this->_rss->startElement('item');

62 foreach($row as $field => $value)

63 {

64 $field = strtolower($field);

65 $this->_rss->writeElement($field, $value);

66 }

67 $this->_rss->endElement();

68 }

69 $this->_rss->endElement();

70 $this->_rss->endElement();

71 $this->_rss->endDocument();

72 }

73 public function flush()

74 {

75 return $this->_rss->flush();

76 }

77 }

78 ?>

The ‘write()’ method (lines 35–72) begins by creating an instance of ‘XMLWriter’ (line 37). If the RSS feed is to be displayed (line 38), the ‘openMemory()’ method (line 39) is invoked, otherwise the ‘openURI()’ and ‘setIndent()’ methods (lines 42 and 43) are invoked to appropriately set up the XML for saving. The ‘startDocument()’ method (line 45) is next, followed by the ‘startElement()’ and ‘writeAttribute()’ methods (lines 46–49), which build the front end of the RSS feed. The ‘channel’ is started in line 50. Next, the ‘title’, ‘link’, ‘description’, ‘lastBuildDate’, and ‘docs’ are written (lines 51–58).

In the next part of the method, items are created from the result set built from the database table. Specifically, an outer ‘foreach’ loop (lines 59–68) creates each ‘item’ (line 61) and uses an inner ‘foreach’ loop (lines 62–66) to add values to each ‘item’ element. The outer loop closes each item element in (line 67).

The method ends by closing the feed (lines 69–71). The ‘flush()’ method (lines 73–76) returns the RSS feed.

To test the application, save ‘dbAggregation.php’, ‘dbIterator.php’, and ‘rss_feed.php’. Next, decide if you want to display the RSS feed in a browser or save to a file.

PHP file ‘rss_flush.php’ displays the RSS feed.

 1 <?php

 2 // File rss_flush.php

 3 require_once 'dbAggregation.php';

 4 require_once 'dbIterator.php';

 5 require_once 'rss_feed.php';

 6 $tbl = "blog";

 7 $query = "SELECT title, article description, ";

 8 $query .= "url link FROM $tbl";

 9 $conn = new dbAggregation();

10 $result = $conn->getResultSet($query);

11 $rss = new rss_feed($result,"see",'');

12 $rss->setFeedTitle('PHP Object-Oriented Programming with Oracle');

13 $rss->setFeedLink('http://dnet.brigham.usu.edu/');

14 $rss->setFeedDescription('Get the lowdown on OOP and PHP');

15 $rss->setLastBuildDate('Sun, 14 Sep 2012 13:24:38-0700');

16 $rss->write();

17 $see = $rss->flush();

18 echo $see;

19 ?>

The SQL query selects ‘title’, ‘article’, and ‘url’ from the ‘blog’ table, and renames the columns to be consistent with RSS tag names (lines 6–8). A new instance of ‘dbAggregation’ is created (line 9) and the ‘getResultSet()’ method is invoked to return the result set to ‘$result’ (line 10). Next, a new instance of ‘rss_feed()’ is created (line 11) with arguments ‘$result’, ‘see’, and ‘’. String ‘see’ tells the program to display the feed. Since no file is saved, nothing is sent as the third parameter. The next four lines (lines 12–15) are just simple setters. The ‘write()’ method is called to build the feed (line 16) and the ‘flush()’ method is called (line 17) to send the result.

Load ‘rss_flush.php’ in a browser. Figure 8.1 shows the results.

Figure 8.1

Figure 8.1 Display Showing RSS Feed

Before attempting to save the file, check permissions. If needed, use the following Linux commands. In this case, ‘xml_docs’ is the folder where I have the files for the application saved and where I want to save the RSS feed. Of course, your directory may be named what you wish.

chmod 777 xml_docs

PHP file ‘rss_save.php’ saves the RSS feed to a file.

 1 <?php

 2 // File rss_save.php

 3 require_once 'dbAggregation.php';

 4 require_once 'dbIterator.php';

 5 require_once 'rss_feed.php';

 6 $tbl = "blog";

 7 $query = "SELECT title, article description, ";

 8 $query .= "url link FROM $tbl";

 9 $conn = new dbAggregation();

10 $result = $conn->getResultSet($query);

11 $rss = new rss_feed($result,"save",'xml_docs/rss.xml');

12 $rss->setFeedTitle('OOP News');

13 $rss->setFeedLink('http://php.net/manual/en/language.oop5.php');

14 $rss->setFeedDescription('Get the lowdown on OOP and PHP');

15 $rss->setLastBuildDate('Sun, 14 Nov 2010 13:24:38-0700');

16 $rss->write();

17 $dir = 'xml_docs/rss.xml';

18 if(is_writable(dirname($dir)))

19 { $save = $rss->flush();

20 echo 'RSS file created'; }

21 else

22 { echo 'Problem with directory permissions'; }

23 ?>

There are two differences between displaying and saving. First, the final two arguments passed to object ‘rss_feed’ are ‘save’ and ‘xml_docs/rss.xml’ (line 11). The ‘save’ argument tells PHP to save the file and the ‘xml_docs/rss.xml’ argument tells PHP the file name to use. Second, the name of the directory is checked to see if it is writable (line 18).

Load ‘rss_save.php’ in a browser. Figure 8.2 shows the results. Verify the contents of ‘rss.xml’ in the ‘xml_docs’ directory.

Figure 8.2

Figure 8.2 Display Showing that RSS Feed Was Created and Saved

Definition and Characteristics of PHP Data Objects

The PHP Data Objects (PDO) extension defines a lightweight consistent interface for accessing databases in PHP. PDO provides a data-access abstraction layer, which means that, regardless of database, the same functions are used to issue queries and fetch data. So, PDO provides an object-oriented alternative to APIs. PDO is very fast because it is written in the ‘C’ language. PDO supports most databases, including Oracle, MySQL, PostGres, DB2, and MS SQL Server.

Display Data with PHP Data Objects

A major advantage of using PDO is that it is object-oriented. As such, it contains a set of methods that work with all supported databases. For instance, the ‘query()’ method executes an SQL statement and returns a result set as a ‘PDO’ object. The ‘exec()’ method executes an SQL statement and returns the number of affected rows. The ‘fetch()’ method fetches the next row from a result set.

PHP file ‘select_pdo.php’ displays data from the ‘web_site’ table (created in Chapter 3). Fill in your specific username, password, and host server information before running the code.

 1 <?php

 2 // File select_pdo.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "SELECT * FROM web_site";

 7 try

 8 { $db = @ new PDO("oci:dbname=$host",$user,$pass); }

 9 catch(PDOException $e)

10 { die('Connection error => ' . $e->getMessage()); }

11 $result = $db->query($sql);

12 if(!$result)

13 {

14 $error = $db->errorInfo();

15 die('Execute query error => ' . $error[2]);

16 }

17 $db = null;

18 while($row = $result->fetch(PDO::FETCH_ASSOC))

19 {

20 $id = $row['VID'];

21 $user = $row['VUSER'];

22 $pswd = $row['PSWD'];

23 $lvl = $row['LVL'];

24 echo $id . ' ' . $user . ' ';

25 echo $pswd . ' ' . $lvl . '<br />';

26 }

27 ?>

I begin by setting the Oracle host server, username, and password information (lines 3–5), and the SQL query (line 6). I use a ‘try–catch’ block to trap connection errors (lines 7–10). In line 8, I assign ‘$db’ the new instance with ‘@ new PDO("oci:dbname=$host",$user,$pass);’. The ‘@’ symbol suppresses error messages. PDO knows that the connection is to Oracle because "oci:dbname=$host" is included as the first parameter in the PDO object.

I continue by using the ‘PDOException’ class in the ‘catch’ (line 9) to handle errors. If there is an error message, I get it with the ‘getMessage()’ method (line 10) and stop processing with ‘die()’ (line 10).

In line 11, I use the ‘query()’ method to place the result set into ‘$result’. If the result set is empty, I prepare the ‘$error’ array to hold the error message with the ‘errorInfo()’ method (line 14), create the message and place it in ‘$error’ as the third element (line 15), and stop processing with ‘die()’ (line 15).

I assign ‘null’ to the ‘$db’ object (line 17) to close the connection. If the result set is not empty, I use the ‘fetch()’ method (line 18) to grab each row. I use the ‘PDO::FETCH_ASSOC’ directive so I can use the field names from the database to identify field values.

Be sure to capitalize field names from the Oracle database table! For instance, in line 20, ‘$id = $row['VID'];’ returns the value of the ‘VID’ field from the table. If you don’t capitalize the field name, an error is not generated, but no output is displayed because Oracle only recognizes capitalized field names. Using a ‘while’ loop (lines 18–26), I fetch each row from the database, set variables from the ‘fetched’ database row (lines 20–23), and display results (lines 24 and 25). Load ‘select_pdo.php’ into a browser. Figure 8.3 shows the results.

Figure 8.3

Figure 8.3 Output Showing ‘web_site’ Data Using ‘PDO’ Class

With PDO, a database connection class is not needed. In addition, PDO is built into the PHP core.

Prepare a SELECT Statement with PHP Data Objects

PDO offers an object-oriented solution to mitigating SQL injection. There are two ways to prepare SQL statements against injection – ‘named’ and ‘unnamed’.

PHP file ‘select_named.php’ uses a ‘named’ bind variable to prepare a SELECT statement. Fill in your specific username, password, and host server information before running the code.

 1 <?php

 2 // File select_named.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "SELECT * FROM web_site WHERE VID = :id";

 7 $id = '001';

 8 try

 9 { $db = new PDO("oci:dbname=$host",$user,$pass); }

10 catch(PDOException $e)

11 { die('Connection error => ' . $e->getMessage()); }

12 $stmt = $db->prepare($sql);

13 $stmt->bindParam(':id',$id,PDO::PARAM_STR,3);

14 $stmt->execute();

15 if(!$stmt)

16 {

17 $error = $db->errorInfo();

18 die('Execute query error => ' . $error[2]);

19 }

20 $db = null;

21 $row = $stmt->fetch(PDO::FETCH_ASSOC);

22 echo $row["VID"] . ' ' . $row["VUSER"] . ' ' .

23 $row["PSWD"];

24 echo ' ' . $row["LVL"];

25 ?>

The logic is the same as the previous example except for the binding logic, and since only one record is returned, there is no need for a ‘while’ loop. I use the ‘named’ bind variable ‘:id’ for the ‘VID’ field (line 6). SQL injection is mitigated because the value of the named bind variable is not determined until after the Oracle execution plan is finished. If a hacker attempts to inject SQL, Oracle does not interpret it.

I use the ‘bindParam()’ method (line 13) to bind the value in ‘$id’ into the ‘named’ variable ‘:id’. The directive ‘PDO::PARAM_STR’ tells the method that the bind variable is a string. The parameter ‘3’ tells the method that the field has three characters. PDO method ‘execute()’ (line 14) runs the SQL and returns the number of affected rows. Load ‘select_named.php’ into a browser. Figure 8.4 shows the results.

Figure 8.4

Figure 8.4 Output of Record Using ‘PDO’ Class and ‘string’ Named Binding

In the previous example, I used a string for the bind value. In this example, I need to use a number (integer) for the bind value, so I have to change the ‘bindParam()’ directive. I also need a ‘while’ loop because more than one record is returned.

 1 <?php

 2 // File select_named_int.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "SELECT * FROM web_site WHERE LVL = :lvl";

 7 $lvl= 2;

 8 try

 9 { $db = new PDO("oci:dbname=$host",$user,$pass); }

10 catch(PDOException $e)

11 { die('Connection error => ' . $e->getMessage()); }

12 $stmt = $db->prepare($sql);

13 $stmt->bindParam(':lvl',$lvl,PDO::PARAM_INT);

14 $stmt->execute();

15 if(!$stmt)

16 {

17 $error = $db->errorInfo();

18 die('Execute query error => ' . $error[2]);

19 }

20 $db = null;

21 while($row = $stmt->fetch(PDO::FETCH_ASSOC))

22 {

23 $id = $row['VID'];

24 $user = $row['VUSER'];

25 $pass = $row['PSWD'];

26 $lvl = $row['LVL'];

27 echo $id . ' ' . $user . ' ' . $pass . ' ' . $lvl .

28 '<br />';

29 }

30 ?>

Instead of using a bind variable for the ‘VID’ character string field, I use a ‘named’ bind variable for the ‘LVL’ number field (line 6). Also, I change the directive in the ‘bindParam()’ method to ‘PARAM_INT’ (line 13). Finally, I add a ‘while’ loop (lines 21–29) to process the results. The rest of the logic is the same as the previous example in ‘select_named.php’. Load ‘select_named_int.php’ into a browser. Figure 8.5 shows the results.

Figure 8.5

Figure 8.5 Output of Record Using ‘PDO’ Class with ‘integer’ Named Binding

In the next example, I use the ‘?’ symbol to indicate that a bind variable is ‘unnamed’. Fill in username, password, and host server information before running the code.

 1 <?php

 2 // File select_unnamed.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "SELECT * FROM web_site WHERE VID = ?";

 7 $id = '001';

 8 try

 9 { $db = new PDO("oci:dbname=$host",$user,$pass); }

10 catch(PDOException $e)

11 { die('Connection error => ' . $e->getMessage()); }

12 $stmt = $db->prepare($sql);

13 $stmt->bindParam(1,$id);

14 $stmt->execute();

15 if(!$stmt)

16 {

17 $error = $db->errorInfo();

18 die('Execute query error => ' . $error[2]);

19 }

20 $db = null;

21 $row = $stmt->fetch(PDO::FETCH_ASSOC);

22 echo $row["VID"] . ' ' . $row["VUSER"] . ' ' .

23 $row["PSWD"];

24 echo ' ' . $row["LVL"];

25 ?>

In line 6, I use ‘?’ for an ‘unnamed’ bind variable. The ‘bindParam()’ method is even simpler (line 13). The first parameter is ‘1’ because only one bind variable is used. The second parameter is ‘$id’ to indicate the value to use. With unnamed bind variables, the type of data doesn’t matter. So, you do not need to change the parameters for method ‘bindParam()’ to bind to a number field. Load ‘select_unnamed.php’ into a browser. Figure 8.6 shows the results.

Figure 8.6

Figure 8.6 Output of Record Using ‘PDO’ Class with Unnamed Binding

Prepare an INSERT Statement with PHP Data Objects

PHP file ‘insert_named.php’ uses ‘named’ bind variables to prepare an INSERT statement. Fill in your specific host server, username, and password information before running the code.

 1 <?php

 2 // File insert_named.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "INSERT INTO web_site VALUES

 7 (:vid, :vuser, :pswd, :lvl)";

 8 $data = array('006','Mark D.','Spot',2);

 9 try

10 { $db = @ new PDO("oci:dbname=$host",$user,$pass); }

11 catch(PDOException $e)

12 { die('Connection error => ' . $e->getMessage()); }

13 $stmt = $db->prepare($sql);

14 $result = $stmt->execute($data);

15 if(!$result)

16 {

17 $error = $db->errorInfo();

18 die('Query error => ' . $error[2]);

19 }

20 $db = null;

21 ?>

The named bind variables are ‘:vid’, ‘:vuser’, ‘:pswd’, and ‘:lvl’ (line 7). Keep in mind that bind variables can be given any name, but I use the same names for the bind variables as the database fields for convenience. I use array ‘$data’ (line 8) to hold the values for the bind variables. I use the ‘prepare()’ method (line 13) to ready the ‘INSERT’ statement for binding. I use the ‘execute()’ method (line 14) to complete the binding process.

Load ‘insert_named.php’ into a browser. An ‘INSERT’ statement adds data to a table, but doesn’t create a result set so nothing is displayed on the browser. So, use ‘Oracle SQL Developer’ to check the table for a new record.

PHP file ‘insert_unnamed.php’ uses an ‘unnamed’ bind variable to prepare an INSERT statement. Fill in your specific host server, username, and password information before running the code.

 1 <?php

 2 // File insert_unnamed.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "INSERT INTO web_site VALUES (?,?,?,?)";

 7 $data = array('007','Rosy','Palms',2);

 8 try

 9 { $db = @ new PDO("oci:dbname=$host",$user,$pass); }

10 catch(PDOException $e)

11 { die('Connection error => ' . $e->getMessage()); }

12 $stmt = $db->prepare($sql);

13 $result = $stmt->execute($data);

14 if(!$result)

15 {

16 $error = $db->errorInfo();

17 die('Query error => ' . $error[2]);

18 }

19 $db = null;

20 ?>

The only difference between this code example and the ‘named’ one is that the bind variables are question marks ‘?’ (line 6).

Load ‘insert_unnamed.php’ into a browser. Use ‘Oracle SQL Developer’ to check the table.

Prepare an UPDATE Statement with PHP Data Objects

PHP file ‘update_named.php’ uses a ‘named’ bind variable to prepare an UPDATE statement. Fill in your specific host server, username, and password information before running the code.

 1 <?php

 2 // File update_named.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "UPDATE web_site SET pswd = 'Spots'

 7 WHERE vid = :vid";

 8 $vid = '006';

 9 try

10 { $db = @ new PDO("oci:dbname=$host",$user,$pass); }

11 catch(PDOException $e)

12 { die('Connection error => ' . $e->getMessage()); }

13 $stmt = $db->prepare($sql);

14 $stmt->bindParam(1,$vid);

15 $result = $stmt->execute();

16 if(!$result)

17 {

18 $error = $db->errorInfo();

19 die('Query error => ' . $error[2]);

20 }

21 $db = null;

22 ?>

The logic is the same as the ‘insert_named.php’ example except that the SQL statement is an ‘UPDATE’ (lines 6 and 7). Load ‘update_named.php’ into a browser. Use ‘Oracle SQL Developer’ to verify the results.

PHP file ‘update_unnamed.php’ uses an ‘unnamed’ bind variable to prepare an UPDATE statement. Fill in your specific host server, username, and password information before running the code.

 1 <?php

 2 // File update_unnamed.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "UPDATE web_site SET pswd = 'Palm' WHERE vid = ?";

 7 $vid = '007';

 8 try

 9 { $db = @ new PDO("oci:dbname=$host",$user,$pass); }

10 catch(PDOException $e)

11 { die('Connection error => ' . $e->getMessage()); }

12 $stmt = $db->prepare($sql);

13 $stmt->bindParam(1,$vid);

14 $result = $stmt->execute();

15 if(!$result)

16 {

17 $error = $db->errorInfo();

18 die('Query error => ' . $error[2]);

19 }

20 $db = null;

21 ?>

The logic is the same as the ‘insert_unnamed.php’ example except that the SQL statement is an ‘UPDATE’ (lines 6 and 7). Load ‘update_unnamed.php’ into a browser. Use ‘Oracle SQL Developer’ to verify the results.

Prepare a DELETE Statement with PHP Data Objects

PHP file ‘delete_named.php’ uses a ‘named’ bind variable to prepare a DELETE statement. Fill in your specific host server, username, and password information before running the code.

 1 <?php

 2 // File delete_named.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "DELETE FROM web_site WHERE vid = :vid";

 7 $vid = '006';

 8 try

 9 { $db = @ new PDO("oci:dbname=$host",$user,$pass); }

10 catch(PDOException $e)

11 { die('Connection error => ' . $e->getMessage()); }

12 $stmt = $db->prepare($sql);

13 $stmt->bindParam(1,$vid);

14 $result = $stmt->execute();

15 if(!$result)

16 {

17 $error = $db->errorInfo();

18 die('Query error => ' . $error[2]);

19 }

20 $db = null;

21 ?>

The logic is the same as the ‘update_named.php’ example except that the SQL statement is a ‘DELETE’ (line 6). Load ‘delete_named.php’ into a browser. Use ‘Oracle SQL Developer’ to verify the results.

The next example deletes a record using an ‘unnamed’ bind variable. Fill in your specific host server, username, and password information before running the code.

 1 <?php

 2 // File delete_unnamed.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $sql = "DELETE FROM web_site WHERE vid = ?";

 7 $vid = '007';

 8 try

 9 { $db = @ new PDO("oci:dbname=$host",$user,$pass); }

10 catch(PDOException $e)

11 { die('Connection error => ' . $e->getMessage()); }

12 $stmt = $db->prepare($sql);

13 $stmt->bindParam(1,$vid);

14 $result = $stmt->execute();

15 if(!$result)

16 {

17 $error = $db->errorInfo();

18 die('Query error => ' . $error[2]);

19 }

20 $db = null;

21 ?>

The logic is the same as the ‘update_unnamed.php’ example except that the SQL statement is a ‘DELETE’ (line 6). Load ‘delete_unnamed.php’ into a browser. Use ‘Oracle SQL Developer’ to verify the results.

Transactions with PHP Data Objects

A transaction comprises a unit of work performed within a database management system (DBMS) against a database. Transactions have two main purposes. First, they provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure. Second, they provide isolation between programs accessing a database concurrently.

A database transaction must be atomic, consistent, isolated, and durable (ACID). An atomic transaction is one that either completes all of its database operations or none of them. A consistent transaction is one where results are predictable. An isolated transaction is one where any changes made are independent and visible from other concurrent operations. A durable transaction is one that, once committed (saved to the database), will survive permanently.

ACID Test

Databases use two mechanisms to ensure that transactions pass the ACID test – rollback and commit. Rollback is an operation that returns the database to a previous state and ends the transaction. Commit is an operation that ends the transaction and makes all changes visible to other users.

By using rollback and commit with transactions, the integrity of a database can be ensured. If a transaction fails to complete, rollback can be used to restore the database to a previous, clean state. If a transaction completes successfully, commit can be used to save changes to the database.

PHP file ‘transaction1.php’ shows how transactions work in PDO. Fill in your specific database information before running the code.

 1 <?php

 2 // File transaction1.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $db = new PDO("oci:dbname=$host",$user,$pass);

 7 $sql1 = "INSERT INTO web_site VALUES ('006','Mark D.','Spot',2)";

 8 $sql2 = "INSERT INTO web_site VALUES ('006','Eileen','Down',2)";

 9 $sql3 = "UPDATE web_site SET pswd = 'Downe' WHERE vid = '006'";

10 $db->beginTransaction();

11 $result = $db->exec($sql1);

12 $db->rollback();

13 $db->beginTransaction();

14 $result = $db->exec($sql2);

15 $result = $db->exec($sql3);

16 $db->commit();

17 $db = null;

18 ?>

I begin by creating a new PDO object and assigning it to ‘$db’ (line 6). I use three SQL statements as transactions and assign them to ‘$sql1’, ‘$sql2’, and ‘$sql3’, respectively (lines 7–9). The first two are INSERT statements, and the last one is an UPDATE statement. I begin a new transaction by invoking the ‘beginTransaction()’ method (line10). I execute the first SQL statement of the transaction in line 11. I then ‘rollback’ the transaction in line 12. I begin a new transaction in line 13 and execute the second and third SQL statements in the transaction. I end by saving the transaction (line 16).

So, the data associated with the ‘INSERT’ statement assigned to ‘$sql1’ is not reflected in the database because the first transaction was rolled back. When a transaction is rolled back, it is like nothing happened! The second transaction was saved so the SQL in lines 8 and 9 were saved. When a transaction is committed, the results are saved to the database.

Load ‘transaction1.php’ into a browser. Use ‘Oracle SQL Developer’ to verify the results.

What Happened with Transaction 1?

Since the first ‘INSERT’ statement is rolled back (in the first transaction), the database does not reflect the change. The second and third SQL statements are saved to the database (in the second transaction). As a result, the ‘Eileen’ record is added to the database and its password is modified to ‘Downe’.

Like the previous example, PHP file ‘transaction2.php’ starts two transactions. However, the first transaction executes two ‘INSERT’ statements. This transaction is reversed because of the ‘rollback’. The second transaction deletes the record with ‘VID’ of ‘6’, executes the second ‘INSERT’, updates the password, and commits.

 1 <?php

 2 // File transaction2.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $db = new PDO("oci:dbname=$host",$user,$pass);

 7 $sql1 = "INSERT INTO web_site VALUES ('006','Eileen','Down',2)";

 8 $sql2 = "DELETE FROM web_site WHERE vid = '006'";

 9 $sql3 = "INSERT INTO web_site VALUES ('006','Mark D.','Spot',2)";

10 $sql4 = "UPDATE web_site SET pswd = 'Spots' WHERE vid = '006'";

11 $db->beginTransaction();

12 $result = $db->exec($sql1);

13 $result = $db->exec($sql3);

14 $db->rollback();

15 $db->beginTransaction();

16 $result = $db->exec($sql2);

17 $result = $db->exec($sql3);

18 $result = $db->exec($sql4);

19 $db->commit();

20 $db = null;

21 ?>

Load ‘transaction2.php’ into a browser. Use ‘Oracle SQL Developer’ to verify the table contents.

What Happened with Transaction 2?

Since both ‘INSERT’ statements are rolled back (in the first transaction), the database does not reflect these changes (line 14). The second, third, and fourth SQL statements (lines 8–10) are saved to the database (in the second transaction). As a result, the ‘Eileen’ record is deleted from the database, the ‘Mark D.’ record is added to the database, and its password is modified to ‘Spots’.

Like the previous example, PHP file ‘transaction2.php’ starts two transactions. However, the first transaction executes two ‘INSERT’ statements. This transaction is reversed because of the ‘rollback’. The second transaction deletes the record with ‘VID’ of ‘6’, executes the second ‘INSERT’, updates the password, and commits.

The final example, PHP file ‘transaction3.php’ restores the database to its original state by deleting the record with ‘VID’ of ‘6’ and saving changes with commit (lines 8–10).

 1 <?php

 2 // File transaction3.php

 3 $user = '';

 4 $pass = '';

 5 $host = '';

 6 $db = new PDO("oci:dbname=$host",$user,$pass);

 7 $sql1 = "DELETE FROM web_site WHERE vid = '006'";

 8 $db->beginTransaction();

 9 $result = $db->exec($sql1);

10 $db->commit();

11 $db = null;

12 ?>

Load ‘transaction3.php’ into a browser. Use ‘Oracle SQL Developer’ to verify the table contents.

Summary

The goal of this chapter was to gain skills in two areas. First, I provided instruction on how to create an RSS feed. Second, I provided instruction on how to use the PDO extension to communicate with the Oracle database.