lecture 23 robb t. koether
TRANSCRIPT
PHP QueryingLecture 23
Robb T. Koether
Hampden-Sydney College
Fri, Mar 8, 2013
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 1 / 22
1 Searching the Database
2 Deleting from the Database
3 Inserting into a Database
4 Updating the Database
5 Assignment
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 2 / 22
Outline
1 Searching the Database
2 Deleting from the Database
3 Inserting into a Database
4 Updating the Database
5 Assignment
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 3 / 22
Searching the Database
Suppose that we want to search the employees table of thecompany database for a specific employee.We must
Use a form to get the employee’s Social Security number (theprimary key).Send it to a PHP program.Query the database for that Social Security number.Report the results.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 4 / 22
Getting the Social Security Number
Getting the Social Security Number<form method="POST" action="find_emp.php">Enter the Social Security number of the employeethat you wish to find.<br/><input type="text" name="ssn"><br/><input type="submit" value="Find Employee"/><input type="reset"/></form>
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 5 / 22
Searching the Database
Search the Database$ssn = $_POST[’ssn’];$query = "SELECT fname, lname FROM employees"
. " WHERE ssn=’$ssn’";$result = mysql_query($query) or die(...);if (mysql_num_rows($result) == 0)
echo "Employee with ssn $ssn was not found";else{
$row = mysql_fetch_array($result);$fname = $row[’fname’];$lname = $row[’lname’];echo "$fname $lname was found";
}
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 6 / 22
Searching the Database
Now suppose that we want to report the dependents of thatemployee.We must
Search for search the dependents table of the employee’s SocialSecurity number.Retrieve the names of his dependents.Report the dependents’ names.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 7 / 22
Searching the Database
Search the Database$query = "SELECT dep_name FROM dependents"
. " WHERE ssn=’$ssn’";$result = mysql_query($query) or die(...);if (mysql_num_rows($result) == 0)
echo "$fname $lname has no dependents.";else{
echo "Dependents of $fname $lname are:<br/>";while ($row = mysql_fetch_array($result))
echo "$row[’dep_name’]<br/>";}
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 8 / 22
Outline
1 Searching the Database
2 Deleting from the Database
3 Inserting into a Database
4 Updating the Database
5 Assignment
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 9 / 22
Deleting from the Database
Suppose that we want to delete an employee from the database.We must
Select the employee from a list.Delete the employee’s tuple from the employees table.Delete the employee’s tuples from the dependents and workstables.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 10 / 22
Deleting from the Database
In the HTML Form<form method="POST" action="del_emp.php">Select the employee to be deleted:<select name="ssn"><?php$query = "SELECT fname, lname, ssn FROM employees";$result = mysql_query($query) or die(...);while ($row = mysql_fetch_array($result)){
$fname = $row[’fname’];$lname = $row[’lname’];$ssn = $row[’ssn’];echo "<option value=’$ssn’>$fname $lname</option>";
}?></select><input type="submit" value="Delete Employee"/><input type="reset"/></form>
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 11 / 22
Deleting from the Database
In the filedel_emp.php$ssn = $_POST[’ssn’];$query = "DELETE FROM employees WHERE ssn=$ssn";mysql_query($query) or die(...);$query = "DELETE FROM dependents WHERE ssn=$ssn";mysql_query($query) or die(...);$query = "DELETE FROM works WHERE ssn=$ssn";mysql_query($query) or die(...);echo "The employee has been deleted";
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 12 / 22
Outline
1 Searching the Database
2 Deleting from the Database
3 Inserting into a Database
4 Updating the Database
5 Assignment
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 13 / 22
Inserting into a Database
We will use the company database for our examples.Suppose that we want to add an employee to the database.We must get from the user the employee’s
First and last names.Social Security number.Sex.Birthday.Salary.Department.
Then insert these data into the employees table.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 14 / 22
Inserting into the Database
Insert into the Database$fname = $_POST[’fname’];$lname = $_POST[’lname’];$ssn = $_POST[’ssn’];$sex = $_POST[’sex’];$bdate = $_POST[’bdate’];$salary = $_POST[’salary’];$dept = $_POST[’dept’];$query = "SELECT * FROM employees WHERE ssn=’$ssn’";$result = mysql_query($query) or die(...);
We must get the form information and find out whether the employee isalready in the database.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 15 / 22
Inserting into the Database
Insert into the Databaseif (mysql_num_rows($result) > 0)
echo "$fname $lname is already in the database";else{
$query = "INSERT INTO employees". " VALUES (’$fname’, ’$lname’, ’$ssn’, ’$sex’,". " ’$bdate’, $salary, $dept)";
mysql_query($query) or die(...);echo "$fname $lname has been added to the database";
}
If he is not already in the database, then add him.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 16 / 22
The HTML Form
In the HTML form, we needText boxes for the first name, last name, Social Security number,and salary.Select elements for the sex, birthday, and department.Select elements for the birthday (month, day, year).Select elements or radio buttons for the sex and department.
We can fill the sex and birthday choices with the obvious values.For the department, we should construct the list of options fromthe database, using the departments table.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 17 / 22
Creating a Select List of Departments
Create a Select List of Departments<select name="dept"><?php$query = "SELECT dname, dept FROM departments";$result = mysql_query($query) or die(...);while ($row = mysql_fetch_array($result)){
$dname = $row[’dname’];$dept = $row[’dept’];echo "<option value=’$dept’>$dname</option>";
}?></select><br/>
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 18 / 22
Outline
1 Searching the Database
2 Deleting from the Database
3 Inserting into a Database
4 Updating the Database
5 Assignment
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 19 / 22
Updating the Database
We want to update the data for an employee.We must
Select the employees name and Social Security number from theemployees table.Display the current employee information.Allow the information to be edited.Save the changes.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 20 / 22
Outline
1 Searching the Database
2 Deleting from the Database
3 Inserting into a Database
4 Updating the Database
5 Assignment
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 21 / 22
Assignment
AssignmentVisit the W3Schools website
http://www.w3schools.com/html/Visit the sections labeled “PHP MySQL Intro” through ”PHPDELETE.”
Visit the PHP websitehttp://php.net/manual/en/book.mysql.php
and check out the functions that we used.
Robb T. Koether (Hampden-Sydney College) PHP Querying Fri, Mar 8, 2013 22 / 22