PHP II Interacting with Database Data

1. PHP IIInteracting with Database Data

2. The whole idea of a database-driven website is to enable the content of the site to reside in a database, and to allow that data to be accessible on the website. • ‘Access’ to data is not limited to viewing it alone. • Very frequently, it is desirable that data is captured on the website, and transferred (in real time) for storage, or for manipulation. • Server-side programming languages such as PHP act as an intermediary between the client (browser), and the database. • PHP enables the design of the presentation component of a web page to be created in HTML (as templates), and then retrieves the ‘content’ dynamically from the database. • Data submitted through a web page can also be transmitted to the database, for processing.

3. Most relational database systems implement Structured Query Language (SQL). • PHP is able to communicate query requests to the database with the use of SQL. Basics of SQL • Relational database systems store data in tables, that are conceptually similar to ‘regular’ tables. • There are mechanism for maintaining the integrity of the data, such as constraints and database triggers. • Standard commands exist for creating databases, and database objects. • There are also commands that are used for manipulating data, such as insertion, deletion, and update of records.

4. Creating a Database • Basic command for creating a database is as follows: create database database_name; • There are rules for naming databases, and database objects (beyond the scope of this lecture), but generally the names should begin with an alphabetic character, and should not contain blank spaces. • A new database will be empty until you create objects such as tables in it. Creating Tables • In creating a database table, the sort of data to be stored in the columns must be considered, so that the columns can be defined appropriately.

5. Basic command: create table tablename(column1 column1’s datatype, column2 column2’s datatype, columnn columnn’s datatype); • E.g. create table students(student_id int, first_name varchar(20), last_name varchar(30)); • The datatype for the student_id column was defined as integer, implying that it can contain integer values, while the other 2 columns were defined as varchar (variable length character columns). • The value enclosed in parentheses next to ‘varchar’ specifies the maximum length of data permitted in the column. • Other datatypes commonly used are ‘Date’ for storing date values, and char (for storing fixed character length values)

6. Altering Tables • Database tables sometimes have to be altered to accommodate ‘new’ records, or because the existing columns have become unsuitable to support business requirements • In altering tables, new columns may be added, or existing columns expanded. • Basic commands: alter table table_name modify column new_column_definition; E.g. alter table students modify first_name varchar(40); alter table table_name add new_column datatype; E.g. alter table students add DOB date; • In the first example, an existing column was modified, while in the second example, an entirely new column was added to the table.

7. Dropping Tables • Tables can be dropped (deleted) from the database, when they are no longer required. • Syntax: drop table table_name; • Be extremely cautious in doing this as it is irreversible. Inserting Data into a Table • Basic command: insert into tablename(column1, column2,column3,...) values (value1, value2, value3,...); E.g. insert into students(student_id, first_name, last_name) values (19899,’Peter’,’Jackson’); • Note that values inserted into character or date columns need to be enclosed in a string.

8. Retrieving Data from a table • Achieved with the ‘select’ statement. select student_id, first_name, last_name from students; • A short-cut for selecting all the columns in a table is to issue the command: select * from table_name; e.g. select * from students • There are times when it might be desirable to restrict the query output with certain criteria. This is achieved with the use of a ‘where’ clause • E.g. select * from students where surname = ‘Jackson’; would retrieve only the stipulated record from the database. Updating Data • This is the process of changing values in the database. • Basic command: update tablename set column = new_value where condition;

9. E.g. update students set student_id = 234099 where last_name=‘Jackson’; • It is possible to update more than one column with one statement e.g. update students set student_id = 234099, first_name = ‘Paul’ where last_name = ‘Jackson’; Deleting records from the database • Basic command: • delete from table_name where condition e.g. delete from students where last_name=‘Jackson’; • Without specifying a criterion in the where clause, all the rows of data in the table would be deleted

10. Connecting to MySQL with PHP • Before you can interact with data held in a database, you need to establish a connection with the database, as they are separate entities. • PHP provides a built-in function for connecting to the database called mysql_connect. • This function takes the form mysql_connect(address, username, password); where address is the IP address or host name of the MySQL server, username and password are the log-in details of the user on the MySQL server. • It returns a number value that identifies the connection that has been established, and this number is usually assigned to a variable. • If the connection fails, the function evaluates to false. • E.g. $conn = mysql_connect(‘localhost’, ‘username’, ‘password’); • The $conn variable is subsequently used as an argument in other related functions.

11. It is important to programmatically check that a connection was established after calling the MySQL function, so that the error can be trapped, and handled appropriately. • E.g. $conn = mysql_connect(‘localhost’, ‘username’, ‘password’); if (!$conn) { echo(‘<P>Unable to connect to the database at this time. Please try later </P>’); exit(); } Line 2 could also have been written: if ($conn = = ‘false’) {

12. Selecting the database • Usually, the database server would contain several databases, hence there is a logical need to select the required one. • The built-in function used to carry out this function is mysql_select_db. • It takes 2 arguments: the name of the database, and the number or connection identifier returned by the mysql_connect function. The latter is defaulted to the value of the last connection established if no value is supplied. • The function returns true when it is executes successfully, and false otherwise. • E.g. if (!mysql_select_db(‘database_name’,$conn) { echo(‘<P>Unable to locate the database</P>’);} //alternative actions to perform if successful......

13. Processing SQL Queries with PHP • The built-in function for processing SQL queries is mysql_query • It accepts 2 parameters (query, connection_id). • The query parameter is a string that contains the SQL command. • E.g. $query = ‘insert into students (student_id, first_name, last_name) values (556283, ‘Peter’, ‘Jackson’); if (mysql_query($query)) { echo(‘<P>New student successfully added to the database</P>’); } else { echo(‘<P>Unable to insert record into the database</P>’); }

14. PHP also has built-in functions that keep track of the number of rows affected by data manipulation commands. • This function is called mysql_affected_rows( ), and it returns the number of rows processed as a result of the insert, delete, or update command. • E.g. $query = ‘delete from students where last_name = ‘Smith’); if (mysql_query($query)) { echo(‘<P>’ . mysql_affected_rows( ) . students share the surname and were deleted </P>’); }

15. Handling SELECT Result Sets • PHP provides useful built-in functions that enable access to record sets retrieved from the database, when select statements are issued. • When PHP processes a query successfully, the mysql_query returns a number that identifies the result set, which contains all the rows returned from the query. It is therefore necessary to declare a variable to store the record set, as shown below: $query = ‘select * from students’ $result = mysql_query($query); If the above query was successful, the $result variable now contains the query output. Individual rows of the result can now be processed by using one of the available built-in functions, such as mysql_fetch_array

16. The my_sql_fetch_array function accepts a result set as a parameter, and fetches the next row in the result set as an array. It eventually returns false when there are no more rows to fetch. • This makes it quite useful in loops. • E.g. $query = ‘select * from students’ $result = mysql_query($query); while ($row = mysql_fetch_array($result)) { //process the row.......... } • The rows of a result set are represented as associative arrays, with the indices corresponding to the table columns in the result set. If $row is a row in the result set, then $row[‘student_id’] is the value of the student_id for that row.

17. This enables access to individual columns in the result set. • E.g. to output all the student_ids in our students table, we could write code like: while ( $row = mysql_fetch_array($result) ) { echo(‘<p>’. $row[‘student_id’] . ‘</p> Example of displaying all the student_ids in a web page: <html> <head> <title>List of Student Numbers</title> <body> <?php //connect to the database server $conn = mysql_connect(‘localhost’, ‘username’, ‘password’);

18. if (!$conn) // if the connection failed {die(‘<p>Unable to connect to the server</p>’); // display error msg } //otherwise go ahead and select the database if ( !mysql_select_db(‘database_name’) ) //if it can’t find the database { die( ‘<p>Unable to select the database</p>’); // display message } ?> <p>Here are all the students in our database: <p> <?php $result = mysql_query(‘select student_id from students’); if (!$result ) { die(‘<p> Error performing query</p>); } while ($row = mysql_fetch_array($result) ) { echo(‘<p>’ . $row[‘student_id’] . ‘</p>’); ?> </body> </html>

19. Class Assignment: • Find out how to send emails dynamically, from a web page using PHP. • Find out how to generate PDF files with PHP. References: Deitel, Deitel & Nieto Chapter 29 Build your Own Database Driven website Using PHP and MySQL by Kevin Yank. 2nd Ed. chapters 2, 3, 4 and 9 Some Useful PHP web sites: • www.php.net • www.phpworld.com • www.phpbuilder.com