Scribd
Upload
56 views15 pages

Web Engineering Lab 13

This document outlines the tasks for Lab 13 of the CS-344: Web Engineering course. The lab focuses on advanced PHP concepts and uses a classicmodels database. Task 1 involves connecting to the database, querying for employee data, and displaying it in an HTML table. Task 2 covers adding a new employee form and script to insert data into the database. Additional tasks involve editing, deleting, and updating employee records in the database through PHP scripts.

Uploaded by

Eesha Arif
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
56 views15 pages

Web Engineering Lab 13

This document outlines the tasks for Lab 13 of the CS-344: Web Engineering course. The lab focuses on advanced PHP concepts and uses a classicmodels database. Task 1 involves connecting to the database, querying for employee data, and displaying it in an HTML table. Task 2 covers adding a new employee form and script to insert data into the database. Additional tasks involve editing, deleting, and updating employee records in the database through PHP scripts.

Uploaded by

Eesha Arif
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 15

Department of Computing

CS-344: Web Engineering

Lab 13: Advanced PHP

Date: January 07, 2020

CS-344: Web Engineering Page 1


Lab 13: Advanced PHP

Lab Task
Notes:

1. Use ‘classicmodels.sql’ and run it on phpMyAdmin to create a local database on your


machine. The lab tasks are related to this database.

Task 1:
config.php:

<?php 
$dbhost = 'localhost';
$dbusername="root";
$dbpassword="";
$dbname="classicmodels";

$mysqli = mysqli_connect($dbhost,$dbusername,$dbpassword,$dbname);

?>

CS-344: Web Engineering Page 2


Code: (index.php)

<?php 
include("./config/config.php");

$employees = mysqli_query($mysqli,"SELECT * FROM employees LEFT JOIN offices ON 
employees.officeCode = offices.officeCode ORDER BY employeeNumber;");

?>
<html>
<head>
<title>LAB11-HOMEPAGE</title>
<style>
    th,td{
        padding: 10px;
    }
</style>
</head>
<body>
<a href="add.html">ADD NEW EMPLOYEE</a><br>
<h1>Employee Table</h1>
<table>
<tr bgcolor='#CCCCCC'>
        <th>Name</th>
        <th>Email</th>
        <th>Job Title</th>
        <th>Emp Office Address</th>
        <th>Reports To</th>
        <th>Update</th>
</tr>
<?php 
        while($emp = mysqli_fetch_array($employees)) {      
            echo "<tr>";
            echo "<td>".$emp['firstName']."&nbsp".$emp['lastName']."</td>";
            echo "<td>".$emp['email']."</td>";
            echo "<td>".$emp['jobTitle']."</td>";
            echo "<td>".$emp['addressLine1']."<br>".
$emp['addressLine2']."<br>".$emp['city'].",".$emp['state'].",".
$emp['country']."</td>";
            $id = $emp['reportsTo'];
            $repto_= mysqli_query($mysqli,"SELECT * FROM employees WHERE emplo
yeeNumber=$id;");

CS-344: Web Engineering Page 3


            if ($repto_ == false){
                echo "<td>No one</td>";

            }else{
                while($repto = mysqli_fetch_array($repto_)){
                    echo "<td>".$repto['firstName']."&nbsp".
$repto['lastName'].",".$repto['jobTitle']."</td>";
    
                }
                    
            }
     
            echo "<td><a href=\"edit.php?id=$emp[employeeNumber]\">Edit</a> | 
<a href=\"delete.php?id=$emp[employeeNumber]\" onClick=\"return confirm('Are yo
u sure you want to delete?')\">Delete</a></td>";       
        }
        $mysqli->close();
    ?>
</table>
</body>

</html>

CS-344: Web Engineering Page 4


Task 2:
Code: (add.html)

<html>
  <head>
    <title>Add New Employee</title>
  </head>

  <body>
    <a href="index.php">Home</a>
    <br /><br />

    <form action="add.php" method="post" name="form1">
      <table width="25%" border="0">
        <tr>
            <td>Employee Number</td>
            <td><input type="number" name="employeeNumber" /></td>
          </tr>
        <tr>
          <td>First Name</td>
          <td><input type="text" name="firstName" /></td>

CS-344: Web Engineering Page 5


        </tr>
        <tr>
          <td>Last Name</td>
          <td><input type="text" name="lastName" /></td>
        </tr>
        <tr>
          <td>Job Title</td>
          <td><input type="text" name="jobTitle" /></td>
        </tr>
        <tr>
          <td>Email</td>
          <td><input type="email" name="email" /></td>
        </tr>
        <tr>
          <td>Office Code</td>
          <td><input type="number" name="officeCode" /></td>
        </tr>
        <tr>
          <td>Reports To Employee:(Enter Employee Number)</td>
          <td><input type="number" name="reportsTo" /></td>
        </tr>
        <tr>
          <td></td>
          <td><input type="submit" name="Submit" value="Add" /></td>
        </tr>
      </table>
    </form>
  </body>
</html>

add.php:

<html>
<head>
    <title>Add Data</title>
</head>

<body>
<?php

CS-344: Web Engineering Page 6


//including the database connection file
include_once(".\config\config.php");

if(isset($_POST['Submit'])) {   
    $employeeNumber = mysqli_escape_string($mysqli,$_POST['employeeNumber']);
    $firstName = mysqli_real_escape_string($mysqli, $_POST['firstName']);
    $lastName = mysqli_real_escape_string($mysqli, $_POST['lastName']);
    $email = mysqli_real_escape_string($mysqli, $_POST['email']);
    $jobTitle = mysqli_real_escape_string($mysqli, $_POST['jobTitle']);
    $officeCode = mysqli_real_escape_string($mysqli,$_POST['officeCode']);
    $reportsTo = mysqli_real_escape_string($mysqli,$_POST['reportsTo']);
        
    // checking empty fields
    if(empty($firstName) || empty($lastName) || empty($email) || empty($jobTit
le) || empty($officeCode) || empty($reportsTo)) {
        echo "<font color='red'>Please fill all the fields!!!</font><br/>";
        
        //link to the previous page
        echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
    } else { 
        // if all the fields are filled (not empty) 
            
        //insert data to database   
        $result = mysqli_query($mysqli, "INSERT INTO employees(employeeNumber,f
irstName,lastName,email,jobTitle,officeCode,reportsTo,extension) VALUES($employ
eeNumber,'$firstName','$lastName','$email','$jobTitle',$officeCode,
$reportsTo,'x5800');");
        
        //display success message
        echo "<font color='green'>Data added successfully.";
        echo "<br/><a href='index.php'>View Result</a>";
    }
    
    $mysqli->close();
}
?>
</body>
</html>

CS-344: Web Engineering Page 7


CS-344: Web Engineering Page 8
CS-344: Web Engineering Page 9
Code (delete.php):

<?php
//including the database connection file
include(".\config\config.php");

//getting id of the data from url
$id = $_GET['id'];

//deleting the row from table
$result = mysqli_query($mysqli, "DELETE FROM employees WHERE employeeNumber=$id
");

$mysqli->close();

//redirecting to the display page (index.php in our case)
header("Location:index.php");
?>

CS-344: Web Engineering Page 10


Code (edit.php):

<?php
// including the database connection file
include_once(".\config\config.php");

if(isset($_POST['update'])){    

    $employeeNumber = mysqli_escape_string($mysqli,$_POST['employeeNumber']);
    $firstName = mysqli_real_escape_string($mysqli, $_POST['firstName']);
    $lastName = mysqli_real_escape_string($mysqli, $_POST['lastName']);

CS-344: Web Engineering Page 11


    $email = mysqli_real_escape_string($mysqli, $_POST['email']);
    $jobTitle = mysqli_real_escape_string($mysqli, $_POST['jobTitle']);
    $officeCode = mysqli_real_escape_string($mysqli,$_POST['officeCode']);
    $reportsTo = mysqli_real_escape_string($mysqli,$_POST['reportsTo']);
    
    // checking empty fields
    if(empty($firstName) || empty($lastName) || empty($email) || empty($jobTit
le) || empty($officeCode) || empty($reportsTo)) {
        $_GET['id'] = $id;      
        
        echo "<font color='red'>Please fill all the fields!!!</font><br/>";
    } else {

        //updating the table
        $result = mysqli_query($mysqli, "UPDATE employees SET firstName='$firs
tName',lastName='$lastName',jobTitle='$jobTitle',email='$email',officeCode=$off
iceCode,reportsTo=$reportsTo WHERE employeeNumber=$employeeNumber");
        $mysqli->close();
        //redirectig to the display page. In our case, it is index.php
        header("Location: index.php");
    }
}
?>

<?php
//getting id from url
$id = $_GET['id'];

//selecting data associated with this particular id
$result = mysqli_query($mysqli, "SELECT * FROM employees WHERE employeeNumber=$
id");

while($res = mysqli_fetch_array($result)){
    $employeeNumber = $res['employeeNumber'];
    $firstName = $res['firstName'];
    $lastName = $res['lastName'];
    $email = $res['email'];
    $jobTitle = $res['jobTitle'];
    $officeCode =$res['officeCode'];
    $reportsTo =$res['reportsTo'];
}

CS-344: Web Engineering Page 12


$mysqli->close();
?>

<html>
<head>  
    <title>Edit Data</title>
</head>

<body>
    <a href="index.php">Home</a>
    <br/><br/>
    
    <form name="form1" method="post" action="edit.php">
        <table border="0">
        <tr>
            <td>Employee Number</td>
            <td><input type="number" name="employeeNumber" value="<?php echo $
employeeNumber;?>"/></td>
          </tr>
        <tr>
          <td>First Name</td>
          <td><input type="text" name="firstName" value="<?php echo $firstNam
e;?>"/></td>
        </tr>
        <tr>
          <td>Last Name</td>
          <td><input type="text" name="lastName" value="<?php echo $lastName;?
>"/></td>
        </tr>
        <tr>
          <td>Job Title</td>
          <td><input type="text" name="jobTitle" value="<?php echo $jobTitle;?
>"/></td>
        </tr>
        <tr>
          <td>Email</td>
          <td><input type="email" name="email" value="<?php echo $email;?
>"/></td>
        </tr>
        <tr>
          <td>Office Code</td>

CS-344: Web Engineering Page 13


          <td><input type="number" name="officeCode" value="<?php echo $office
Code;?>" /></td>
        </tr>
        <tr>
          <td>Reports To Employee:(Enter Employee Number)</td>
          <td><input type="number" name="reportsTo" value="<?php echo $reports
To;?>"/></td>
        </tr>
        
            <tr>
                <td><input type="hidden" name="id" value=<?php echo $_GE
T['id'];?>></td>
                <td><input type="submit" name="update" value="Update"></td>
            </tr>
        </table>
    </form>
</body>
</html>

CS-344: Web Engineering Page 14


CS-344: Web Engineering Page 15

You might also like