PROG2002 Topic 5 Server-side Database Access With PHP

PROG2002Topic 5Server-side DatabaseAccess with PHPIntroductionIn this topic you will start building up the Data access interface of a web application using PHP. Inparticular, the focus is on using PDO - the PHP Data Objects - to interface with a SQL databaseserver such as MySQL. We begin the topic by looking at different database access options availablein PHP and why we choose PDO among the others. Next, we will examine in details how to usePDO to perform standard CRUD (create, read, update, and delete) operations on MySQL databases.Finally, we will discuss how the Database access interface should be constructed.Fig. 5.1 High-level design of a generic web applicationObjectivesOn completion of this topic, you should be able to: Compare and contrast PDO and other database access options in PHP Use PDO to perform CRUD operations on a SQL database server like MySQL Build a data access interface for a web application

PROG2002CSC10217 Topic 5 - Server-side Database Access with PHPDatabase Access in PHPDatabase Access Options in PHPPHP provides access to most of the current database technologies including SQL and NoSQLdatabases. The access is offered via database extensions, which are categorised into two groups: Data abstraction extensions: PDO, ODBC and DBA are extensions in this group. Vendor-specific extensions: mysqli, mssql and sqlite extensions in this group.The full list of the data access extensions is available at http://php.net/manual/en/refs.database.php.Each data access extension group has its own advantages and disadvantages. For example, the mainadvantage of the data abstraction extensions is that they provide a uniform way for accessingdifferent database technologies. The same program code can be used to access MySQL, MSSQL,IBM DB2 etc. with a little or even without modification. In addition, due to the abstraction theseextensions are less likely to have incompatibility issues when there is a technology upgrade.However, also due to the abstraction, these extensions often cannot fully explore the capability ofeach database technology to its edge, which potentially reduce the application performance.On the other hand, the vendor-specific extensions are tailored to individual technology. As a result,different program code has to be written to access different database technologies. Compatibility isalso a big issue when using vendor-specific extensions since the standard is maintained by individualvendor and can be changed any time without warning. However, vendor-specific extensions mayhave some performance advantage over the abstract data access extensions.PHP Data Objects – PDO vs. Other Data Access ExtensionsPDO is an abstract data access extension introduced in PHP5. Compared to ODBC and DBA, whichare two other extensions of the same group PDO is newer and native to PHP. While DBA onlysupports IBM DB2 and text-based databases, ODBC is not a native PHP extension. In fact, ODBCworks like another abstraction layer that wraps PDO and other database drivers inside. ThereforeODBC is slower than PDO and it is more suitable for existing applications that already use ODBC.PDO provides a unified interface for working with various RDBMS including creating databaseconnection, performing queries, handling errors, etc. PDO makes use of database specific driverse.g., PDO MYSQL for MySQL, PDO PGSQL for PostgreSQL and PDO OCI for Oracle tofunction properly as illustrated in Fig 5.2. So to use PDO for a specific database, you have to makesure that the corresponding database driver is available and properly configured for PHP access.Fig. 5.2 PDO ArchitecturePage 2

PROG2002CSC10217 Topic 5 - Server-side Database Access with PHPUp to this point, the selection of PDO for database access in PHP is obvious. We only select otherextensions in case there is a special reason for not using PDO. For example, we want to use someexisting code that already written for ODBC or vendor-specific data access extensions.Activity 5.1Using the resources you find on the Internet, compare PDO and mysqli in terms of performance.Database Access with PDOPDO provides a uniform way to access MySQL, PostgreSQL and other database technologies.Therefore, we will use MySQL database to demonstrate the use of PDO.Before using PDO, we have to make sure that the extension is enabled for a specific databasetechnology by checking the PHP configuration file php.ini. In case of MySQL, make sure you havethe following line uncommented in php.ini (this is already done on the infotech.scu.edu.au server).Open connection to database serverTo open a connection to MySQL database server, use the following connection string. mysql: is the name of the technology and can be replaced with the string representing otherdatabase technology such as pgsql for PostgreSQL. host: is the hostname or IP address of the corresponding SQL server dbname: is the name of the database you want to connect to.In addition, you will also need to provide the username and password to access the database. Thefollowing is an example of using PDO to open a connection to a MySQL database.The connection to the database is stored in dbConn variable and will be used in the future databaseaccess. You will need to make this variable accessible to all functions that require database access.There are a few constants that control the PDO behaviours such as PDO::ATTR ERRMODE andPDO::ERRMODE EXCEPTION. These constants force PDO to report errors if any by throwingcorresponding exceptions. More about PDO constants in case you are needed are available ecute SQL commandsDirect execution:Page 3

PROG2002CSC10217 Topic 5 - Server-side Database Access with PHPPDO provides several methods to execute an SQL query depending on the type of the query. If theSQL query does not need to return data i.e. CREATE, DELETE, INSERT and UPDATE query andcontains no parameters, it can be executed using PDO::exec method. PDO::exec returns the numberof rows affected by the query. For example, the following code execute a CREATE query:If the SQL query needs to return data i.e. SELECT query and contains no parameters, it can beexecuted using PDO::query method as showed in the following example:Page 4

PROG2002CSC10217 Topic 5 - Server-side Database Access with PHPActivity 5.2There is a PDO constant PDO::FETCH BOTH that affects the way the result is returned. In placeof PDO::FETCH BOTH you can use PDO::FETCH NUM and PDO::FETCH ASSOC. What arethe difference between these options?Execution via PDOStatement:PDO provides another method to execute an SQL query using PDOStatement object. This methodis universal as it can be used for any SQL query. However, it is more common to use this method ifsome parameters have to be passed to the query before the execution.The following is an example of using PDOStatement to execute an INSERT query:You can see there are two more steps have to be executed using this execution method. First, thequery has to be prepared by calling conn- prepare( sql); This call will check the query forsyntactical errors and return a prepared PDOStatement object called sqlprep. Next, the parametersare passed to the statement using bindValue and bindParam methods. The main difference betweenthese two methods are the values assigned by PDOStatement::bindParam will only be evaluatedat the execution time. Finally, the statement is executed by calling PDOStatement::execute().Activity 5.3Create a PHP script using PDO to create and perform CRUD operations on the following databasetable: tab user (id, username[10], password[10], access-level [0,1,2]). Use your mysql local databaseand your username and password in this activity.Page 5

PROG2002CSC10217 Topic 5 - Server-side Database Access with PHPData access interface for a web applicationA good practice in building software application is to create a data access interface or object, whichis responsible for all data access operations of the application. This centralised access design hassome significant advantages: It provides a uniform way to access the application specific data for all other modules It removes the need to implement database access in each application module. It is easy for parallel development, easy to replace, upgrade and troubleshootThe data access interface object of an application should provide the standard CRUD operations toall application data objects. For example, if an application needs to manage two types of data objects:user and car, then the interface should have methods to Create, Read (Select), Update and Deleteusers and cars. Therefore, before start building the data access interface for a web application, it isimportant to find out all data objects the application has to manage and the database operations haveto perform on them. For example, an application may need to select cars by year, make and colour.In this case several methods to select cars have to be implemented in the interface.Activity 5.4Start building the database access interface for the web application described in Assignment-1. First,watch the provided video tutorials in this week. Next, enumerate all data objects the application hasto manage and all data operations you should perform on them. In the implementation state, usebv caryard dba.php in bvcardyard to guide you.Assignment adviceIn this week you continue working on version-1 of the movie zone application. I will start workingon the data access interface for the movie zone application as directed in activity 5.4. You will writea PHP class to capture interface design. By the end of this week, you must have the following: High-level design diagram of the movie zone application Low-level design diagram of the movie zone application (version 1) Implementation of the Controller class (version 1) Implementation of the Model class (version 1 with mock-up methods) Implementation of the View class (version 1 with simple plain text output methods) Implementation of the data access class (version 1)SummaryIn this topic you have learned to complete another major task in the server side development:handling database access. You have been introduced to different database access options in PHP andtheir advantages and disadvantages. You should understand by now why PDO is chosen to performdatabase access in PHP in most situations. Furthermore, you have examined in detail how to usePDO to access MySQL databases. Finally, some insightful discussion about building the data accessinterface layer for web applications have been presented, which will guide you in building your dataaccess interface layer.In the next topic, you will start looking at the client-side technology i.e. JavaScript in particular.Page 6

PROG2002CSC10217 Topic 5 - Server-side Database Access with PHPReferencesCoursesweb.net. (2018). PHP-MySQL free course, online tutorials PHP MySQL code. [online]Available at: http://coursesweb.net/php-mysql [Accessed 6 Apr. 2018].Php.net. (2018). PHP: PDO - Manual. [online] Available at: http://php.net/manual/en/book.pdo.php[Accessed 6 Apr. 2018].Page 7

Server-side Database Access with PHP Introduction In this topic you will start building up the Data access interface of a web application using PHP. In particular, the focus is on using PDO - the PHP Data Objects - to interface with a SQL database server such as MySQL. We begin the topic