6. PHP and MySQL

What We Will Cover

Elucidations

Homework Questions?

Quiz Questions?

Questions from last class?

^ top

6.1: Getting Started With PHP

Learner Outcomes

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: Creating PHP-Enhanced Web Pages

  • PHP is a scripting language that runs on a web server
  • A scripting language is a programming language that is embedded in another application, such as a web server
  • You usually write PHP code by embedding the commands in a web page
  • However, you can write PHP code in a separate file that is then included in a web page

Creating a PHP file

  • You create a PHP file like you create an HTML file
  • However, the file must end with the extension .php rather than .html or .htm
  • The .php extensions tells the web server to run the file as a PHP script rather than an HTML document

Embedding PHP Commands in HTML

  • Programmers commonly embed PHP scripts within the tags of a standard HTML page
  • The PHP source code is surrounded by a pair of special PHP tags
  • Syntax: 
    <?php PHP source code ?>
  • For example:

    <!DOCTYPE HTML PUBLIC
      "-//W3C//DTD HTML 4.01 Transitional//EN"
      "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type"
      content="text/html; charset=iso-8859-1">
    <title>Simple PHP Script</title>
    </head>

    <body>
    <?php print "Hello PHP World!" ?>
    </body>
    </html>

  • Click to see the page and then view the page source
  • When you view the page source, notice that you cannot see the PHP tags
  • We will look at the reason you do not see the PHP tags in the following section

^ top

6.1.2: 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

PHP page processing

  1. User enters query (URL) into client (browser)
  2. Browser connects to server and sends a request for a PHP file
  3. Web server receives the request, finds the file and sends it to the PHP engine
  4. PHP engine executes the PHP statements, returning a standard Web document to the server
  5. Server sends the results back to the client
  6. Results get sent over the Internet
  7. 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

    PHP-MySQL connection

^ top

6.1.3: PHP Development Process

  • Developing PHP-enhanced HTML documents consists of three steps
    1. Writing the PHP (and HTML) code
    2. Copying the code to the server
    3. Accessing the file using a browser
  • This process is repeated many times during development

Writing Code

  • You use a text editor to create PHP code
  • For instance, you can enter the following script in TextPad 
    <?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
  • The print statement says to display whatever follows the word print to a Web page
  • In this case we are displaying the string, "A simple script"

Copying to the Server

  • On classroom machines, save the PHP file shown above as first.php in the directory: C:\xampp\htdocs\

Viewing the File

  • Start a browser and enter the Web address of the file
  • For our first example, the address is: http://localhost/first.php
  • Note that you cannot use your browser's "Open file" menu to view the page

Using Other Web Servers

  • If you have access to a Web server running PHP, you can copy your file to the server using FTP
  • One such server is WebHawks, which is maintained for Cabrillo students at webhawks.org
  • To access your pages on WebHawks see my instructions: How To Use WebHawks
  • On your home machine, the location to save files depends on your installation

^ 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

  1. Open the XAMPP Control Panel by double-clicking the icon on the desktop or by using the Apache Friends entry in the start menu.

    XAMPP icon

  2. Start the Apache and MySQL modules, if they are not already running.

    XAMPP control panel

  3. Start TextPad, or another text editor, and enter this code:

    <!DOCTYPE HTML PUBLIC
      "-//W3C//DTD HTML 4.01 Transitional//EN"
      "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type"
      content="text/html; charset=iso-8859-1">
    <title>Simple PHP Script</title>
    </head>

    <body>
    <?php print "Hello PHP World!" ?>
    </body>
    </html>

  4. Save this code in a file named hello.php to the htdocs directory of your Apache (XAMPP) installation.

    If you installed XAMPP on Windows using the default settings, this directory is C:\xampp\htdocs. Also, the Start menu has an entry that opens the htdocs directory by following Start -> Apache Friends -> XAMPP -> XAMPP httpdoc folder. On other systems you will need to search for the htdocs directory.

  5. To view the file as a web page, open a new Web browser tab or window and enter localhost/hello.php in the address field.

    You should see a page like the following in the browser:

  6. View the page source, using the View Source option of the browser, and verify you do not see any PHP tags.

    If you see the PHP tags <?php and ?> then the Web server is not processing the PHP page. Usually this happens when you load the file directly into the browser rather than using localhost or if you have the wrong file extension. Make sure you have the correct page source before proceeding. If viewing the page source in a Web browser still shows the PHP tags, then get help from the instructor or a classmate.

  7. Now that we can view a page correctly, we will look at what you see when there is a problem. Save the same file as hello.html and view the page in a browser. In addition, view the page source, using the View Source option of the browser, and notice that you now see PHP tags.

    If you can see PHP tags using the View Source option of the browser, then the Web server is not sending the PHP code to the PHP engine for processing. One way this error occurs is if you use the wrong file extension. For more information see section 6.1.2: Accessing PHP-Enhanced HTML Documents.

  8. Reload your hello.php file into your text editor and view the file in your Web browser. View the page source, using the View Source option of the browser, and verify you do not see any PHP tags.
  9. After verifying your page is working, remove the closing double quote mark (") so that the line of PHP code looks like: 
    <?php print "Hello PHP World! ?>

    Reload the page for hello.php in your Web browser and you should see an error message like:

    Parse error: syntax error, unexpected $end in C:\xampp\htdocs\hello.php on line 15

    Programming languages, like PHP, follow strict rules on the arrangement of words in commands. This arrangement is called syntax. For more information see section 6.1.4: Importance of Proper Syntax.

  10. Correct the error we created in the previous step and verify it is correct by viewing the page in your Web browser.
  11. Let us add a paragraph element around the message, "Hello PHP World!". Change the PHP code of your hello.php file so that the line of PHP code looks like: 
    <?php print "<p>Hello PHP World!</p>" ?>

    Notice that we placed the paragraph tags inside the double-quote marks ("). This is known as, "embedding HTML in PHP". For more information see section 6.1.5: Embedding HTML in PHP.

  12. Reload (refresh) the hello.php page in your Web browser and view the page source, using the View Source option of the browser.

    Notice that the paragraph tags <p> and </p> are now part of the page source even though the PHP tags are not present. Anything between the quote marks following the command print is displayed in the Web page.

  13. Let us add a comment to our script. Change the PHP code of your hello.php file so that the line of PHP code looks like: 
    <?php
/*
    Comment one
*/
// Comment two
print "<p>Hello PHP World!</p>" # Comment 3
?>

    Notice the three different styles of comments. For more information see section 6.1.6: Comments in PHP.

  14. Reload (refresh) the hello.php page in your Web browser and view the page source, using the View Source option of the browser.

    Notice that the comments do NOT appear in the page source. Comments are notes to people who read your code, like the instructor, and are ignored by the PHP interpreter. For more information see section 6.1.6: Comments in PHP.

  15. Submit your final hello.php file to Blackboard as part of assignment 6.

Check Yourself

As time permits, be prepared to answer these questions. You can find more information by following the links after the question.

  1. What extension must you add to a PHP file name? (6.1.1)
  2. Which of the following executes PHP code? (6.1.2)
    1. Web server
    2. PHP Engine
    3. Web browser
    4. Central processor at php.net
  3. What tags are used to enclose PHP code? (6.1.3)
  4. Why cannot you use a browser's "Open file" command to view PHP pages? (6.1.3)
  5. How do you insert PHP scripts into a Web page? (6.1.3)
  6. What PHP command displays information in a Web page? (6.1.3)
  7. What is wrong with the following PHP script? (6.1.4) 
    <?php
  print "The message to print;
?>
  8. What is meant by, "embedding HTML in PHP"? (6.1.5)
  9. What is the purpose of a comment? (6.1.6)
  10. What are the three ways to create comments in PHP? (6.1.6)

^ top

6.1.4: Importance of Proper Syntax

  • In programming terms, syntax is the arrangement of words for a programming language
  • Ideally, you would always write PHP scripts with correct syntax
  • In reality, there are often problems to correct
  • Syntax errors happen when you write one or more PHP statements that are "grammatically" incorrect
  • 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.5: Embedding HTML in PHP

  • Programmers commonly embed PHP scripts within HTML tags
  • PHP code does not get displayed; only the output from print statements is displayed
  • However, you can create HTML tags within PHP scripts
  • This is done by creating HTML tags using print statements
  • The following script demonstrates this approach
  • Note that 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 a very different page

Creating HTML From PHP

<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>";
?>

^ top

6.1.6: Comments in PHP

  • Comments are notes to people who read your scripts
  • Comments are ignored when the script runs
    • Using comments does not measurably slow down your programs
  • Comments should be used in two cases:
    1. To describe the purpose of the script
    2. 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 statement as well
  • 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 way 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.7: Summary

  • PHP is a scripting language that runs on a Web server
  • You create a PHP file like you create a an HTML file
  • However, the file must end with the extension .php rather than .html or .htm
  • Programmers commonly embed PHP scripts within the tags of a standard HTML page
  • The PHP source code is surrounded by a pair of special PHP tags
  • Syntax: 
    <?php PHP source code ?>
  • 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

6.2: PHP Language Basics

Learner Outcomes

At the end of the lesson the student will be able to:

  • Declare a variable and assign it a value
  • Store and access data in PHP variables
  • Recognize literal data values
  • Display variable data and literal values in a Web page
  • Perform arithmetic using PHP operators and functions
  • Create complex arithmetic expressions

^ top

6.2.1: Creating Variables

  • In real life, almost everything we do requires memory
  • Even a simple task like adding two numbers together requires the use of memory
  • We need to remember the numbers we add and the result of the addition
  • Just like in real life, it is hard to do anything in a computer without memory
  • A variable is how you store information in a computer's memory
  • In this location you can store numbers and text strings
  • However, you can only store one value in each variable
  • Values stored in a variable can change (vary) as the program runs
  • You create a variable in PHP by typing a dollar sign followed by the name of the variable
  • For example, for the variable named myVar, you type: 
    $myVar
  • There are certain rules you must follow when creating a variable name

Rules for Variable Names

  • A variable name is a sequence of letters, numbers, or underscore characters
  • However, PHP has some limitations on variable names
  • Specifically, the first character (after the dollar sign) must be either a letter or an underscore character ( _ )
    • Cannot be a number
  • Unlike many languages, variable names can be a reserved keyword but you should avoid them
  • Note that you cannot have spaces in a variable name
  • A space is NOT a letter, number or underscore character
  • In addition, note that names are cAsE sEnSiTiVe!
  • $id, $ID, $iD and $Id are all valid, but different, identifiers

Programming Style: Variable Naming Conventions

  • Professional programmers follow naming conventions when creating names
  • The 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

Check Yourself

Which of the following are valid identifiers?

  1. $myHello2
  2. $2myHello
  3. $My_HeLlO
  4. $my hello
  5. $a_very_long_variable_name_that_is_hard_to_read
  6. $hel-lo

^ top

6.2.2: Assigning a Value to a Variable

  • To assign a value to a variable you use an equal (=) sign
  • The syntax is: 
    $variableName = value;
  • For example: 
    $userName = "eparrish";
  • Note that assignment statements, like most PHP statements, end in a semicolon (;)
  • Notice the way that an assignment statement works
  • The right-hand side of the equals sign is always assigned to the variable on the left-hand side
  • You cannot reverse the sides

^ top

6.2.3: Writing a Variable Value to a Web Document

  • You can use a variable in place of the value it contains
  • For instance, to write a text string to a web page: 
    $name = "Edward Parrish";
print $name;
  • You might assign a value to a variable like this because you want to use it in several places on your page
  • By using the $name variable, you will not accidentally misspell "Edward Parrish" somewhere in the page
  • Note that an equivalent PHP command to write to a Web page is the keyword echo
  • Thus another way to express the above examples is: 
    $name = "Edward Parrish";
echo $name;
  • You can use either print or echo interchangably in most situations
  • For a discussion of the differences see: What is the difference between echo and print?

Combining Text Strings

  • PHP lets you combine a variable with a text string inside of double quotes
  • For example:

    <?php
      $name     = "Edward Parrish";
      print     "<p>Welcome to the page of $name</p>";
    ?>

  • You can view the page by clicking here
  • While you can use single quotes for text strings, single-quoted strings do not perform this substitution
  • You can combine multiple text variables and strings using the dot (.) operator
  • For example:

    <?php
      $userName     = "eparrish"; // using double quotes
          $emServer = 'nowhere.com'; // using single quotes
          $email = $userName . "@" . $emServer;
    ?>
    <p>The email address is: <?php print $email; ?>.</p>

  • You can view this page by clicking here
  • Notice that we defined the value of $email in one set of PHP tags and then used it in another set of PHP tags
  • Once you define a variable you can use it anywhere in your page

^ top

Exercise 6.2

In this exercise, we explore how to use variables and use the variables in arithmetic and with mathematical functions.

Specifications

  1. Copy the following PHP script into a text editor, save the file in your htdocs directory as variables.php, and then view the file as a Web page in your browser. 
    <html>
<head><title>PHP Script</title></head>
<body>
<?php
    // Enter your PHP code here

?>
</body>
</html>
  2. Between the PHP tags, declare a variable named $apples and assign the variable a value of 12: 
    $apples = 12;

    To assign a value to a variable, we use an equals sign (=) as the assignment operator. For more information see section 6.2.2: Assigning a Value to a Variable.

  3. After the variable declaration, add a print statement to display the number of apples and then reload (refresh) the Web variables.php page in your browser to view the results: 
    print "The number of apples is $apples.";

    Notice that the variable inside the double quote marks (") displays the value 12 rather than the name of the variable $apples. For more information see section 6.2.3: Writing a Variable Value to a Web Document.

  4. After the other PHP commands, declare a second variable named $oranges and assign the variable a value of "14" but with double quotes around the number like this: 
    $oranges = "14";

    The addition of the double quote marks (") makes the variable a string data type rather than an integer data type. For more information see section 6.2.4: Data Types and Literal Values.

  5. After the variable declaration, add a print statement to display the variable and then reload (refresh) the Web page in your browser to view the results: 
    print "<br>The number of oranges is $oranges.";

    Even though the data type of the variable $oranges is a string, printing the variable inside the double quote marks (") displays the value 14 rather than the name of the variable $oranges. For more information see section 6.2.3: Writing a Variable Value to a Web Document.

  6. Now we want to sum (add) the two variables. After the other PHP commands, declare another variable named totalFruit and assign the variable the sum of apples and oranges like this: 
    $totalFruit = $apples + $oranges;

    In strongly-typed languages, like C++ or Java, this statement would produce an error. However, a weakly-typed language like PHP will automatically convert a string to a number when needed. For more information see section 6.2.4: Data Types and Literal Values.

  7. After the other PHP commands, add a print statement to display the $totalFruit variable and then reload (refresh) the Web page in your browser to view the results: 
    print "<br>The total number of fruit is $totalFruit";

    Proving with PHP you can add apples and oranges. For more information see section 6.2.5: Using Arithmetic Operators.

  8. After the other PHP commands, add the print statement shown below and then reload (refresh) the Web page in your browser to view the results: 
    print "<br>The sum is $apples + $oranges";

    You can perform arithmetic in a print statement, but not inside double quote marks. Instead, you must perform the arithmetic outside the double quotes as we will see in the next step.

  9. PHP supports the usual five operators for basic arithmetic (+, -, * / and %). After the other PHP commands, add the following print statements: 
    print "<br>Addition: ".($apples + $oranges);
print "<br>Subtraction: ".($apples - $oranges);
print "<br>Multiplication: ".($apples * $oranges);
print "<br>Division: ".($apples / $oranges);
print "<br>Modulus: ".($apples % $oranges);

    Notice that we are joining the string with the arithmetic using a dot (.). This is known as concatenation. In this case, PHP is taking the number after performing the arithmetic operation in the parenthesis and then converting the number to a string. For more information see section 6.2.4: Data Types and Literal Values.

  10. After the other PHP commands, add the following print statements to display more complex operations and then reload (refresh) the Web page in your browser to view the results: 
    print "<br>No parenthesis: ".($apples + $oranges / 2);
print "<br>Parenthesis: ".(($apples + $oranges) / 2);

    As in algebra, PHP performs multiplication and division before addition and subtraction. To change the order of operations, you use parenthesis. For more information see section 6.2.6: Writing Complex Expressions.

  11. To perform more advanced mathematical operations, you must use functions. After the other PHP commands, add the following print statements: 
    print "<br>sqrt($totalFruit): ".sqrt($totalFruit);

    In this statement, we are taking the square root of the value in the variable $totalFruit. For more information see section 6.2.7: Mathematical Functions.

  12. Submit your final PHP script file to Blackboard as part of assignment 6.

Check Yourself

As time permits, be prepared to answer these questions. You can find more information by following the links after the question.

  1. Why do you need to store data when creating a program? (6.2.1)
  2. How do you store data in a program? (6.2.1)
  3. Which of the following are valid identifiers? (6.2.1)
    1. $myHello2
    2. $2myHello
    3. $My_HeLlO
    4. $my hello
    5. $a_very_long_variable_name_that_is_hard_to_read
    6. $hel-lo
  4. How do you assign a value to a variable? (6.2.2)
  5. What code do you write to declare a variable named foo and assign it a value of 10? (6.2.2)
  6. What code do you use to declare a variable named weekDay and assign it a value of "Friday"? (6.2.2)
  7. What code do you write to print the value of $foo to a Web page? (6.2.3)
  8. What are the four data types supported by PHP? (6.2.4)
  9. True or false? PHP will automatically convert a string to a number to perform an arithmetic operation. (6.2.4)
  10. What five arithmetic operators does PHP support and what do they do? (6.2.5)
  11. What is the first operation performed in the expression: 1 + 2 * 3 / 4 % 5? (6.2.6)
  12. What code do you write to calculate the square root of the number 49? (6.2.7)

^ top

6.2.4: 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 data types
  • You can see the data types and examples of literal values in the following table
  • Later in the course we will look at more complex aggregate data types

PHP Scalar Data 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?

  1. True
  2. 0.33
  3. -128
  4. "$3.95"

Mixing Strings and Numbers

  • Unlike numeric variables, you cannot add, subtract, multiply or divide strings
  • Thus, must be careful when mixing string and numeric expressions
  • What, if anything, is displayed by the following PHP code? 
    $fruit = "apple"
$sum = $fruit + 1;
print "Sum = $sum";
  • As an experienced programmer, you may expect an error message
  • Instead, PHP will display the output: 
    Sum = 1
  • This is because PHP automatically converts strings to numbers
  • If the string is not in a number format, the result is returned as 0
  • As another example, what is displayed by the following?

    <?php
        $num     = "3.14159" + 1;
        print     $num;
    ?>

    Try it!.

^ top

6.2.5: Using Arithmetic Operators

  • You can code PHP to calculate arithmetic on integer and floating-point numbers
  • You use special symbols, called operators, to perform the operations:
    • + and - for addition and subtraction
    • * for multiplication
    • / for division
    • % for modulus (integer remainder)
  • 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 a part of a statement that return a value
  • We can use these expressions to perform arithmetic and display the results 
    print (3 + 4); // prints 7
  • Which part of the above code is the expression? (answer)
  • We can use operators with variables as well: 
    $apples = 6;
print $apples + 1;
  • To check your understanding, work out what the page displays in the following example

Arithmetic Example

<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

  • The relevant part is the PHP code
  • First we create two variables and assign them values 
    $apples = 12;
$oranges = 14;
  • The next line adds the two variables and assigns the value to a new variable 
    $totalFruit = $apples + $oranges;
  • Then the 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: Writing Complex Expressions

  • You 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
  • PHP follows certain rules when evaluating such an expression

Parenthesis and Precedence

  • Some arithmetic operators act before others (e.g., multiplication before addition)
  • PHP follows rules similar to real-number algebra
  • Arithmetic operations are processed in algebraic order:
    1. Unary operations: +, -
    2. Multiplication, division, modulus: *, /, %
    3. Addition, subtraction: +, -
  • Binary operators of same precedence are evaluated from left to right
  • To change the usual order, you can use parenthesis to group expressions
  • Anything within parenthesis is evaluated first
  • For example, to find the average of three variables a, b and c
    • Do not use: $a + $b + $c / 3
    • Instead use: ($a + $b + $c ) / 3
  • You can have parenthesis within parenthesis
    • The innermost parenthesis is evaluated first 
      (2 * (10 + 5))

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

^ top

6.2.7: Mathematical Functions

  • A function is a collection of commands that perform an action or return a value
  • 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.8: Summary

  • In this section, we covered the basics of programming with PHP
  • The basics included how to create and use variables
  • A variable is a location in the computer's memory where you can store a single value
  • You create a variable in PHP by typing a dollar sign followed by the name of the variable
  • For example, for the variable named myVar, you type: 
    $myVar
  • You make up a name for each variable following the naming rules of PHP
  • To assign a value to a variable, you use the syntax: 
    $variableName = value;
  • For instance: 
    $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);
  • When you use many operators and functions in the same expression they are evaluated in algebraic order:
    1. Unary operations: +, -
    2. Multiplication, division, modulus: *, /, %
    3. Addition, subtraction: +, -

Quick Quiz

  1. PHP requires you to declare variables before you use them.


  2. By setting the variable named "$name" to true, you have declared the variable type to be a type.
  3. Which of the variable declarations below passes a text value to the variable text1?


^ top

6.3: A Taste of PHP and MySQL

Learner Outcomes

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

  • With variables and functions, we can use PHP to access a database
  • To get content from MySQL database, your program must first establish a connection
  • After making the connection, you can execute SQL queries on your database
  • You open the connection in two steps:
    1. Create a connection
    2. Select a database

Creating a Connection

  • To make a connection, you use the mysql_connect() function
  • Syntax: 
    $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
  • The mysql_connect() function returns a resource referring to the connection
  • We save this resource in a variable because we will need to use it later
  • For example: 
    $dbhost = "localhost";
$dbuser = "root";
$dbpwd = "";
$dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd);

Selecting a Database

  • To select a database, you use the mysql_select_db() function
  • Syntax: 
    mysql_select_db($dbname, $dbCnx);
  • Where $dbName is the name of the database and $dbCnx is the connection resource
  • For example, to select the Artzy database: 
    $dbname = "artzy";
mysql_select_db($dbname, $dbCnx);
  • Note how we used the $dbCnx variable from the connection

Isolating the Connection Information

  • Notice that just four connection variables control access to a MySQL database
    • This is sensitive information that needs to be kept confidential
    • Keeping it separate makes it easier to move your application to a different server
    • Also helps the instructor grade your homework
  • For this course, you must keep these variables in a separate file named dbconvars.php like:

    <?php
    $dbhost     = "localhost";
    $dbuser = "root";
    $dbpwd = "";
    $dbname = "artzy";
    ?>

  • The variable names must match these exactly but the connection values may vary
  • In addition, you must save the dbconvars.php file in a subdirectory of your page named includes
  • To include the variables in your code, use a require_once statement 
    require_once "includes/dbconvars.php";
  • Including the file means that these variables loaded into your application
  • Now you use the connection variables without having to redeclare them: 
    $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd);
mysql_select_db($dbname, $dbCnx);

Common Pitfall

  • A common mistake is to redeclare the connection variables in your page
  • For example, DO NOT COMMIT THE FOLLOWING ERROR: 
    require_once "includes/dbconvars.php";
$dbhost = "localhost";     // NO!
$dbuser = "mylogin";       // NO!
$dbpwd = "secretpassword"; // NO!
$dbname = "mydb";          // NO!
  • PHP will not warn you when you redeclare variables and your application will work on your computer
  • However, your page will not work on another computer, like the instructor's, and you will get a low score

More Information

^ 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 variable that contains the SQL command you 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 (known as the result set) are returned structured like a table
  • You save this data in a variable like $result
  • Then you can access a cell (row and column) of the table using the mysql_result() function 
    mysql_result($result, rowNum, colName);
  • Where rowNum and colName are row and column identifiers
  • Rows and columns are numbered starting at 0
  • Columns can be accessed by column name or number
  • For instance: 
    print mysql_result($result, 0, 1);
print mysql_result($result, 0, "ProductName");
print mysql_result($result, 1, "ProductName");

More Information

^ top

6.3.3: Cleaning Up

  • After we are done using the MySQL data, we can release the memory used for the resource 
    mysql_free_result($resultSet);
  • Similar to opening a connection to MySQL, we also close a connection 
    mysql_close($connectionID);
  • For example: 
    mysql_free_result($result);
mysql_close($dbCnx);
  • Not strictly necessary -- automatically happens when the page completes processing
  • However, it is a good idea to free the result before initiating another query whenever possible

More Information

^ top

6.3.4: Creating HTML Tables

  • You can use HTML tables to neatly format data in rows and columns
  • You mark a table with the two-sided <table> tag
  • You add rows to your table by nesting the <tr> tags
  • Then you nest <td> tags to add the table data as cells or columns
  • Each row can have one or more table-data tags
  • For table headings, you use the <th> tag instead of <td>
  • The following code shows an example table

HTML Code for an Example Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<table border="1" cellspacing="0" cellpadding="5">
<tr>
  <th>Heading 1</th>
  <th>Heading 2</th>
</tr>
<tr>
  <td>row 1, column 1</td>
  <td>row 1, column 2</td>
</tr>
<tr>
  <td>row 2, column 1</td>
  <td>row 2, column 2</td>
</tr>
</table>

Display of the Example Table

Heading 1 Heading 2
row 1, column 1 row 1, column 2
row 2, column 1 row 2, column 2

Other Table Options

  • You can create a table border using the attribute border 
    <table border="value"> ... </table>
  • To change cell spacing you use: 
    <table cellspacing="value"> ... </table>
  • Cell padding is the space between content of each table cell and the cell's border
  • Tables have many other options as well
  • While tables are no longer recommended for page layout, they are still useful for displaying data in table form
  • Note that you can print PHP variables in a table cell
  • For example: 
    <td><?php print $name1 ?></td>

More Information

^ top

6.3.5: All Together Now

  • Given the task:

    Open a connection to Artzy and create a query that returns some product names.

  • We first create a SQL statement for the query: 
    SELECT * FROM Products
  • Then we put the query in the following PHP code:

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
       "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head><title>PHP Query</title></head>
    <body>
    <?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());

    $name1 = mysql_result($result, 0, 1);
    $name2 = mysql_result($result, 0, "ProductName");
    $name3 = mysql_result($result, 1, "ProductName");

    mysql_free_result($result);
    mysql_close($dbCnx);
    ?>
    <table border="1">
    <tr>
    <td><?php print $name1 ?></td>
    <td><?php print $name2 ?></td>
    <td><?php print $name3 ?></td>
    </tr>
    </table>
    </body>
    </html>

  • We save the file as query.php and view it in a browser
  • Also, you can view the results by clicking here
  • In addition, we make sure to put the dbconvars.php file in a subdirectory named includes

    <?php
    $dbhost     = "localhost";
    $dbuser = "root";
    $dbpwd = "";
    $dbname = "artzy";
    ?>

Do or Die

  • Note use of the "or die()" construct during connections and queries
  • This structure cause the script to print a message and exit if an error occurs
  • Adding this code lets you create your own custom error messages
  • Also note the use of mysql_error()
  • The mysql_error() function gets the error message created by MySQL
  • This error message is often useful for debugging problems with your scripts

More Information

^ top

6.3.6: Summary

  • In this section we looked at how to extract data from a MySQL database using PHP
  • First we created a connection and selected a database: 
    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());
  • Note that we used the included dbconvars.php file for connection variables
  • Then we created a query and got the results from the database: 
    $result = mysql_query("SELECT * FROM products")
    or die("Query failed: ".mysql_error());
  • With the results, we were able to extract data from the result set 
    $name1 = mysql_result($result, 0, 1);
$name2 = mysql_result($result, 0, "ProductName");
$name3 = mysql_result($result, 1, "ProductName");
  • Being tidy, we cleaned up after our query to free up server resources 
    mysql_free_result($result);
mysql_close($dbCnx);

^ top

Exercise 6.3

Instructions:

In this exercise we extract data from a MySQL database. If you have difficulty, ask another student or the instructor for help.

Specifications

  1. Save the following connection information in a file named dbconvars.php in a subdirectory named includes of the htdocs directory: 
    <?php
$dbhost = "localhost";
$dbuser = "root";
$dbpwd = "";
$dbname = "artzy";
?>

    Change the values assigned to $dbuser and $dbpwd to match your MySQL installation. In the classroom, $dbuser = root; and $dbpwd = cis165;. For more information see section 6.3.1: Connecting to a MySQL Database.

  2. Copy the following PHP script into a text editor, save the file in your htdocs directory as query.php, and then view the file as a Web page in your browser. 
    <html>
<head><title>PHP Script</title></head>
<body>
<?php
    // Enter your PHP code here

?>
</body>
</html>
  3. Between the PHP tags, add the following statement to include the variables from dbconvars.php file and then reload (refresh) the Web page in your browser to verify you have no errors so far: 
    require_once "includes/dbconvars.php";

    We isolate this connection information so the instructor can grade your assignments using his MySQL database. For more information see section 6.3.1: Connecting to a MySQL Database.

  4. After the other PHP commands, create a connection to the database using the following statement and then reload (refresh) the Web page in your browser to verify you have no errors so far: 
    $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd)
    or die("Could not connect: ".mysql_error());

    The mysql_connect() function takes three arguments and establishes a connection to the MySQL database server. For more information see section 6.3.1: Connecting to a MySQL Database.

  5. After the other PHP commands, select the artzy database using the following statement and then reload (refresh) the Web page in your browser to verify you have no errors so far: 
    mysql_select_db($dbname, $dbCnx)
    or die("Could not select db: ".mysql_error());

    The mysql_select_db() function takes two arguments and selects a database on the MySQL server. For more information see section 6.3.1: Connecting to a MySQL Database.

  6. Once we have established a connection and selected a database, we can make a query on the database. After the other PHP commands, make a query on the database using the following statement and then reload (refresh) the Web page in your browser to verify you have no errors so far: 
    $result = mysql_query("SELECT * FROM products")
    or die("Query failed: ".mysql_error());

    The results of the query are stored in the variable $result. We will extract data from the $result variable in the next step. For more information see section 6.3.2: Making a Query.

  7. Now we need to extract data from the $result variable. After the other PHP commands, add the following statements and then reload (refresh) the Web page in your browser to view the data: 
    print mysql_result($result, 0, 1);
print mysql_result($result, 0, "ProductName");
print mysql_result($result, 1, "ProductName");

    The mysql_result() function extracts a specific row and column from a result set like $result. You can specify the column using either the column number or the column name produced by the query. For more information see section 6.3.2: Making a Query.

  8. After we are done with the results set, we should release the memory for other uses. After the other PHP commands, add the following statement and then reload (refresh) the Web page in your browser to verify you have no errors: 
    mysql_free_result($result);

    For more information see section 6.3.3: Cleaning Up.

  9. After we are done using MySQL, we should close the connection. After the other PHP commands, add the following statement and then reload (refresh) the Web page in your browser to verify you have no errors: 
    mysql_close($dbCnx);

    For more information see section 6.3.3: Cleaning Up.

  10. Create a second file named connections.txt and record these questions and the answers to these question in this 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?

  11. Submit these file to Blackboard as part of assignment 6:
    1. query.php
    2. connections.txt

Check Yourself

As time permits, be prepared to answer these questions. You can find more information by following the links after the question.

  1. What are the four connection variables you need to connect to a MySQL database using PHP? (6.3.1)
  2. Why must you put these four variables in a file named dbconvars.php? (6.3.1)
  3. What PHP function do you use to make a SQL query? (6.3.2)
  4. What MySQL function do you use to extract information from a result set? (6.3.2)
  5. Must you free results sets and close MySQL connections after making a query? (6.3.3)
  6. What is the purpose of the "or die()" added to connection and query statements in PHP? (6.3.4)

^ top

Wrap Up

Due Next:
A6-PHP Connections (3/30/09)
Quiz 6 and Discussion Chapter 7 (3/30/09)
When class is over, please shut down your computer if it is on

^ top

Home | WebCT | Announcements | Course info | Expectations | Schedule
Project | Help | FAQ's | HowTo's | Links
Last Updated: March 22 2009 @18:58:44