III B.com (CA)/ III BSc- PHP Notes

unit -5

Q) What are MySQL or MySQLi Functions?

MySQLi is an Improved Extension of MYSQL, provides a Procedural Interface as well as an Object Oriented Interface.

MySQLi functions:

1.  mysqli_connect():  This function is used for connecting to MySQL

Example

$link = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

2.  mysqli_connect_error():  mysqli_connect() throws an error at failure, and mysqli_connect_error() stores the error of the last call to mysqli_connect(). If there is no error, it returns NULL.

Example:

<?php

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

if (mysqli_connect_error()) {

$logMessage = 'MySQL Error: ' . mysqli_connect_error();

// Call your logger here.

die('Could not connect to the database');

}

// Rest of the code goes here

?>

3.  mysqli_select_db(): To change the database in use.

For example assume that user robin also has privileges for a database called company_new_db; then you can change the database as below.

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

mysqli_select_db($link, 'company_new_db');

4.  mysqli_close():  we can use this function to close a MySQL connection. It returns TRUE on success and FALSE on failure.

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

mysqli_close($link);

5.  mysqli_query():  This is the function used for executing MySQL queries. It returns FALSE on failure.

$link = @mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

$query = "SELECT * FROM employee";

if (mysqli_query($link, $query)) {

// Iterate and display result

} else {

// Show error

}

mysqli_close($link);

6.  mysqli_fetch_array():  This function is used for reading data from a MySQL result set (returned by a mysqli_query()). It reads and returns one row of data as an array and then moves the pointer to the next row. When there are no more rows to return, it returns NULL. Because of this behavior, it’s often used with a While Loop as below.

while ($row = mysqli_fetch_array($result)) {

/* Till there is data, $row will be an array.

* At the end, $row becomes NULL ending the loop.

*/

}

7.  mysqli_num_rows():  mysqli_num_rows() returns the number of rows in a result set. Using it, you can take a different action when the result set is empty.

if (mysqli_num_rows($result) > 0) {

// Proceed with the $result

} else {

// Show an error message

}

Q) How to Connect to MySQL with PHP

1.  mysqli_connect ( ) function is used to connect MYSQL with PHP.

Syntax:

$mysqli = mysqli_connect(“hostname”, “username”, “password”, “database”);

2.  mysqli_query( ) function is used to execute mysql queries.

<?php

$mysqli = mysqli_connect(“localhost”, “shaik”, “Ayaan2013#”, “ShaikDB”);

if (mysqli_connect_errno())

{

printf(“Connect failed: %s\n”, mysqli_connect_error());

exit();

} else {

$sql = “CREATE TABLE testTable

(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,testField VARCHAR(75))”;

$res = mysqli_query($mysqli, $sql);

if ($res === TRUE) {

echo “Table testTable successfully created.”;

} else {

printf(“Could not create table: %s\n”, mysqli_error($mysqli));

}

mysqli_close($mysqli);

}

?>

Q) Explain Working with MySQL Data

The mysqli_query() function is used to execute the basic SQL queries such as INSERT, UPDATE, and DELETE.

1.  Inserting Data with PHP:  The easiest (and safest) method for inserting data is INSERT statement. The INSERT INTO statement is used to add new records to a MySQL table:

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Example: $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', ‘john@example.com')";

2.  The DELETE statement is used to delete records from a table:

DELETE FROM table_name
WHERE some_column = some_value

Example: $sql = "DELETE FROM MyGuests WHERE id=3";

3.  The UPDATE statement is used to update existing records in a table:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value 

Example: $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

4.  The SELECT statement is used to select data from one or more tables:

SELECT column_name(s) FROM table_name

or

SELECT * FROM table_name

Example: $sql = "SELECT id, firstname, lastname FROM MyGuests";

Example:

<?php
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

Q) Explain the creation of Online Address Book.

1.  Planning and Creating the Database Tables

When you think of an address book, the obvious fields come to mind: name, address, telephone number, email address.

Let us consider an address book, with fields such as  name, address, telephone number, email address.

Address Book Table and Field Names

Table Name                 Field Names

master_name                id, date_added, date_modified, f_name, l_name

address                    id, master_id, date_added, date_modified, address, city,state, zipcode, type

telephone                  id, master_id, date_added, date_modified, tel_number,

type.

fax                        id, master_id, date_added, date_modified, fax_number,type.

email                           id, master_id, date_added, date_modified, email, type.

personal_notes             id, master_id, date_added, date_modified, note.

The SQL statement for master_name is as follows:

CREATE TABLE master_name (

id                   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

date_added      DATETIME, date_modified         DATETIME,

f_name          VARCHAR (75), l_name       VARCHAR (75) );

The SQL statement for address is as follows:

CREATE TABLE address (

id                   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id            INT NOT NULL,

date_added           DATETIME,

date_modified        DATETIME,

address              VARCHAR (255),

city                 VARCHAR (30),

state                      CHAR (2),

zipcode              VARCHAR (10),

type                 ENUM (‘home’, ‘work’, ‘other’) );

The SQL statement for telephone is as follows:

CREATE TABLE telephone (

id              INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id       INT NOT NULL,

date_added      DATETIME,

date_modified   DATETIME,

tel_number      VARCHAR (25),

type            ENUM (‘home’, ‘work’, ‘other’) );

The SQL statement for fax is as follows:

CREATE TABLE fax (

id              INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id       INT NOT NULL,

date_added      DATETIME,

date_modified   DATETIME,

fax_number      VARCHAR (25),

type            ENUM (‘home’, ‘work’, ‘other’));

The SQL statement for email is as follows:

CREATE TABLE email (

id              INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id       INT NOT NULL,

date_added      DATETIME,

date_modified   DATETIME,

email           VARCHAR (150),

type            ENUM (‘home’, ‘work’, ‘other’));

The SQL statement for personal_notes is as follows:

CREATE TABLE personal_notes (

id              INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

master_id       INT NOT NULL UNIQUE,

date_added      DATETIME,

date_modified   DATETIME,

note            TEXT );

2.  Creating an Include File for Common Functions. It set up the database connection function. Save it as addressbook_include.php

addressbook_include.php

<?php

2: function doDB() {

3: global $mysqli;

4:

5: //connect to server and select database; you may need it

6: $mysqli = mysqli_connect(“localhost”, “joeuser”,

7: “somepass”, “testDB”);

8:

9: //if connection fails, stop script execution

10: if (mysqli_connect_errno()) {

11: printf(“Connect failed: %s\n”, mysqli_connect_error());

12: exit();

13: }

14: }

15: ?>

Note:

Lines 2–14 set up the database connection function, doDB. If the connection cannot be made, the script exits when this function is called; otherwise, it makes the value of $mysqli available to other parts of your script.

3.  Creating Menu: Create menu to perform several actions on Online address book , which is shown below.

mymenu.html

1: <!DOCTYPE html>

2: <html>

3: <head>

4: <title>My Address Book</title>

5: </head>

6: <body>

7: <h1>My Address Book</h1>

8:

9: <p><strong>Management</strong></p>

10: <ul>

11: <li><a href=”addentry.php”>Add an Entry</a></li>

12: <li><a href=”delentry.php”>Delete an Entry</a></li>

13: </ul>

14:

15: <p><strong>Viewing</strong></p>

16: <ul>

17: <li><a href=”selentry.php”>Select a Record</a></li>

18: </ul>

19: </body>

20: </html>

4.  Creating the Record-Addition Mechanism: addentry.php  shows a basic record-addition script, which has two parts: what to do if the form should be displayed (lines 4–89) and what actions to take if the form is being submitted (lines 91–187). Lines 3–89 simply place the contents of the HTML form into a string called $display_block.

addentry.php:

1: <?php

2: include ‘addressbook_include.php;

3:

4: if (!$_POST) {

5: //haven’t seen the form, so show it

6: $display_block = <<<END_OF_TEXT

7: <form method=”post” action=”$_SERVER[PHP_SELF]”>

8: <fieldset>

9: <legend>First/Last Names:</legend><br/>

10: <input type=”text” name=”f_name” size=”30”

11: maxlength=”75” required=”required” />

12: <input type=”text” name=”l_name” size=”30”

13: maxlength=”75” required=”required” />

14: </fieldset>

15:

16: <p><label for=”address”>Street Address:</label><br/>

17: <input type=”text” id=”address” name=”address”

18: size=”30” /></p>

19:

20: <fieldset>

21: <legend>City/State/Zip:</legend><br/>

22: <input type=”text” name=”city” size=”30” maxlength=”50” />

23: <input type=”text” name=”state” size=”5” maxlength=”2” />

24: <input type=”text” name=”zipcode” size=”10” maxlength=”10” />

25: </fieldset>

26:

27: <fieldset>

28: <legend>Address Type:</legend><br/>

29: <input type=”radio” id=”add_type_h” name=”add_type”

30: value=”home” checked />

31: <label for=”add_type_h”>home</label>

32: <input type=”radio” id=”add_type_w” name=”add_type”

33: value=”work” />

34: <label for=”add_type_w”>work</label>

35: <input type=”radio” id=”add_type_o” name=”add_type”

36: value=”other” />

37: <label for=”add_type_o”>other</label>

38: </fieldset>

<legend>Telephone Number:</legend><br/>

42: <input type=”text” name=”tel_number” size=”30” maxlength=”25” />

43: <input type=”radio” id=”tel_type_h” name=”tel_type”

44: value=”home” checked />

45: <label for=”tel_type_h”>home</label>

46: <input type=”radio” id=”tel_type_w” name=”tel_type”

47: value=”work” />

48: <label for=”tel_type_w”>work</label>

49: <input type=”radio” id=”tel_type_o” name=”tel_type”

50: value=”other” />

51: <label for=”tel_type_o”>other</label>

52: </fieldset>

53:

54: <fieldset>

55: <legend>Fax Number:</legend><br/>

56: <input type=”text” name=”fax_number” size=”30” maxlength=”25” />

57: <input type=”radio” id=”fax_type_h” name=”fax_type”

58: value=”home” checked />

59: <label for=”fax_type_h”>home</label>

60: <input type=”radio” id=”fax_type_w” name=”fax_type”

61: value=”work” />

62: <label for=”fax_type_w”>work</label>

63: <input type=”radio” id=”fax_type_o” name=”fax_type”

64: value=”other” />

65: <label for=”fax_type_o”>other</label>

66: </fieldset>

67:

68: <fieldset>

69: <legend>Email Address:</legend><br/>

70: <input type=”email” name=”email” size=”30” maxlength=”150” />

71: <input type=”radio” id=”email_type_h” name=”email_type”

72: value=”home” checked />

73: <label for=”email_type_h”>home</label>

74: <input type=”radio” id=”email_type_w” name=”email_type”

75: value=”work” />

76: <label for=”email_type_w”>work</label>

77: <input type=”radio” id=”email_type_o” name=”email_type”

78: value=”other” />

79: <label for=”email_type_o”>other</label>

80: </fieldset>

81:

82: <p><label for=”note”>Personal Note:</label><br/>

83: <textarea id=”note” name=”note” cols=”35”

84: rows=”3”></textarea></p>

85:

86: <button type=”submit” name=”submit”

87: value=”send”>Add Entry</button>

88: </form>

89: END_OF_TEXT;

Stop here for a minute and make sure

90: } else if ($_POST) {

91: //time to add to tables, so check for required fields

92: if (($_POST[‘f_name’] == “”) || ($_POST[‘l_name’] == “”)) {

93: header(“Location: addentry.php”);

94: exit;

95: }

96:

97: //connect to database

98: doDB();

99:

100: //create clean versions of input strings

101: $safe_f_name = mysqli_real_escape_string($mysqli,

102: $_POST[‘f_name’]);

103: $safe_l_name = mysqli_real_escape_string($mysqli,

104: $_POST[‘l_name’]);

105: $safe_address = mysqli_real_escape_string($mysqli,

106: $_POST[‘address’]);

107: $safe_city = mysqli_real_escape_string($mysqli,

108: $_POST[‘city’]);

109: $safe_state = mysqli_real_escape_string($mysqli,

110: $_POST[‘state’]);

111: $safe_zipcode = mysqli_real_escape_string($mysqli,

112: $_POST[‘zipcode’]);

113: $safe_tel_number = mysqli_real_escape_string($mysqli,

114: $_POST[‘tel_number’]);

115: $safe_fax_number = mysqli_real_escape_string($mysqli,

116: $_POST[‘fax_number’]);

117: $safe_email = mysqli_real_escape_string($mysqli,

118: $_POST[‘email’]);

119: $safe_note = mysqli_real_escape_string($mysqli,

120: $_POST[‘note’]);

121:

122: //add to master_name table

123: $add_master_sql = “INSERT INTO master_name (date_added,

124: date_modified, f_name, l_name) VALUES

125: (now(), now(), ‘“.$safe_f_name.”’, ‘“.$safe_l_name.”’)”;

126: $add_master_res = mysqli_query($mysqli, $add_master_sql)

127: or die(mysqli_error($mysqli));

128:

129: //get master_id for use with other tables

130: $master_id = mysqli_insert_id($mysqli);

131:

132: if (($_POST[‘address’]) || ($_POST[‘city’]) ||

133: ($_POST[‘state’]) || ($_POST[‘zipcode’])) {

134: //something relevant, so add to address table

135: $add_address_sql = “INSERT INTO address (master_id,

136: date_added, date_modified, address, city, state,

137: zipcode, type) VALUES

138: (‘“.$master_id.”’, now(), now(),

139: ‘“.$safe_address.”’, ‘“.$safe_city.”’,

140: ‘“.$safe_state.”’ , ‘“.$safe_zipcode.”’ ,

141: ‘“.$_POST[‘add_type’].”’)”;

142: $add_address_res = mysqli_query($mysqli, $add_address_sql)

143: or die(mysqli_error($mysqli));

144: }

145:

146: if ($_POST[‘tel_number’]) {

147: //something relevant, so add to telephone table

148: $add_tel_sql = “INSERT INTO telephone (master_id, date_added,

149: date_modified, tel_number, type) VALUES

150: (‘“.$master_id.”’, now(), now(),

151: ‘“.$safe_tel_number.”’, ‘“.$_POST[‘tel_type’].”’)”;

152: $add_tel_res = mysqli_query($mysqli, $add_tel_sql)

153: or die(mysqli_error($mysqli));

154: }

155:

156: if ($_POST[‘fax_number’]) {

157: //something relevant, so add to fax table

158: $add_fax_sql = “INSERT INTO fax (master_id, date_added,

159: date_modified, fax_number, type) VALUES

160: (‘“.$master_id.”’, now(), now(), ‘“.$safe_fax_number.”’,

161: ‘“.$_POST[‘fax_type’].”’)”;

162: $add_fax_res = mysqli_query($mysqli, $add_fax_sql)

163: or die(mysqli_error($mysqli));

164: }

165: if ($_POST[‘email’]) {

166: //something relevant, so add to email table

167: $add_email_sql = “INSERT INTO email (master_id, date_added,

168: date_modified, email, type) VALUES

169: (‘“.$master_id.”’, now(), now(), ‘“.$safe_email.”’,

170: ‘“.$_POST[‘email_type’].”’)”;

171: $add_email_res = mysqli_query($mysqli, $add_email_sql)

172: or die(mysqli_error($mysqli));

173: }

174:

175: if ($_POST[‘note’]) {

176: //something relevant, so add to notes table

177: $add_notes_sql = “INSERT INTO personal_notes (master_id,

178: date_added, date_modified, note) VALUES

179: (‘“.$master_id.”’, now(), now(),

180: ‘“.$safe_note.”’)”;

181: $add_notes_res = mysqli_query($mysqli, $add_notes_sql)

182: or die(mysqli_error($mysqli));

183: }

184: mysqli_close($mysqli);

185: $display_block = “<p>Your entry has been added. Would you

186: like to <a href=\”addentry.php\”>add another</a>?</p>”;

187: }

188: ?>

189: <!DOCTYPE html>

190: <head>

191: <title>Add an Entry</title>

192: </head>

193: <body>

194: <h1>Add an Entry</h1>

195: <?php echo $display_block; ?>

196: </body>

197: </html>

 The output for add entry is as follows:

Q) Explain how to View Records  of Online Address Book in MYSQL.

Here we create the two-part script that shows how to select and view records in your database.

selentry.php shows the select-and-view script, which has three parts:

1.  the record-selection form (lines 5–42)

2.  the code to display the record contents(lines 43–172), and

3.  the HTML template that displays the dynamically generated strings (lines 176–184).

The Record-Selection Form

1: <?php

2: include ‘addressbook_include.php’;

3: doDB();

4:

5: if (!$_POST) {

6: //haven’t seen the selection form, so show it

7: $display_block = “<h1>Select an Entry</h1>”;

8:

9: //get parts of records

10: $get_list_sql = “SELECT id,

11: CONCAT_WS(‘, ‘, l_name, f_name) AS display_name

12: FROM master_name ORDER BY l_name, f_name”;

13: $get_list_res = mysqli_query($mysqli, $get_list_sql)

14: or die(mysqli_error($mysqli));

15:

16: if (mysqli_num_rows($get_list_res) < 1) {

17: //no records

18: $display_block .= “<p><em>Sorry, no records to select!</em></p>”;

19:

20: } else {

21: //has records, so get results and print in a form

22: $display_block .= “

23: <form method=\”post\” action=\””.$_SERVER[‘PHP_SELF’].”\”>

24: <p><label for=\”sel_id\”>Select a Record:</label><br/>

25: <select id=”sel_id\” name=\”sel_id\” required=\”required\”>

26: <option value=\”\”>-- Select One --</option>”;

27:

28: while ($recs = mysqli_fetch_array($get_list_res)) {

29: $id = $recs[‘id’];

30: $display_name = stripslashes($recs[‘display_name’]);

31: $display_block .=

32: “<option value=\””.$id.”\”>”.$display_name.”</option>”;

33: }

34:

35: $display_block .= “

36: </select>

37: <button type=\”submit\” name=\”submit\”

38: value=\”view\”>View Selected Entry\”></button>

39: </form>”;

40: }

41: //free result

42: mysqli_free_result($get_list_res);

The code to display the record contents

43: } else if ($_POST) {

44: //check for required fields

45: if ($_POST[‘sel_id’] == “”) {

46: header(“Location: selentry.php”);

47: exit;

48: }

49:

50: //create safe version of ID

51: $safe_id = mysqli_real_escape_string($mysqli, $_POST[‘sel_id’]);52:

52:

53: //get master_info

54: $get_master_sql = “SELECT concat_ws(‘ ‘,f_name,l_name) as display_name

55: FROM master_name WHERE id = ‘“.$safe_id.”’”;

56: $get_master_res = mysqli_query($mysqli, $get_master_sql)

57: or die(mysqli_error($mysqli));

58:

59: while ($name_info = mysqli_fetch_array($get_master_res)) {

60: $display_name = stripslashes($name_info[‘display_name’]);

61: }

62:

63: $display_block = “<h1>Showing Record for “.$display_name.”</h1>”;

64:

65: //free result

66: mysqli_free_result($get_master_res);

67:

68: //get all addresses

69: $get_addresses_sql = “SELECT address, city, state, zipcode, type FROM

70: address WHERE master_id = ‘“.$safe_id.”’”;

71: $get_addresses_res = mysqli_query($mysqli, $get_addresses_sql)

72: or die(mysqli_error($mysqli));

73:

74: if (mysqli_num_rows($get_addresses_res) > 0) {

75: $display_block .= “<p><strong>Addresses:</strong><br/>

76: <ul>”;

77:

78: while ($add_info = mysqli_fetch_array($get_addresses_res)) {

79: address = stripslashes($add_info[‘address’]);

80: $city = stripslashes($add_info[‘city’]);

81: $state = stripslashes($add_info[‘state’]);

82: $zipcode = stripslashes($add_info[‘zipcode’]);

83: $address_type = $add_info[‘type’];

84:

85: $display_block .= “<li>$address $city $state $zipcode

86: ($address_type)</li>”;

87: }

88: $display_block .= “</ul>”;

89: }

90: //free result

91: mysqli_free_result($get_addresses_res); 92: //get all tel

93: $get_tel_sql = “SELECT tel_number, type FROM telephone WHERE

94: master_id = ‘“.$safe_id.”’”;

95: $get_tel_res = mysqli_query($mysqli, $get_tel_sql)

96: or die(mysqli_error($mysqli));

97:

98: if (mysqli_num_rows($get_tel_res) > 0) {

99: $display_block .= “<p><strong>Telephone:</strong><br/>

100: <ul>”;

101:

102: while ($tel_info = mysqli_fetch_array($get_tel_res)) {

103: $tel_number = stripslashes($tel_info[‘tel_number’]);

104: $tel_type = $tel_info[‘type’];

105:

106: $display_block .= “<li>$tel_number ($tel_type)</li>”;

107: }

108: $display_block .= “</ul>”;

109: }

110: //free result

111: mysqli_free_result($get_tel_res);

112:

113: //get all fax

114: $get_fax_sql = “SELECT fax_number, type FROM fax WHERE

115: master_id = ‘“.$safe_id.”’”;

116: $get_fax_res = mysqli_query($mysqli, $get_fax_sql)

117: or die(mysqli_error($mysqli));

118:

119: if (mysqli_num_rows($get_fax_res) > 0) {

120: $display_block .= “<p><strong>Fax:</strong><br/>

121: <ul>”;

122:

123: while ($fax_info = mysqli_fetch_array($get_fax_res)) {

124: $fax_number = stripslashes($fax_info[‘fax_number’]);

125: $fax_type = $fax_info[‘type’];

126:

127: $display_block .= “<li>$fax_number ($fax_type)</li>”;

128: }

129: $display_block .= “</ul>”;

130: }

131: //free result

132: mysqli_free_result($get_fax_res);

133:

134: //get all email

135: $get_email_sql = “SELECT email, type FROM email WHERE

136: master_id = ‘“.$safe_id.”’”;

137: $get_email_res = mysqli_query($mysqli, $get_email_sql)

138: or die(mysqli_error($mysqli));

139: if (mysqli_num_rows($get_email_res) > 0) {

140: $display_block .= “<p><strong>Email:</strong><br/>

141: <ul>”;

142:

143: while ($email_info = mysqli_fetch_array($get_email_res)) {

144: $email = stripslashes($email_info[‘email’]);

145: $email_type = $email_info[‘type’];

146:

147: $display_block .= “<li>$email ($email_type)</li>”;

148: }

149: $display_block .= “</ul>”;

150: }

151: //free result

152: mysqli_free_result($get_email_res);

153:

154: //get personal note

155: $get_notes_sql = “SELECT note FROM personal_notes WHERE

156: master_id = ‘“.$safe_id.”’”;

157: $get_notes_res = mysqli_query($mysqli, $get_notes_sql)

158: or die(mysqli_error($mysqli));

159:

160: if (mysqli_num_rows($get_notes_res) == 1) {

161: while ($note_info = mysqli_fetch_array($get_notes_res)) {

162: $note = nl2br(stripslashes($note_info[‘note’]));

163: }

164: $display_block .= “<p><strong>Personal Notes:</strong><br/>

165: $note</p>”;

166: }

167: //free result

168: mysqli_free_result($get_notes_res);

169: $display_block .= “<br/>

170: <p style=\”text-align:center\”>

171: <a href=\””.$_SERVER[‘PHP_SELF’].”\”>select another</a></p>”;

172: }

173: //close connection to MySQL

174: mysqli_close($mysqli);

175: ?>

The HTML template that displays the dynamically generated strings

176: <!DOCTYPE html>

177: <html>

178: <head>

179: <title>My Records</title>

180: </head>

181: <body>

182: <?php echo $display_block; ?>

183: </body>

184: </html>

Q) Explain the mechanism of Record-Deletion.

The record-deletion mechanism is nearly identical to the script used to view a record.

(In fact, you can just take the first 42 lines, paste them into a new file called delentry.php, and change “View” to “Delete” in lines 24 and 38. Starting with a new line 43, the remainder of the code for delentry.php is shown in the following List)

43: } else if ($_POST) {

44: //check for required fields

45: if ($_POST[‘sel_id’] == “”) {

46: header(“Location: delentry.php”);

47: exit;

48: }

49:

50: //create safe version of ID

51: $safe_id = mysqli_real_escape_string($mysqli, $_POST[‘sel_id’]);

52:

53: //issue queries

54: $del_master_sql = “DELETE FROM master_name WHERE

55: id = ‘“.$safe_id.”’”;

56: $del_master_res = mysqli_query($mysqli, $del_master_sql)

57: or die(mysqli_error($mysqli));

58:

59: $del_address_sql = “DELETE FROM address WHERE

60: id = ‘“.$safe_id.”’”;

61: $del_address_res = mysqli_query($mysqli, $del_address_sql)

62: or die(mysqli_error($mysqli));

63:

64: $del_tel_sql = “DELETE FROM telephone WHERE id = ‘“.$safe_id.”’”;

65: $del_tel_res = mysqli_query($mysqli, $del_tel_sql)

66: or die(mysqli_error($mysqli));

67:

68: $del_fax_sql = “DELETE FROM fax WHERE id = ‘“.$safe_id.”’”;

69: $del_fax_res = mysqli_query($mysqli, $del_fax_sql)

70: or die(mysqli_error($mysqli));

71:

72: $del_email_sql = “DELETE FROM email WHERE id = ‘“.$safe_id.”’”;

73: $del_email_res = mysqli_query($mysqli, $del_email_sql)

74: or die(mysqli_error($mysqli));

75:

76: $del_note_sql = “DELETE FROM personal_notes WHERE

77: id = ‘“.$safe_id.”’”;

78: $del_note_res = mysqli_query($mysqli, $del_note_sql)

79: or die(mysqli_error($mysqli));

80:

81: mysqli_close($mysqli);

82:

83: $display_block = “<h1>Record(s) Deleted</h1>

84: <p>Would you like to

85: <a href=\””.$_SERVER[‘PHP_SELF’].”\”>delete another</a>?</p>”;

86: }

87: ?>

88: <!DOCTYPE html>

89: <html>

90: <head>

91: <title>My Records</title>

92: </head>

93: <body>

94: <?php echo $display_block; ?>

95: </body>

96: </html>