6. PHP and MySQL

What We Will Cover Announcements Please silence all cell phones and pagers Please sit at a computer station and start your computer When class is over, please shut down your computer Elucidations 6.1: Getting Started With PHP 6.1.1: Accessing PHP-Enhanced HTML Documents 6.1.2: PHP Development Process 6.1.3: Importance of Proper Syntax 6.1.4: Embedding PHP in HTML 6.1.5: Comments in PHP 6.1.6: Summary Exercise 6.1 6.2: Using PHP Literals and Variables 6.2.1: Data Types and Literal Values 6.2.2: PHP Variable Names 6.2.3: Creating PHP Variables 6.2.4: Combining Variables and print Statements 6.2.5: Using Arithmetic Operators 6.2.6: Mathematical Functions 6.2.7: Writing Complex Expressions 6.2.8: Summary Exercise 6.2 6.3: A Taste of PHP and MySQL 6.3.1: Connecting to a MySQL Database 6.3.2: Making a Query 6.3.3: Cleaning Up 6.3.4: All Together Now Exercise 6.3 Wrap Up Due Next: A6: PHP Connections (3/22/06) Exercise 6 and Quiz 6 (3/22/06) Elucidations Homework Questions? A5: Load Data and Index Tables (3/15/06) Exercise 5 and Quiz 5 (3/15/06) Do NOT have a USE statement in your dbname.sql file Do NOT have a CREATE DATABASE statement in your dbname.sql file Make sure your tables are in a loadable order. MySQL 4.0.X Compatibility Newer versions of MySQL (4.1.X or later) can dump some extra info However, Most ISPs do not support these versions Thus, you need to remove this extra info before you try to upload these dump files For example: CREATE TABLE IF NOT EXISTS `accounts` ( `AcctID` int(11) NOT NULL auto_increment, `CardType` varchar(20) collate latin1_general_ci NOT NULL default '', `CardNum` varchar(25) collate latin1_general_ci NOT NULL default '', `ExpDate` varchar(15) collate latin1_general_ci NOT NULL default '', PRIMARY KEY (`AcctID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ; In your dbname.sql file you turn in for assignments: Do NOT have: DEFAULT CHARSET=latin1 Do NOT have: COLLATE=latin1_general_ci Do NOT have: collate latin1_general_ci Do NOT have: default CURRENT_TIMESTAMP In addition, some older MySQL versions might object to: ENGINE=MyISAM If you see this issue, then change it to: TYPE=MyISAM Rather than deleting the extra info manually, you can ask phpMyAdmin to supply a database dump compatible with older style MySQL versions On the Export tab under SQL export compatibility, select MYSQL 40. Note that this option only appears if you have a newer MySQL version installed Thus, you only see the option SQL export compatibility if you need to use it To allow you to fix your files, I have extended the due dates for A4: Project Database Design and A5: Load Data and Index Tables to Friday 10/14/05 Quiz Questions? WebCT Questions from last class? ^ top 6.1: Setting Up the PHP Web Application Objectives At the end of the lesson the student will be able to: Describe the development process for PHP Create and run a simple PHP script Use comments in PHP scripts ^ top 6.1.1: Accessing PHP-Enhanced HTML Documents When you access a file with embedded PHP commands several steps occur Many of these steps are invisible to the user User enters query (URL) into client (browser) Browser connects to server and sends a request for a PHP file Web server receives the request, finds the file and reads it PHP scripting engine executes the PHP statements Server sends the results back to the client Results get sent over the Internet Client receives results and displays them to user Accessing a Database In step 4, PHP can access a database PHP has a number of built-in functions for: Connecting to a database Querying a database using SQL Receiving results from a database PHP uses different functions for different database systems Depends on the features of a particular database Many of the differences between functions are minor We will focus on the use of MySQL with PHP ^ top 6.1.2: PHP Development Process Developing PHP-enhanced HTML documents consists of three steps Writing the PHP (and HTML) code Copying the code to the server Accessing the file using a browser This process is repeated many times during development Writing Code You can use any text editor to create PHP code For instance, you can enter the following script in gedit <?php print "A simple script"; ?> Note how the PHP code starts with a "<?php" tag and ends with "?>" Between these tags is a single PHP print statement Copying to the Server On classroom machines, save the PHP file shown above as first.php in the directory: /home/yourLogin/public_html Viewing the File Start a browser and enter the Web address of the file For our first example, the address is: localhost/~yourLogin/first.php Note: do NOT use "Open File" Using Other Web Servers If you have access to a Web server running PHP, you can copy your file to the server using FTP For instance, you can upload to WebHawks with CoreFTP See John Govsky's: How to Upload to Webhawks.org with CoreFTP Lite To access your pages on WebHawks use: http://webhawks.org/~yourusername/ On your home machine, the location to save files depends on your installation ^ top 6.1.3: Importance of Proper Syntax Ideally, you will always write correct code In reality, there are often problems to correct Syntax errors happen when you write one or more PHP statements that are grammatically incorrect in the PHP language For example, notice the syntax of the following print statement: <?php print "The message to print"; ?> The message to print is enclosed in quotation marks If you forget one of the quotation marks, you get a syntax error You would then have code like the following: <?php print "The message to print; ?> When we run this, PHP reports an error Even though the error is on line 2 that PHP incorrectly reports it on line 4 PHP cannot match up the quote marks Sometimes your script does not contain errors on the line reported Realize that PHP can only approximate where the problem is located You need to re-edit the script, republish it and then view it again Several attempts may be needed to correct all the syntax errors Advice: start with something you know works and make small changes at a time Then when errors occur, you know they were due to the most recent changes Common Syntax Problems Use quotation marks, parenthesis and brackets in pairs End most PHP statements with a semicolon (;) Realize that PHP ignores whitespace Extra blank spaces Blank lines Tabs Be careful of case: PHP commands are not case-sensitive PHP variable and function names ARE case-sensitive ^ top 6.1.4: Embedding PHP in HTML Programmers commonly embedd PHP scripts within HTML tags Only the output from PHP gets displayed in the page You can create HTML tags within the PHP as well The following script demonstrates both uses <html> <head> <title>Generating HTML from PHP</title> </head> <body> <h1>Generating HTML from PHP</h1> <?php   print "<p>Generating HTML in PHP statements</p>";   print "</body></html>"; ?> The file must be a PHP file, such as embedded.php The file must have a .php suffix Otherwise, the Web server will not send the file to the scripting engine If the above were saved as embedded.html, you would see very different output ^ top 6.1.5: Comments in PHP Comments allow you to include descriptive text in your script Comments are ignored when the script runs Using comments does not measurably slow down your code Comments should be used in two cases: To describe the purpose of the script To describe particularly tricky lines of code Comments can start with two forward slash marks (//) These last from the slashes to the end of the line For example: <?php // This is a comment ?> You can add comments like these after a code statement For example: <?php print "A simple script"; //Output a line ?> Also, you can create single-line comments using a pound sign (#) <?php print "A simple script"; #Output a line ?> Another eay to make comments is: /* ... */ This style allows multi-line comments, such as: <?php /* This is a multiline comment */ ?> Also, this style allows part of a line to be commented <?php /* here comes the code */ print "A simple script"; ?> ^ top 6.1.6: Summary PHP files are stored on a web server When the user requests a PHP file, the web server asks PHP to answer the request The web server knows when to do this based on the file extension Thus, every PHP file must end in .php You must copy all PHP files to a location accessible by the web server To view the file, you use a browser to access the web server To code PHP, you must use proper syntax The easiest way to code PHP is to add PHP scripts to HTML code PHP allows you to place comments within the code ^ top Exercise 6.1 In this exercise we look at how to create a PHP script file and view it in a web browser. Specifications Use the next 10 minutes to complete the following. Start a text file named exercise6.txt. Prepare the exercise header as described in the HowTo on submitting exercises Label this exercise: Exercise 6.1 Record your answers to the following questions in your exercise6.txt file Save the following file as embedded.php and view the output in a browser. <html> <head> <title>Generating HTML from PHP</title> </head> <body> <h1>Generating HTML from PHP</h1> <?php   print "<p>Generating HTML in PHP statements</p>";   print "</body></html>"; ?> Q1: When you view the source of the browser page, do you see any PHP code? Now save the file as embedded.html and view the output in a browser. Q2: Does the output appear different than previously? View the page source of the browser page. Q3: When you view the source of embedded.html, do you see any PHP code? Q4: How do you explain the difference? Try adding PHP comments to your embedded.php file. Q5: Where in the script are you able to add comments? ^ top 6.2: Using PHP Literals and Variables Objectives At the end of the lesson the student will be able to: Recognize literal data values Store and access data in PHP variables Display data values Perform arithmetic Create complex expressions ^ top 6.2.1: Data Types and Literal Values PHP has four single-valued (a.k.a. scalar) data types A data type tells a computer how to store and interpret the data Literal values are a sequence of characters in a form that you might normally write PHP can recognize literal values for each of the four scalar types General Type Explanation Examples Booleans Logical values true or false true FALSE Integers Numbers without decimal points 123 -987 Floating point Numbers with decimal points 1.23 -0.01 Strings A sequence of characters inside quotes "abc" 'PHP' Check Yourself What is the data type for each of the following? True 0.33 -128 "$3.95" ^ top 6.2.2: PHP Variable Names Variables are used to store and access data in computer memory Variable names provide a label to use when to referring to the data Using variable names, you can perform many operations Assign an initial value: $myAge = 29; Change a value: $myAge = 30; Display a value: print $myAge; The PHP programmer makes up the variable names "Naming is the key to understanding" You must follow certain rules when creating a name Rules for Variable Names Variable names are made up of a sequence of characters The first character of a variable name is always a dollar sign ($) The second character must be a letter or an underscore ( _ ) Any other characters can be a: Letter Digit Underscore ( _ ) Note that names cannot contain any periods or spaces Also note that names cannot be a reserved keyword such as "default" or "var") In addition, note that names are cAsE sEnSiTiVe! $id, $ID, $iD and $Id are all valid, but different, identifiers Check Yourself Which of the following are valid identifiers? $myHello2 $2myHello $My_HeLlO $my hello $a_very_long_variable_name_that_is_hard_to_read $hel-lo Programming Style: Variable Naming Conventions Professional programmers follow naming conventions when creating names Conventions are not required by PHP but make code easier to read One convention is to start variable names with a lowercase letter (after the dollar sign) Another is to use uppercase letters or underbars ('_') as word separators $myVar $my_var Another good practice is to choose names that describe the value that the variable stores For example: $counter is more descriptive than $c ^ top 6.2.3: Creating PHP Variables In PHP, you create a variable by naming it and assigning it a value Place the variable name on the left side of an equal sign (=) Place the value on the right side End with a semicolon (;) This is known as an assignment statement Most PHP statements end with a semicolon (;) $num1 = 5; $num2 = 10; $num1 = $num2; Above example uses two variables: $num1 and $num2 At the end of the three statements, both variables have the value 10 ^ top 6.2.4: Combining Variables and print Statements So far we have used a print statement to output text and HTML tags You can also use a print statement to display a variable's value print $myAge; You can include text with the variable to print $myAge = 29; print "I am $myAge years old."; Executing the above will display I am 29 years old. A More Complicated Example What does the following display? <html> <head> <title>Variable Example</title> </head> <body> <?php   $firstNum = 12;   $secondNum = 356;   $temp = $firstNum;   $firstNum = $secondNum;   $secondNum = $temp;   print ("firstNum = $firstNum <br>           secondNum = $secondNum"); ?> </body> </html> Check yourself. Explanation of Code Lines 1-3 starts the HTML page <html> <head> <title>Variable Example</title> </head> <body> Line 4 starts the PHP script <?php Lines 5-6 create and assign values to two variables $first_num = 12; $second_num = 356; Lines 7-9 exchange the two numbers, using a $temp variable $temp = $firstNum; $firstNum = $secondNum; $secondNum = $temp; Lines 10-11 display the values of the two variables print ("firstNum = $firstNum <br> secondNum = $secondNum"); Lines 12-14 end the PHP script and the HTML page ?> </body> </html> ^ top 6.2.5: Using Arithmetic Operators You can code PHP to calculate arithmetic on integer and floating-point numbers You use special symbols to perform the operations: + and - for addition and subtraction * for multiplication / for division % for modulus (integer remainder) These special symbols are called operators Some examples: 3 + 4 12 - 7 12.3 + 4.56 .065 * 1200 18 / 2 17.4 / 3.2 These combination of numbers and operators are called arithmetic expressions Expressions are parts of a program that return a value We can use these expressions to perform arithmetic and display the results print (3 + 4); // prints 7 Can also use operators with variables: print $myAge + 1; For Example What does the following display? <html> <head> <title>Counting Fruit</title> </head> <body> <?php   $apples = 12;   $oranges = 14;   $totalFruit = $apples + $oranges;   print "The total number of fruit is $totalFruit"; ?> </body> </html> Check yourself. Explanation of Code Relevant part is the PHP code First create two variables and assign them values $apples = 12; $oranges = 14; Next line adds the two variables and assigns the value to a new variable $totalFruit = $apples + $oranges; Print statement displays the value of the $total_fruit variable print "The total number of fruit is $totalFruit"; Showing that with PHP you can add apples and oranges ^ top 6.2.6: Mathematical Functions There are many common mathematical operations that are not provided by operators Instead, PHP provides the Mathematical Functions library Functions include: abs, pow, max, min, rand, round, sqrt, log, and trig functions Also includes common math constants such as: M_PI (approximately 3.141592653) M_E (base of natural logarithms, approximately 2.71828182) If you need to handle big numbers you can use the BCMath Arbitrary Precision Mathematics Functions Arguments Most mathematical functions require you to send them one or more arguments Arguments are input values that functions use in processing Usually mathematical functions return a value based on the arguments Examples $value = pow(2, 3); // value gets int 8 print($value); $num = -2.0; $value = abs($num); // value gets float 2.0 print($value); ^ top 6.2.7: Writing Complex Expressions Can use many operators and functions in the same expression $x = 1 + 2 * 3; What is the value of $x after this statement? The value of $x might be either 9 or 7 The correct answer depends on the order PHP applies the arithmetic operators The computer follows certain rules when evaluating an expression General Expression Rules Two binary operators cannot be placed side by side Invalid expression: 5 * % 6 Operators * and % cannot be next to each other Parenthesis can be used to group expressions Anything within parenthesis is evaluated first You can have parenthesis within parenthesis Innermost parenthesis evaluated first Parenthesis cannot be used to indicate multiplication You must use the * operator Precedence and Associativity Rules Arithmetic operators are processed like in algebra: Parenthesis: ( ) Multiplication, division, modulus: *, /, % Addition, subtraction: +, - Operators with the same precedence are evaluated from left to right Examples of Expressions Algebra Expression PHP Expression 2(10 + 5) 2 * (10 + 5) 1 12.2 + 3 · 7.3 1 / (12.2 + 3 * 7.3) 10 - 7 3.3 + 9 · 1.6 (10 - 7) / (3.3 + 9 * 1.6) 2 · 42 2 * 4 * 4 Using parenthesis to specify and clarify operator precedence is a good idea When in doubt, use parenthesis ^ top 6.2.8: Summary PHP has four single-valued (scalar) data types A data type tells a computer how to store and operate on data General Type Explanation Examples Booleans Logical values true or false true FALSE Integers Numbers without decimal points 123 -987 Float or double Numbers with decimal points 1.23 -0.01 Strings A sequence of characters inside quotes "abc" 'PHP' Variables store a value that can change as a program executes. To create a variable, you give it a name To store a value in a variable, you assign it a value $num = 12; Once you have a value stored in a variable, you can print the value print $num; You can perform arithmetic on numeric values $num = $num + 2; PHP supports all the standard math operations $a = 2; $b = 4; print $a + $b // Addition: Sum of $a and $b print $a - $b // Subtraction: Difference of $a and $b print $a * $b // Multiplication: Product of $a and $b print $a / $b // Division: Quotient of $a and $b print $a % $b // Modulus: Remainder of $a divided by $b When you want to perform more complicated math operations, you use mathematical functions $value = pow($a, $b); print($value); You can use many operators and functions in the same expression $average = ($a + $b) / 2; ^ top Exercise 6.2 Through the miracles of modern computer technology, we will now convert your $1000 computer into a $10 calculator! Specifications Label this exercise: Exercise 6.2 Complete the following and record the answers to questions in your exercise6.txt file. Copy the following script into a text editor and run the script. <html> <head> <title>Counting Fruit</title> </head> <body> <?php   $apples = 12;   $oranges = 14;   $totalFruit = $apples + $oranges;   print "The total number of fruit is $totalFruit"; ?> </body> </html> Replace the $apples + $oranges portion of the script with the arithmetic expression 7 + 2 and rerun the script. Q1: What value was displayed? Use the expression 7 / 2 and rerun the script. Q2: What value was displayed? Why? Change the expression to use 7 % 2 and rerun the script. Q3: What value was displayed? Why? The following are algebraic expressions and incorrect PHP expressions corresponding to them. Find the errors and write corrected PHP expressions. Save the corrected expressions in your exercise file. Algebra Expression Incorrect PHP Expression (2)(3) + (4)(5) (2)(3) + (4)(5) 6 + 18 2 6 + 18 / 2 5 9 (100 - 32) 5 / 9 (100 - 32) 2 · 42 2 * 4 ^ 2 ^ top 6.3: A Taste of PHP and MySQL Objectives At the end of the lesson the student will be able to: Connect to a MySQL database using PHP Retrieve data from MySQL using PHP Disconnect from MySQL ^ top 6.3.1: Connecting to a MySQL Database To get content from MySQL database, your program must establish a connection Two steps to opening a connection to a database: Creating a connection Selecting a database Creating a Connection To make a connection, use the mysql_connect() function $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd); $dbhost is either the IP address or hostname of the computer on which the MySQL server software is running $dbuser is the MySQL user account you use for database queries $dbpwd is the password for that account mysql_connect() function returns a resource representing the connection We save this resource in a variable because we will need to use it later For example: to connect to our local MySQL server: $dbhost = "localhost"; $dbuser = "root"; $dbpwd = ""; $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd); Selecting a Database To select a database, use the mysql_select_db() function mysql_select_db($dbname, $dbCnx); For example: selecting the Artzy database: $dbname = "artzy"; mysql_select_db($dbname, $dbCnx); Note how we used the $dbCnx variable from the connection Isolating the Connection Information Just four connection variables control access to a MySQL database This is sensitive information that needs to be kept confidential Also helps the instructor grade your homework You must keep these variables in a separate file named dbconvars.php <?php $dbhost = "localhost"; $dbuser = "root"; $dbpwd = ""; $dbname = "artzy"; ?> In addition, you save the dbconvars.php file in a subdirectory named includes To include the variables in your code, use a require_once statement require_once("includes/dbconvars.php"); Now you can use the connection variables in your application $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd); mysql_select_db($dbname, $dbCnx); Further Information mysql_connect mysql_select_db ^ top 6.3.2: Making a Query To query a database, use the mysql_query() function like this: $query = "SELECT * FROM Products"; $result = mysql_query($query, $dbCnx); $query is a string that contains the SQL command we want to execute Note that $dbCnx parameter is optional $result = mysql_query($query); If the query is short, you can put it directly in the function call $result = mysql_query("SELECT * FROM Products"); Query results (a.k.a. result set) are returned structured like a table You save this data in a variable like $result You can then access a row and column of the data using the mysql_result() function mysql_result($result, rowNum, colName); Rows are numbered starting at 0 Columns can be accessed by column name or number For example: print mysql_result($result, 0, 1); print mysql_result($result, 0, "ProductName"); print mysql_result($result, 1, "ProductName"); Further Information mysql_query mysql_result ^ top 6.3.3: Cleaning Up After we are done using the MySQL data, we can release the memory used mysql_free_result($resultSet); Similar to opening a connection to MySQL, we can also close a connection mysql_close($connectionID); For example: mysql_free_result($result); mysql_close($dbCnx); Not strictly necessary -- will happen automatically when the page completes processing Further Information mysql_close mysql_free_result ^ top 6.3.4: All Together Now Given the task: Open a connection to Artzy and create a query that returns all product names. We first create a SQL statement for the query: SELECT ProductName FROM Products Then we put the query in the following PHP code: <?php require_once("includes/dbconvars.php"); $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd)     or die("Could not connect: ".mysql_error()); mysql_select_db($dbname, $dbCnx)     or die("Could not select db: ".mysql_error()); $result = mysql_query("SELECT * FROM products")     or die("Query failed: ".mysql_error()); print mysql_result($result, 0, 1); print mysql_result($result, 0, "ProductName"); print mysql_result($result, 1, "ProductName"); mysql_free_result($result); mysql_close($dbCnx); ?> We save the file as query.php and view it in a browser Note use of the or die construct during connections and queries Ends the script when an error occurs Posts your own custom error message Also note the use of mysql_error() Gets the error message from MySQL In addition, make sure you put the dbconvars.php file in a subdirectory named includes <?php $dbhost = "localhost"; $dbuser = "root"; $dbpwd = ""; $dbname = "artzy"; ?> ^ top Exercise 6.3 Instructions: Use the next 10 minutes to complete the following. Label this exercise: Exercise 6.3 Complete the following exercises and answer any questions. Specifications Save the connection information in a file named dbconvars.php. <?php $dbhost = "localhost"; $dbuser = "root"; $dbpwd = ""; $dbname = "artzy"; ?> Run the following script as query.php. <?php require_once("includes/dbconvars.php"); $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd)     or die("Could not connect: ".mysql_error()); mysql_select_db($dbname, $dbCnx)     or die("Could not select db: ".mysql_error()); $result = mysql_query("SELECT * FROM products")     or die("Query failed: ".mysql_error()); print mysql_result($result, 0, 1); print mysql_result($result, 0, "ProductName"); print mysql_result($result, 1, "ProductName"); mysql_free_result($result); mysql_close($dbCnx); ?> Record the answers to the following questions in your exercise6.txt file. Q1: Why does the instructor want you to save database connection variables in includes/dbconvars.php? Q2: It is NOT acceptable to save your database connection variables in a file with a name other than includes/dbconvars.php? Why? Q3: What is the purpose of the or die() clause in the above statements? ^ top Wrap Up Reminders Due Next: A6: PHP Connections (3/22/06) Exercise 6 and Quiz 6 (3/22/06) When class is over, please shut down your computer You may complete unfinished exercises at the end of the class or at any time before the next class. Instructions on submitting exercises are available from the HowTo's page. ^ top Home | WebCT | Announcements | Course info | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: March 15 2006 @17:40:48