PHP and MySQL

PHP Sessions

What is a PHP Session?

A session is a way to store information (in variables) to be used across multiple pages. Session variables are stored on the server. By default, session variables last until the user closes the browser.

Creating a Session

The session_start() function must be the very first thing in your document. Every script on your site that starts the same session will be able to access the same set of session variables. The call to session_start to create a new session is as simple as

session_start();

Get PHP Session Variable Values

Most sessions set a user-key on the user's computer that looks something like this: 765487cf34ert8dede5a562e4f3a7e12. When a session is opened on another page, it scans the computer for a user-key. If there is a match, it accesses that session, if not, it starts a new session.

Modify a PHP Session Variable

Session data is stored in the $_SESSION superglobal variable. It is an associative array, and can be used to store and access information like this:

//Put this on one PHP page:
session_start(); // $_SESSION only available when session is started
$_SESSION["myData"] = "Testing, testing 1... 2... 3...";

//Put this on another PHP page:
session_start(); // $_SESSION only available when sesion is started
echo $_SESSION["myData"]; 

End a PHP Session

When a user request to log out from a website, it is time to end a session. To remove all global session variables and destroy the session, use session_unset() and session_destroy()

//Put this in a PHP page where you wish to end the session
//Note - the page must use session_start() before this code will work
session_unset();
session_destroy(); 

More Information on PHP Sessions: https://www.w3schools.com/php/php_sessions.asp

PHP Database Connection

This discussion is summarized from the official PHP MySQL Manual

In PHP, database access is provided through extension APIs to the base PHP language. These APIs allow you to connect to a databse with a connector, then send and receive messages to an external database through the connection. Each database system that a PHP installation supports depends on a driver that is specific to that database type.

Definitions for Highlighted Terms

• API: Application Programming Interface. The set of things that lets you carry out a task from a programming language. Database programming APIs in PHP are exposed by PHP extensions. PHP has both procedural and OO APIs. OO APIs are preferred by the PHP dev community.
• Connector: The software that lets your database application connect to a MySQL database server. Will probably provide basic ways to send SQL commands, may have libraries that layer over it to make difficult tasks easier.
• Driver: the software that knows how to communicate to a specific type of database server. Drivers often written on top of an external library written in a low level language like C, eg. MySQL Client Library or MySQL Native Driver. The drivers an installation of PHP provides is selected at compile time. The PDO::getAvailableDrivers command can provide you with a full list of drivers available to you on our server.
• Extension: in PHP this is anything that adds functionality to the core PHP language. Extensions may expose an new API directly to the PHP programmer - mysqli and PDO do this. Other extensions don't - the PDO MySQL driver extension provides an interface to PDO instead. The programmer can then use PDO to interact with MySQL connections as a generic database.

PHP's MySQL API Options

• mysqli extension: an improved version of the original mysql API for PHP. The improvements include Object Oriented programming and debugging support among many other nice features. This API is powerful and MySQL specific.
• PHP Data Objects (PDO) with PDO MySQL driver extension: PDO is Object Oriented by design. It provides a consistent API for working with different database servers. It allows you to write clean and portable database code, but only allows generic database features common to many database types. Logic for using the database will not change, but you may still have to rewrite your connection and query commands to match your database's syntax and features.

Both APIs have similar performance, so we will use PDO for the class and lab to help you apply your skills to other Database systems.

Using PDO

You should familiarize yourself with all the functions discussed here by reading the corresponding entries and examples in the PDO Manual.

You need to prepare the following information before establishing a MySQL database connection in PHP:

1. Server address of MySQL database.
   Until September, the WebDev server and the MySQL server are the same machine, so you use localhost as the database server address.
   In the future we will be using a dedicated MySQL server: mysqlusr.cs.uregina.ca
2. The specific name of database that will run your SQL commands.
3. Your MySQL database account name.
4. Your MySQL database password.

// (1) Try to create a MySQL database connection:
try {
    $db = new PDO('mysql:host=localhost; dbname=temp8", "temp8", "password");
    // Since PHP 8.0.0 PDO throws exceptions by default for all errors.
    // If you are using an older version of PHP, errors are silent
    // you can enable them with this line
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

// (2) Catch exception errors. 
//     If you don't catch exceptions, the default action is to exit the script
//     and print a back trace that might reveal security details.
catch (PDOException $e) {
    die ("PDO Error >> " . $e->getMessage() . "\n<br />");
}


// (3) Run your SQL commands in PHP...
$queryResult = ....; // some query
...; // fill with actual code to 
...; // handle the query

// (4) Always close the database connection
//     To do this set all variables that refer to the PDO instance to null
$queryResult = null; //Close queries
$db = null; // Close connection

Note: You must always remember to close each database connection you established after you have done with your SQL work in PHP.

The following links provide more information on managing PDO connections.

• PHP PDO Manual: Connections and Connection management - https://www.php.net/manual/en/pdo.connections.php
• W3Schools: PHP Connect to MySQL (Scroll down for PDO instructions): https://www.w3schools.com/php/php_mysql_connect.asp

Create a table in MySQL

The CREATE TABLE statement is used to create a table called User in MySQL.

The following code create a table in php, you can name it CreateTable.php

Note: Creating a table with PHP is not recommended, this is for demonstration purposes only.

<?php
try {
    // Create connection
    // an exception is thrown if the connection attempt fails
    $db = new PDO("mysql:host=localhost; dbname=temp8", "temp8", "password");

    // sql to create table
    $sql = "CREATE TABLE User (
                user_id INT NOT NULL AUTO_INCREMENT,
                email VARCHAR(255) NOT NULL,
                password VARCHAR(30) NOT NULL,
                DOB DATE NOT NULL,
                PRIMARY KEY (user_id)
            )";

    // use exec because we expect no return.
    // an exception is thrown if the table cannot be created
    $db->exec($sql);
    echo "Table User created successfully!\n<br />";
} catch (PDOException $e) {
    echo "PDO Error >> " . $e->getMessage() . "\n<br />";
}

$db = null;
?>

You can run this code by visiting it like a web page in the browser, or you can execute it directly on WebDev by using the command php CreateTable.php at the WebDev prompt.

Here are some links on the commands and techniques used to create a MySQL table with PDO:

• W3Schools: PHP MySQL Create Table - http://www.w3schools.com/php/php_mysql_create_table.asp
• PHP PDO Manual: PDO::exec - https://www.php.net/manual/en/pdo.exec.php

Insert from PHP

After establishing a MySQL database connection, you can use the PDO::exec function to execute SQL commands that don't return lists of results. In this case it is perfect for for inserting a new record to your database table. The PDO::exec function returns a non-zero integer if the operation is successful, otherwise, it returns false or a non-Boolean value that evaluates to false in Boolean expressions. If the insert operation fails, you need to show an error message to the user in a useful place.

You can use the following code for Signup.php

<?php
$validate = true;
$error = "";
$reg_Email = "/^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$/";
$reg_Pswd = "/^(\S*)?\d+(\S*)?$/";
$reg_Bday = "/^\d{1,2}\/\d{1,2}\/\d{4}$/";
$email = "";
$date = "mm/dd/yyyy";


// Check if a form was sent:
//   submitSignup is the name of the submit button on the form, 
//   it needs to be set and any non-zero value is true
if (isset($_POST["submitSignup"]) && $_POST["submitSignup"]) {
    $email = trim($_POST["email"]);
    $date = trim($_POST["date"]);
    $password = trim($_POST["password"]);
    
    try {
        //connect to database
        $db = new PDO("mysql:host=localhost; dbname=temp8", "temp8", "password");

        //Validate all fields before attempting a query

        // Validate email format
        $emailMatch = preg_match($reg_Email, $email);
        if($email == null || $email == "" || $emailMatch == false) {
            $validate = false;
            $error .= "Invalid email address.\n<br />";
        }

        // Check if the email address is already taken.
        $q1 = "SELECT COUNT(*) FROM User WHERE email = '$email'";
        $count = $db->query($q1)->fetchColumn(); 
        if($count > 0) {
            $validate = false;
            $error .= "Email address already exists.\n";
        } 
              
        // Validate password
        $pswdLen = strlen($password);
        $pswdMatch = preg_match($reg_Pswd, $password);
        if($password == null || $password == "" || $pswdLen < 8 || $pswdMatch == false) {
            $validate = false;
            $error .= "Invalid password.\n<br />";
        }

        // Validate birthday
        $bdayMatch = preg_match($reg_Bday, $date);
        if($date == null || $date == "" || $bdayMatch == false) {
            $validate = false;
            $error .= "Invalid birthday.\n<br />";
        }

        // Only attempt to insert new user if all fields valid
        if($validate == true) {
            //Convert form's date string to MySQL DATE format
            $dateFormat = date("Y-m-d", strtotime($date));

            // add code here to insert a record into the table User;
            // table User attributes are: email, password, DOB
            // values from the form are in these local variables: $email, $password, $dateFormat, 
            // start with $q2 =


        
            $r2 = $db->exec($q2);
            
            if ($r2 != false) {
                header("Location: Login.php");
                $r2 = null;
                $db = null;
                exit();

            } else {
                $r2 = null;
                $validate = false;
                $error .= "Trouble adding new user to database!\n<br />";
            }         
        }
        
        if ($validate == false) {
            $error .= "Signup failed.";
        }
        $db = null;
    } catch (PDOException $e) {
        echo "PDO Error >> " . $e->getMessage() . "\n<br />";
    }
}

// Error printing should be done in a better part of the HTML document.
echo $error;
?>

The following links provide more information about the code used above:

• W3 Schools: PHP insert - http://www.w3schools.com/php/php_mysql_insert.asp
• PHP PDO Manual: PDO::query - https://www.php.net/manual/en/pdo.query.php
• PHP PDO Manual: PDOStatement (returned by PDO::query) - https://www.php.net/manual/en/class.pdostatement.php
• PHP PDO Manual: PDOStatement::rowCount - https://www.php.net/manual/en/pdostatement.rowcount.php
• PHP Manual: Regular expression matching - http://php.net/manual/en/function.preg-match.php

Query from PHP

The PDO query sample on W3 Schools is a bit more complicated than the mysqli examples, so you can study this simplified PDO sample before you study theirs. It will work with the student table created by students.sql in the MySQL lab:

<!DOCTYPE html>
<head>
    <title>PHP Query Demo</title>
    <style>
    table, td, th {border: solid black 1px;}
    table {border-collapse: collapse;}
    </style>
</head>
<body>
    <h1>PDO Query Demo</h1>
    <table>
        <tr><th>Student ID</th><th>First Name</th><th>Last Name</th><th>Email</th></tr>
<?php

try {
    //Make a database connection
    $conn = new PDO("mysql:host=localhost;dbname=temp8", "temp8", "password");

    //Get a query result - a PDOStatement - as an associative array
    $stmt = $conn->query("SELECT distinct student_ID, first_Name, last_Name, email FROM students", PDO::FETCH_ASSOC);

    //Loop through the associative array
    while($row = $stmt->fetch()) {

        //Switch out of PHP to emit HTML with fewer echoes!
        ?>
        <tr>
            <td><?=$row['student_ID']?></td><td><?=$row['first_Name']?></td>
            <td><?=$row['last_Name']?></td>
            <td><?=$row['email']?></td>
        </tr>

        <?php //Switch back to PHP to end the loop
    }
    $stmt = null;
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
?>   
    </table>
</body>
</html>  

You can use the following code for Login.php

<?php

$validate = true;
$error = "";

$reg_Email = "/^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$/";
$reg_Pswd = "/^(\S*)?\d+(\S*)?$/";

$email = "";

// Check if a form was sent:
//   submitLogin is the name of the submit button on the form, 
//   it needs to be set and any non-zero value is true
if (isset($_POST["submitLogin"]) && $_POST["submitLogin"]) {
    $email = trim($_POST["email"]);
    $password = trim($_POST["password"]);
    
    //Before using form data for anything, validate it!
    $emailMatch = preg_match($reg_Email, $email);
    if($email == null || $email == "" || $emailMatch == false) {
        $validate = false;
        $error .= "Invalid email address.\n<br />";
    }
        
    $pswdLen = strlen($password);
    $passwordMatch = preg_match($reg_Pswd, $password);
    if($password == null || $password == "" || $pswdLen < 8 || $passwordMatch == false) {
        $validate = false;
        $error .= "Invalid password.\n<br />";
    }
    
    // Only perform the query if all fields are valid
    if($validate == true) {
        try {
            $db = new PDO("mysql:host=localhost; dbname=temp8", "temp8", "password");

            //add code here to select * from table User where email = '$email' AND password = '$password'
            // start with $q = 

            // Search for the requested email and password combo
            $r = $db->query($q, PDO::FETCH_ASSOC);

            // check result length: should be exactly 1 if there's a match.
            if ($r->rowCount() == 1)
            {
                // if there's a match, get the row
                $row = $r->fetch();

                // add identifying information from the row to the session and go to next page
                session_start();
                $_SESSION["email"] = $row["email"];
                header("Location: index.php");
                $r = null;
                $db=null;
                exit();

            // result had wrong length
            } else {
                $validate = false;
                $error .= "The email/password combination was incorrect. Login failed.";
            }
            $r = null;
            $db=null;
        } catch (PDOException $e) {
            die("PDO Error >> " . $e->getMessage() . "\n<br />");
            
        }
    }

    // If validation or query failed, report errors
    // Maybe move this to HTML section of the page 
    if ($validate == false)
    {
        echo $error;
    }

}

?>

The following links provide details for more database operations, such as select, update and delete.

PHP query and show: http://www.w3schools.com/php/php_mysql_select.asp

PHP Update: http://www.w3schools.com/php/php_mysql_update.asp

PHP Delete: http://www.w3schools.com/php/php_mysql_delete.asp

Short Echo Tags

By using the short echo tag saves a lot of coding time. Here is an example. You will also find examples from Dr. Hoeber's lecture notes.

<?php
$var = "Hello!";
?>
<html>
<head><title>Short echo tag</title></head>
<body>
Variables in PHP code can be displayed in HTML this way.
<?=$var?>
</body>
</html>

Now you know two of many ways to escape to PHP from HTML. While you might see some of the others as you are learning, only <?php ?> and <?= ?> should be used in new PHP scripts.

Redirect to another page

To redirect the visitor to another page simply use the following code:

<?php

header("Location: filename.php");
exit(); //exit the script as soon as you redirect the page.
?>

More information on PHP redirect page, see the following links.

Passing a GET variable through a link with PHP

A way to gather input from the user and pass it to the server for processing is to pass a GET variable through a link with PHP.

To pass a variable in the URL, it uses

http://hello.php/?name=value 

In the receiving page, the superglobal $_GET, or automatic global, gets the value of the variable from the URL:

$_GET['name'];

Assign this value to a variable

$var =$_GET['name']; 

In the hello.php program:

<?php
$name = $_GET['name'];
echo "Hello $name";

?>

Now the user entered

http://hello.php/?name=CS215 

The above example will output something similar to:

Hello CS215

Detailed explanation and examples are listed here.