CS222 Lab 12: MySQL, PHP, JavaScript

Instructor: Trish Cornez

This lab assumes the following MySQL relational database has been created and placed on the host server.
Please add at least 12 data records to this database.
To accurately test this table, enter 3 records containing the same first name.
For example: Bobo Smith, Bobo Greene, and Bobo Butler.

Part A

Use PHP with HTML to select a MySQL database and display the content.

Construct the html page shown in the left image below. Use a form containing a submit button.
Write a PHP script to perform the following tasks:
- Connect to the MySQL database
- Perform an SQL query to collect all records in the Friend database table
- Print only the firstName, lastName, and email.

Part B

Allow PHP to receive input from HTML to selectively specify a MySQL database query.

Modify the html page from Part A, as shown in the left image below.
The page should now feature an input text box for the user to enter a first name.

Modify the PHP script to allow the user to supply input that directs the database query.
The SQL query should be able to SELECT all records with a matching firstName.

Part C:

JavaScript, PHP, and HTML5

Write a JavaScript file to respond to the "Select" button, shown in the left figure below.
A JavaScript method should collect input data and pass it to a PHP file in an XMLHttpRequest.

The PHP file should be coded to peform the specified query and return the results formatted as an HTML table.

Part D:

JavaScript, PHP, and HTML5

Practice 2

expenseADD.php

<?php

//TASK 1: MAKE A CONNECTION TO THE DATABASE, DISPLAY ERROR FOR FAILED CONNECTIONS
        //        Argument 1: IP address provided by GoDaddy
        //        Argument 2: Database user with access privileges
        //        Argument 3: Database password_hash
        //        Argument 4: Database Name
        //        Argument 5: Port for MySQL
        $mysqli = new mysqli("127.0.0.1", "CS222XE", "oaL_1", "C2XEF", 3306);
        if ($mysqli->connect_errno) {
          echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
        }
        
        //TASK 2: GET DATA AND DECODE JSON
        $input_data = file_get_contents('php://input');
        
        //TASK 3: DECODE THE JSON DATA
        $data = json_decode($input_data);
        $expenseEmail = $data->email;
        $expenseName = $data->name;
        $expenseDate = $data->date;  //SQL date format
        $expenseAmount = $data->amount;
        
        //TASK 4: BUILD THE SQL EXPENSE RECORD AND PROCESS THE SQL INSERT INSTRUCTION
        $sql = "INSERT INTO Expenses (EMAIL, ExpenseName, Date, Amount) VALUES ('$expenseEmail','$expenseName', '$expenseDate', $expenseAmount)";
        
        //TASK 5: CHECK FOR AN ERROR
        if ($mysqli->query($sql) === TRUE) {
          echo "Expense added successfully";
        } else {
          echo "Error: " . $sql . "<br>" . $mysqli->error;
        }
        
        //TASK 6: CLOSE THE DATABASE CONNECTION
        $mysqli->close();
        ?>

expenseApp.js

expenseApp.php

<?php

//TASK 1: MAKE A CONNECTION TO THE DATABASE, DISPLAY ERROR FOR FAILED CONNECTIONS
    //        Argument 1: IP address provided by GoDaddy
    //        Argument 2: Database user with access privileges
    //        Argument 3: Database password_hash
    //        Argument 4: Database Name
    //        Argument 5: Port for MySQL
    $mysqli = new mysqli("127.0.0.1", "CS222XE", "oaL_1", "C2XEF", 3306);
    if ($mysqli->connect_errno) {
      echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    
    //TASK 2: BUILD A SQL  INSTRUCTION TO SELECTING ALL RECORDS
    //        RETRIEVE DATA FROM THE PROVIDED EMAIL
    $email = $_GET['EMAIL'];
    $sql = "SELECT * FROM Expenses WHERE EMAIL = '$email'";
    
    //TASK 3: USE THE ESTABLISHED DATABASE CONNECTION TO PROCESS THE DATABASE QUERY.
    //        STORE THE RESULTS IN $result VARIABLE.
    $result = $mysqli->query($sql);
    
    //TASK 4: BUILD A TABLE OF RESULTS IN A STRING
    if ($result->num_rows > 0) {
      $myDisplayResults = "<table>";
      $myDisplayResults .= "<tr>";
      $myDisplayResults .= "<th>Expense Name</th>";
      $myDisplayResults .= "<th>Amount</th>";
      $myDisplayResults .= "<th>Date</th>";
      $myDisplayResults .= "</tr>";
    
      while($row = $result->fetch_assoc()) {
        
        $myDisplayResults .= "<tr>";
        //ADD EXPENSE NAME
        $myDisplayResults .= "<td>$row[ExpenseName]</td>";
       
        //ADD THE EXPENSE AMOUNT FORMATTED AS CURRENCY
        $money = "$row[Amount]";
        $myDisplayResults .= "<td>".money_format('$%i', $money)."</td>";
       
        //ADD THE DATE OF THE EXPENSE
        $myDisplayResults .= "<td>$row[Date]</td>";
        $myDisplayResults .= "</tr>";
      }
      $myDisplayResults .= "</table>";
      echo $myDisplayResults;
    } else {
      echo "0 results";
    }
    
    //TASK 5: CLOSE THE DATABASE CONNECTION
    $mysqli->close();
    ?>