week6 & 7.html & php

index.html
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Retrieving, Updating and Deleting data</title>
  <link rel="stylesheet" href="styles.css">
</head>
<body>
  <div class="container">
    <div class="nav">
      <ul>
        <li><a href="index.html">Home</a></li>
      </ul>
    </div>
    <div class="content">
      <!--to retrieve data-->

      <form action="retrieve.php" method="post" id="retrieve">

        <fieldset>

          <legend>Retrieve Data</legend>

          User ID <input type="text" name="userID-retrieve" id="userID-retrieve">

          Last Name <input type="text" name="lname-retrieve" id="lname-retrieve">

          Email <input type="email" name="email-retrieve" id="email-retrieve">
          <input type="submit" name="retrieve" value="retrieve">

        </fieldset>

      </form>

      <br>

      <!--to update data-->

      <form action="update.php" method="post" id="update">

        <fieldset>

          <legend>Update Data</legend>

          <input type="hidden" id="upd-userID" name="upd-userID" value="lois_5" />

          First Name <input type="text" name="upd-fname" id="upd-fname"> <br>

          Last Name <input type="text" name="upd-lname" id="upd-lname">

          <input type="submit" name="update" value="update">

        </fieldset>

      </form>

      <br>

      <!--to delete data-->

      <form action="delete.php" method="post" id="delete">

        <fieldset>

          <legend>Delete Data</legend>

          User ID <input type="text" name="uid-delete" id="uid-delete">

          Email <input type="email" name="email-delete" id="email-delete">

          <input type="submit" name="delete" value="delete">

        </fieldset>

      </form>

      <br><br>

    </div>

    <div class="footer">
      &copy; adnagee creations inc.
    </div>
  </div>

</body>
</html>

retrieve.php
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Retrieving data</title>
  <link rel="stylesheet" href="styles.css">
</head>
<body>
  <div class="container">
    <div class="nav">
      <ul>
        <li><a href="index.html">Home</a></li>
      </ul>
    </div>
    <div class="content">
      <h2>Retrieving data from the database</h2>
      <p>This process still requires the 5 steps as when we stored data to the database i.e. </p>
      <ol>
        <li>Connect to the database</li>
        <li>Retrieve the form data</li>
        <li>Create the query string</li>
        <li>Execute the query</li>
        <li>Provide user feedback, based on the success of the query</li>
      </ol>
      <p>What we should pay particular attention to though is that step 5 is more involved as we have to process the retrieved data by adding it dynamically to the web page. <br> As you will notice in this case the $result variable will not simply hold TRUE or FALSE, but will instead contain a <strong>resultset</strong> object which is essentially a 2-dimensional array or table-like data structure.</p>
      <?php

      // set database variables
      $server = 'localhost';
      $user = 'root';
      $password = ''; //there is no password for the current setup of MySQL
      $database = 'ITEC244_2410';
      //make the actual connection to MySQL and the chosen database
      $conn = mysqli_connect($server, $user, $password, $database);
      //if the connection failed print error message
      if (!$conn) {
        die('Database Connection failed ' . mysqli_connect_error());
      }
      echo "Successful connection to $database ";
      //establish connection and select the database as shown previously
      //retrieve the form data
      $uid = $email = $lname = ""; //initialize the variables to empty strings
      if (!empty($_POST['userID-retrieve']))
        $uid = $_POST['userID-retrieve'];
      if (!empty($_POST['email-retrieve']))
        $email = $_POST['email-retrieve'];
      if (!empty($_POST['lname-retrieve']))
        $lname = $_POST['lname-retrieve'];


      //establish connection and select the database as shown previously
      //retrieve the form data as shown previously
      //Create the query string
      $query = "select * from fans where userid = '$userID' OR email = '$email' OR lname = '$lname';";
      //establish connection and select the database as shown previously
      //retrieve the form data as shown previously
      //create the query string
      //execute the query
      $result = mysqli_query($conn, $query);
      if ($result) {
        if (mysqli_num_rows($result) > 0) {
          // if we are in this block it means that $result was NOT false and it contained
          // at least one row of data... so let's process the data one row at a time
          while ($row = mysqli_fetch_assoc($result)) {
            // if we are in this inner block it means that we just successfully retrieved
            // a row of data from the resultset held in $result. We can embed this in
            // our HTML page as shown below
            echo "<h4 class=\"standout\">$row[userID] <br>
            <img src='$row[profile_pic]'  alt='profile' /></h4><p>Full Name: $row[fname] $row[lname]
        <br>Email Address: $row[email]</p>";
          } //end while loop
        } //end if 1 or more rows
        else
          echo "<br>No rows returned";
      }
      mysqli_close($conn);
      ?>
    </div>
    <div class="footer">
      &copy; adnagee creations inc.
    </div>
  </div>

</body>
</html>

update.php
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Updating data</title>
  <link rel="stylesheet" href="styles.css">
</head>
<body>
  <div class="container">
    <div class="nav">
      <ul>
        <li><a href="index.html">Home</a></li>
      </ul>
    </div>
    <div class="content">
      <h2>Updating data in database</h2>
      This process still requires the 5 steps as when we stored and retrieved data to/from the database i.e.
      <ol>
        <li>Connect to the database</li>
        <li>Retrieve the form data</li>
        <li>Create the query string</li>
        <li>Execute the query</li>
        <li>Provide user feedback, based on the success of the query</li>
      </ol>
      <p>If the update is unsuccessful for any reason the $result variable will contain FALSE, otherwise it will be TRUE.</p>
      <?php

       // set database variables
       $server = 'localhost';
       $user = 'root';
       $password = ''; //there is no password for the current setup of MySQL
       $database = 'ITEC244';
       //make the actual connection to MySQL and the chosen database
       $conn = mysqli_connect($server, $user, $password, $database);
       //if the connection failed print error message
       if (!$conn) {
         die('Database Connection failed ' . mysqli_connect_error());
       }
       echo "Successful connection to $database ";

       //retrieve the form data
       $fname = $lname = "";
       $userID = $_POST['upd-userID'];
       //retrieve the hidden data from the form
       if (!empty($_POST['upd-fname']))
       $fname = $_POST['upd-fname'];
       if (!empty($_POST['upd-lname']))
       $lname = $_POST['upd-lname'];

       //Create the query string
       $query = "update fans set fname = '$fname', lname = '$lname' where userID = '$userID';";
       //execute the query
       $result = mysqli_query($conn, $query);

       //process the results and provide feedback
       if ($result)
       {
        //query successfully executed in MySQL
        $rows_aff = mysqli_affected_rows($conn);
        //retrieves the number of rows updated
        if ($rows_aff > 0) {// at least 1 row was updated
          echo "$rows_aff rows were updated as requested. <br><br>";
        }
        else
         {
          // query executed but no rows were updated
          echo "Sorry no rows were found for user ID $userID. <br><br>";
         }
         }
         else {
          echo "Sorry, there was an error in processing this update. <br><br>";
      }

         //close the connection
         mysqli_close($conn);

?>
    </div>
    <div class="footer">
      &copy; Priyanka & Kareena creations inc.
    </div>
  </div>

</body>
</html>

delete.php
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Deleting data</title>
  <link rel="stylesheet" href="styles.css">
</head>
<body>
  <div class="container">
    <div class="nav">
      <ul>
        <li><a href="index.html">Home</a></li>
      </ul>
    </div>
    <div class="content">
      <h2>Deleting data from database</h2>
     <p>Once again, this process requires the 5 steps as when we stored, retrieved and updated data to/from the database i.e. </p>
      <ol>
        <li>Connect to the database</li>
        <li>Retrieve the form data</li>
        <li>Create the query string</li>
        <li>Execute the query</li>
        <li>Provide user feedback, based on the success of the query</li>
      </ol>
      <p>Like the update query, if the delete is unsuccessful for then the $result variable will contain FALSE, otherwise it will be TRUE.</p>

      <?php

// set database variables
$server = 'localhost';
$user = 'root';
$password = ''; //there is no password for the current setup of MySQL
$database = 'ITEC244';
//make the actual connection to MySQL and the chosen database
$conn = mysqli_connect($server, $user, $password, $database);
//if the connection failed print error message
if (!$conn) {
  die('Database Connection failed ' . mysqli_connect_error());
}
echo "Successful connection to $database ";

//retrieve the form data
$del_userID = $del_email = "";
if (isset($_POST['userID-delete']))
$del_userID = $_POST['userID-delete'];
if (isset($_POST['email-delete']))
$del_email = $_POST['email-delete'];

//Create the query string
$query = "delete from fans where userID = '$del_userID' OR email = '$del_email';";
//execute the query
$result = mysqli_query($conn, $query);

//process the results and provide feedback
if ($result) { //query successfully executed in MySQL
  $rows_aff = mysqli_affected_rows($conn);
  //retrieves the number of rows updated
  if ($rows_aff > 0) { // at least 1 row was deleted
    echo "$rows_aff row(s) were deleted for user with ID $del_userID or email $del_email as requested. <br><br>";
  } else
  {
    // query executed but no rows matched the criterion of the where clause.
    echo "Sorry no rows were found for user with ID $del_userID or email $del_email. <br><br>";
   }
  }
  else
  {
     echo "Sorry, there was an error in processing this delete. <br><br>";
    }

//close the connection
mysqli_close($conn);

?>
    </div>
    <div class="footer">
      &copy; Priyanka & Kareena creations inc.
    </div>
  </div>

</body>
</html>