PHP and MySQL

Database connection, insert from PHP, select and show in PHP

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.

Createing 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

To change a session variable, just overwrite it.

Destroy 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():

https://www.w3schools.com/php/php_sessions.asp

PHP Database Connection

PHP provides several ways in obtaining a database connection. You can use a function mysqli to connect MySQL databases. However, in case you need to connect other data sources other than MySQL, additional work for using the function mysqli is necessary. The PHP data object (PDO) provides a way of connecting to various database systems. In this lab, you will learn how to use mysqli to establish a MySQL database connection.

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


// (1) Create a MySQL database connection:
// suppose the user name is temp8 and 
// the database you will use is yourusername:


 $db = new mysqli("localhost", "temp8", "password", "temp8");

// (2) Check connection: 
// if failed to establish a connection, then exit the php program
     if ($db->connect_error)
    {
        die ("Connection failed: " . $db->connect_error);
    }


// (3) Run your SQL commands in PHP...

// (4) Always close Database connection:
  $db->close();

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

The following provides details of how to use mysqli function.

MySQL database connection: http://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: Create table using PHP is not recommended, this is only for students who have not created the table User yet.

<?php
/// Create connection
$conn = new mysqli("localhost", "temp8", "password", "temp8");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

// 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)
)";

if ($conn->query($sql) === TRUE) {
    echo "Table User created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>
 

To execute the above code in hercules, use command php CreateTable.php at the hercules prompt.

Here is a link on how to use sql to create table. http://www.w3schools.com/php/php_mysql_create_table.asp

Insert from PHP

After establishing a MySQL database connection, you can use a function mysqli_query to execute your SQL command and insert a new record to your database table. The function returns true if the operation is successful, otherwise, it returns false. In case the insert operation failed, you need to show the error message to the user.

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


if (isset($_POST["submitted"]) && $_POST["submitted"])
{
    $email = trim($_POST["email"]);
    $date = trim($_POST["date"]);
    $password = trim($_POST["password"]);
       
    $db = new mysqli("localhost", "temp8", "password", "temp8");
    if ($db->connect_error)
    {
        die ("Connection failed: " . $db->connect_error);
    }
    
    $q1 = "SELECT * FROM User WHERE email = '$email'";
    $r1 = $db->query($q1);

    // if the email address is already taken.
    if($r1->num_rows > 0)
    {
        $validate = false;
    }
    else
    {
        $emailMatch = preg_match($reg_Email, $email);
        if($email == null || $email == "" || $emailMatch == false)
        {
            $validate = false;
        }
        
              
        $pswdLen = strlen($password);
        $pswdMatch = preg_match($reg_Pswd, $password);
        if($password == null || $password == "" || $pswdLen< 8 || $pswdMatch == false)
        {
            $validate = false;
        }

        $bdayMatch = preg_match($reg_Bday, $date);
        if($date == null || $date == "" || $bdayMatch == false)
        {
            $validate = false;
        }
    }

    if($validate == true)
    {
        $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
        // variables in the form are: email, password, dateFormat, 
        // start with $q2 =
       
        $r2 = $db->query($q2);
        
        if ($r2 === true)
        {
            header("Location: Login.php");
            $db->close();
            exit();
        }
    }
    else
    {
        $error = "email address is not available. Signup failed.";
        $db->close();
    }

}
?>

The following link provides the detail.

PHP insert: http://www.w3schools.com/php/php_mysql_insert.asp

http://php.net/manual/en/function.preg-match.php

Query from PHP

You can use the following code for Login.php

<?php

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

$email = "";
$error = "";

if (isset($_POST["submitted"]) && $_POST["submitted"])
{
    $email = trim($_POST["email"]);
    $password = trim($_POST["password"]);
    
    $db = new mysqli("localhost", "temp8", "password", "temp8");
    if ($db->connect_error)
    {
        die ("Connection failed: " . $db->connect_error);
    }

    //add code here to select * from table User where email = '$email' AND password = '$password'
    // start with $q = 
       
    $r = $db->query($q);
    $row = $r->fetch_assoc();
    if($email != $row["email"] && $password != $row["password"])
    {
        $validate = false;
    }
    else
    {
        $emailMatch = preg_match($reg_Email, $email);
        if($email == null || $email == "" || $emailMatch == false)
        {
            $validate = false;
        }
        
        $pswdLen = strlen($password);
        $passwordMatch = preg_match($reg_Pswd, $password);
        if($password == null || $password == "" || $pswdLen < 8 || $passwordMatch == false)
        {
            $validate = false;
        }
    }
    
    if($validate == true)
    {

        session_start();
        $_SESSION["email"] = $row["email"];
        header("Location: index.php");
        $db->close();
        exit();
    }
    else 
    {
        $error = "The email/password combination was incorrect. Login failed.";
        $db->close();
    }
}

?>

The following links provide the detail of database operations, such as query, 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

In Class Exercise Lab Assignment

Step 1: Create table User if you haven't done so.

Step 2: Your Signup.php collects User information.

Step 3: Your Login.php collect user login information.

For your convenience, Here are the code for index.php, Logout.php

Link your index.php on your home page. Zip the following files, and submit to URcourses by 11:55 PM.

  1. CreateTable.php
  2. Signup.php
  3. Login.php
  4. Logout.php