Chapter 8 – Working with Databases spring into PHP 5 by Steven Holzner

1. Chapter 8 – Working with Databasesspring into PHP 5by Steven Holzner Slides were developed by Jack DavisCollege of Information Scienceand TechnologyRadford University

2. Supported Databases • Many including Adabas, dBase, Empress,FilePro,Oracle , IBM DB2, Informix, and more. • One of the most popular Databases is MySQL is free and can be downloaded at:http://www.mysql.com. This book's examples are based on MySQL version 4.0. • Later the PHP DB module will be shown. DB provides a layer of abstraction over database operations, leeting the client work with many different database servers using the same function calls. The DB module lets the user access all the database servers using the same function calls, however using native built-in support is much faster • Manuals for PHP support for the various database servers can be found at:http://www.php.net/dbname

3. Basic SQL • To interact with databases in PHP, SQL (Structured Query Language) is used.Assume a table named fruit:SELECT * FROM fruit// selects all records from table fruitIn PHP :$query = "SELECT * FROM fruit";$result = mysql_query($query) or die ("Query Failed: ". mysql_error());// selects specific fieldsSELECT name, number FROM fruit// use where clauseSELECT * FROM fruit WHERE name="apples"// can also use >, >=, <, <= operators

4. Basic SQL (cont) • // use in clauseSELECT * FROM fruit WHERE name IN ("apples","oranges")// can add logical operatorsSELECT * FROM fruit WHERE name NOT IN("apples","oranges") AND number IS NOT NULL//can order a recordset descendingSELECT * FROM fruit ORDER BY name DESC// can delete recordsDELETE FROM fruit WHERE name NOT IN ("apples","oranges")// use UPDATE to change valueUPDATE fruit SET number="2006" WHERE name = "apples"// insert new dataINSERT INTO fruit (name,number) VALUES('apricots', '203')

5. Database Connections in PHP • At RU, the PHP server is set-up with a connection to a continuously running MySQL database server • To connect to a database use mysql_connect command and provide the host, username, and password$host = 'localhost';$user = 'jcdavis'; $pass = 'abcdef'; // your database password$connection = mysql_connect($host,$user,$pass) or die ("Couldn't connect to dbase");// now connect to the specific database// at RU your database name is the same// as your username$db = mysql_select_db($user);// now you can execute queries with// mysql_query mysql_close

6. Set Up Your MySQL Database at RU • https://php.radford.edu • go to this URL for informationhttps://php.radford.edu/~mysql-php/ • use the MySQL admin tool to set up a table called fruits (use lower case field names)namenumberapples 501oranges 255pears 299

7. Other Data Types? • MySQL supports many other data types beyond TEXT and INT. Here are a few : • TEXT specifies that the table column can hold a large amount of character data. It can use space inefficiently since it reserves space for up to 65,535 characters. • CHAR(N) specifies a table column that holds a fixed length string of up to N characters (N must be less than 256). • VARCAR(N) specifies a table column that holds a variable length string of up to N characters and removes any unused spaces on the end of the entry.

8. Other Data Types? • INT specifies a table column that holds an integer with a value from about –2 billion to about 2 billion. • INT UNSIGNED specifies a table column that holds an integer with a value from 0 to about 4 billion. • SMALLINT specifies a table column that holds an integer with a value from –32,768 to 32,767. • SMALLINT UNSIGNED specifies a table column that holds an integer with a value from 0 to 65,535. • DECIMAL(N,D) specifies a number that supports N total digits, of which D digits are to the right of the decimal point.

9. Creating Database Tables • Once database instance is created need to create your tables. • Use SQL CREATE TABLE command

10. Some additional CREATE TABLE Options • Can specify some additional options in CREATE TABLE:

11. Issuing CREATE TABLE From PHP Script Segment 1. $connect = mysql_connect($server, $user, $pass); 2. if ( !$connect ) { 3. die ("Cannot connect to $server using $user"); 4. } else { 5. mysql_select_db('MyDatabaseName'); 6. $SQLcmd = 'CREATE TABLE Products( ProductID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Product_desc VARCHAR(50), Cost INT, Weight INT, Numb INT )'; 7. mysql_query($SQLcmd, $connect); 8. mysql_close($connect); 9. } Connect to MySQL Issue the SQL query to the database.

12. Inserting Data • Once database is created will need to insert data • Use the SQL INSERT command

13. Retrieving Data • To retrieve all data, use following SQL command • For example$connect = mysql_connect('Localhost', 'phpgm','mypasswd');$SQLcmd = 'SELECT * FROM Products';mysql_select_db('MyDatabase');$results_id = mysql_query($SALcmd, $connect

14. Using mysql_fetch_array • The mysql_fetch_array function can be used to get successive rows from a table in a while loop and get each field value from each row (record).Assume the fruits table has two fields name and number, like the following.apples 1020oranges 3329bananas 442pears 235$conn = mysql_connection($host, $user, $pass);$db = mysql_select_db($user, $conn);// remember at RU username = dbname$query = "SELECT * FROM fruit";$result = mysql_query($query);echo "<table border=\”5\” >";echo "<tr>";echo "<th>Name</th><th>Number</th>";echo "</tr>";

15. mysql_fetch_array (cont.) • while ($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>"; echo "$row[name]"; echo "</td>"; echo "<td>"; echo "$row[number]"; echo "</td>"; echo "</tr>"; }echo "</table>";mysql_close($conn);

16. Searching For Specific Records • Use the SELECT SQL statement with a WHERE clause SELECT * FROM TableName WHERE (test_expression); Specify a test expression to evaluate Specify the table name to look at. The asterisk (“*”) means look at all table columns.

17. Selected WHERE CLAUSE Test Operators

18. Update, Delete Example • sample update query$query = "UPDATE fruit SET number = 234 WHERE name = 'pears'";$result = mysql_query($query) or die ("Query Failed"); • sample delete query$query = "DELETE * FROM fruit WHERE name = 'apples' ";$result = mysql_query($query) or die(); • sample insert$query = "INSERT INTO fruit (name, number) VALUES('grapes','200')";

19. Pear DB Module • PHP supports the DB module, which gives you a level of abstraction that smoothes over the details of working with different database servers. When you use DB, you can use the same functions to access your data; if you switch database server types, you just have to change the name of the database server you're using.DB is a PHP extension managed by PEAR, the PHP Extension and Application Repository. The PEAR module has to be installed when you configure your PHP installation on the Apache server.

20. PEAR DB Example • Example of using the DB module's functions to read a MySQL database// at the top of the PHP require 'DB.php';// connect using the DB connect method$db = DB::connect('dbname://username: password@server/databasename);$query = "SELECT * FROM fruit";$result = $db->query($query);// you can recover a row of data using the fetchRow method.

21. Oracle DB connection https://php.radford.edu/~jcdavis/it325examples/oracleConnectExample4.php