IFS 234 – Server side application DEvelopment

1. IFS 234 – Server side application DEvelopment TOPIC 2 – DATABASEAPPLICATIONS

2. Database systems • Server side databases • Oracle • MySQL • Microsoft SQL Server • Client side databases • SQLite (for web and mobile)

3. Access to Server databases • Microsoft / Windows • ODBC • ODBC (Open Database Connectivity) is a standard C programming languagemiddlewareAPI for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems; an application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

4. Access to Server databases • phpmyAdmin phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL with the use of a web browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions. • Other DBMS-specific workbenches • MySQL workbench • HeidiSQL

5. Access to Server databases • Demo • phpMyAdmin to localhost mysqld service • HeidiSQL to non-WAMP mysqld service (installed seperately) • Security / antivirus and firewall interference

6. Access to Server databases • Demo • phpMyAdmin to localhost mysqld service • HeidiSQL to non-WAMP mysqld service (installed seperately) • Security / antivirus and firewall interference

7. ACCESS your database from code • PHP example: $username = “xxxx"; $password = “yyyyy"; $hostname = “172.172.172.172"; //connection to the database $dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL"); echo "Connected to MySQL<br>"; //select a database to work with $selected = mysql_select_db(“my_db",$dbhandle) or die("Could not find database");

8. SQL – Structured query language • SQL(Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS). • Originally based upon relational algebra and tuple relational calculus, SQL consists of a data definition language and a data manipulation language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.

9. SQL within server side programming • PHP example: $result_measure = mysql_query("SELECT measure_id, cat_id, measure_desc, grams FROM measure ORDER BY measure_id ASC"); while ($row = mysql_fetch_array($result_measure)) $db_array[] = $row; foreach($db_array as $key=>$value){ $temp_arr["measure_id"] = $value['measure_id']; $temp_arr["cat_id"] = $value['cat_id']; $temp_arr["measure_desc"] = $value['measure_desc']; $temp_arr["grams"] = $value['grams']; $json_arr[] = $temp_arr; } echo json_encode($json_arr);

10. To summarize • DBMS running on server • DBMS contains one or more databases • Connect to DBMS using workbench programs such as phpMyAdmin, MySQL Workbench, or HeidiSQL • Make sure you have this working for localhost (your server) • Connect to DBMS from within server side code (PHP) using host, username, password details • Manipulate data within a server side database using SQL commands from within your PHP code