Web-Oriented 3-Tier Architecture with PHP, MySQL, and phpMyAdmin

UFCFR3-30-1 Information Technology (2021/22) Lecture 6 Multitier Architecture, PHP & MySQL

web-oriented 3-tier architecture (php example) Web Services

Web-oriented 3-tier architecture: tools & technologies oPresentation tier Browser / custom client, Client Side Scripting (JavaScript), Applets. o Logical Tier Web Server (Apache, IIS, Websphere etc.); Scripting Languages (PHP, Perl etc.), Programming Languages (Java, Python, C, C# etc), Application Frameworks (Ruby on Rails, Symfony etc.) oData Tier Database Management System (DBMS) (Oracle, MySQL, SQL Server, DB2 etc.), XMLDB s, NoSQL DB s.

MySQL in the persistence / data tier : MySQL o Open Source (relational) database server - Runs on many platforms (Unix & Windows) o Networked server no fancy GUI like MS Access. - You can find clients(such as phpMyAdmin)that provide a GUI. o Great for small, medium to large-sized applications (ebay, amazon, facebook etc. all make use of it)

MySQL in the persistence /data tier : MySQL Strengths o High performance benchmarks very well against commercial dbs o Low-cost no cost under open source licence o Easy to configure and learn easy to set up, SQL compliant o Portable Linux, Unix and Windows versions o Open Source source code is available for modification

phpMyAdmin o A MySQL client written in PHP o Via a browser you can : o Manage Databases o Manage MySQL users o Create tables, add/edit/delete data, browse data o Submit queries (SQL) o Import and Export tables Ensure MySQL is running using the XAMPP control panel o A great way to learn SQL! o phpMyAdmin @ localhost : http://localhost/phpmyadmin

phpMyAdmin interface: phpmyadmin help mysql help manage users db status pop-up sql window home import data export db databases

PHP (main) APIs for using MySQL There are three main PHP API options when considering connecting to a MySQL database server: oPHP's MySQL Extension - original extension which provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. Can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available. o PHP's mysqli Extension - MySQL improved extension takes advantage of new features found in MySQL versions 4.1.3 and newer. The mysqliextension is included with PHP versions 5 and later. o PHP Data Objects (PDO) - PHP Data Objects, or PDO, is a database abstraction layer that provides a consistent API regardless of the type of database server. In theory, it allows for the switch of the database server, from say Firebird to MySQL, with only minor changes to the PHP code.

Advantages of the MySQLi API o Object-oriented interface o Support for Prepared Statements o Support for Multiple Statements o Support for Transactions o Enhanced debugging capabilities o Embedded server support Note: MySQL versions version 4.1.3 or later it is strongly recommended.

example MySQL db Entity model Example records

php script using mysqli (select example) <?php // Connect to the db $mysqli = new mysqli( hostname', username', password', database'); // Send the query to the database and pull the records in a // certain city using the SELECT statement // If the result returns true if ($result = $mysqli->query("SELECT name, wp_link FROM station // print out the number of records retrieved echo 'For the city of "Bristol", there are ' .$result->num_rows.' records.<br/>'; WHERE city='Bristol'")) { // The "fetch_object()" method allows access to the returned // rows within the resource object ($result in this case). while ($row = $result->fetch_object()) { echo 'Station: '.$row->name.' '; echo 'Wikipedia Link: <a href='.$row->wp_link.'>'.$row->wp_link. '</a><br/>'; } } else { // it s an error & the query failed echo $mysqli->error; } // end else $mysqli->close(); ?> run example

refactoring repeating data to another table Entity model

refactored data table: station1 table: city

reformulated sql query with join field name disambiguation SELECT station1.name, station1.wp_link FROM station1, city WHERE station1.city_code = city.code AND city.name = 'Bristol' tables join condition

php script with sql join <?php // Connect to the db $mysqli = new mysqli(( hostname', username', password', database ); // Send the query to the database and pull the records in a // certain city using the SELECT statement // If the result returns true if ($result = $mysqli->query("SELECT station1.name, station1.wp_link FROM station1, city WHERE station1.city_code = city.code AND city.name = 'Bristol'")) { // print out the number of records retrieved echo 'For the city of "Bristol", there are ' .$result->num_rows.' records.<br/>'; } else { // it s an error & the query failed echo $mysqli->error; } // end else $mysqli->close(); ?> // The "fetch_object()" method allows access to the returned // rows within the resource object ($result in this case). while ($row = $result->fetch_object()) { echo 'Station: '.$row->name.' '; echo 'Wikipedia Link: <a href='.$row->wp_link.'>'.$row->wp_link. '</a><br/>'; } run example

Further reading with examples: PHP Connect to MySQL