Database
Databases
Two of the most popular databases are MySQL and MS SQL Server. The syntax for each can be a little different.
Creating A MySQL Database
First we will create a MySQL database. In the following example we are going to create a database named refdev. In MySQL Workbench with the bottom tab "Schemas" selected, do the following:
Under SCHEMAS, right click and choose "Create Schema"
Enter Schema Name: refdev
, Default Charset
, Default Collation
Click Apply
This creates an empty database. In order for it to be functional, tables must be added and populated with data.
Adding Tables
The following will create three tables: PhoneBook, PhoneNumber, and PhoneType.
| PhoneBook | A table to represent people in the phone book. |
|---|---|
| PhoneNumber | A table that represents the phone number(s) associated with a person. Note that this is a many to one relationship. |
| PhoneType | A table that represnts the type of phone number i.e. Cell, Home, Work, etc... |
DROP TABLE IF EXISTS PhoneBook;
CREATE TABLE PhoneBook (
ItemId INT NOT NULL AUTO_INCREMENT
, FirstName VARCHAR (100)
, LastName VARCHAR (100)
, PRIMARY KEY (ItemId)
);
DROP TABLE IF EXISTS PhoneNumber;
CREATE TABLE PhoneNumber (
NumberId INT NOT NULL AUTO_INCREMENT
, ItemId INT NOT NULL
, PhoneTypeId INT NOT NULL
, PhoneNumber VARCHAR (200)
, Notes VARCHAR (255)
, PRIMARY KEY (NumberId)
);
DROP TABLE IF EXISTS PhoneType;
CREATE TABLE PhoneType (
TypeId INT NOT NULL AUTO_INCREMENT
, Name VARCHAR (50)
, PRIMARY KEY (TypeId)
);
MySQL Insert / Update Stored Procedure
The following stored procedures can be used to input new data or update existing data. If the identifier parameter is negative, then the operation is input. If the identifier is a positive integer, then the operation is update.
DROP PROCEDURE IF EXISTS piuPhoneBook;
DELIMITER $$
CREATE PROCEDURE piuPhoneBook (
lItemId int
, lFirstName varchar(100)
, lLastName varchar(100)
)
BEGIN
DECLARE lNewID INT DEFAULT -1;
IF (lItemId = -1) THEN
BEGIN
INSERT INTO PhoneBook (FirstName,LastName)
SELECT lFirstName,lLastName;
SET lNewID=LAST_INSERT_ID();
END;
ELSE
BEGIN
UPDATE PhoneBook
SET FirstName=lFirstName
,LastName=lLastName
WHERE ItemId=lItemId;
SET lNewID=ROW_COUNT();
END;
END IF;
END$$
DROP PROCEDURE IF EXISTS piuPhoneNumber;
DELIMITER $$
CREATE PROCEDURE piuPhoneNumber (
lNumberId int
, lItemId int
, lPhoneTypeId int
, lPhoneNumber varchar(200)
, lNotes varchar(255)
)
BEGIN
DECLARE lNewID INT DEFAULT -1;
IF (lNumberId = -1) THEN
BEGIN
INSERT INTO PhoneNumber (ItemId,PhoneTypeId,PhoneNumber,Notes)
SELECT lItemId,lPhoneTypeId,lPhoneNumber,lNotes;
SET lNewID=LAST_INSERT_ID();
END;
ELSE
BEGIN
UPDATE PhoneNumber
SET ItemId=lItemId
,PhoneTypeId=lPhoneTypeId
,PhoneNumber=lPhoneNumber
,Notes=lNotes
WHERE NumberId=lNumberId;
SET lNewID=ROW_COUNT();
END;
END IF;
END$$
DROP PROCEDURE IF EXISTS piuPhoneType;
DELIMITER $$
CREATE PROCEDURE piuPhoneType (
lTypeId int
, lName varchar(50)
)
BEGIN
DECLARE lNewID INT DEFAULT -1;
IF (lTypeId = -1) THEN
BEGIN
INSERT INTO PhoneType (Name)
SELECT lName;
SET lNewID=LAST_INSERT_ID();
END;
ELSE
BEGIN
UPDATE PhoneType
SET Name=lName
WHERE TypeId=lTypeId;
SET lNewID=ROW_COUNT();
END;
END IF;
END$$
MySQL List Stored Procedure
The following stored procedures are used to list the contents of the table. If the identifier is negative, the procedure will list all of the contents. If the identifier is positive, the procedure will only list the contents that match its id.
DROP PROCEDURE IF EXISTS plPhoneBook;
DELIMITER $$
CREATE PROCEDURE plPhoneBook (lItemId INT)
BEGIN
IF (lItemId = -1) THEN
BEGIN
SELECT ItemId, FirstName, LastName
FROM PhoneBook;
END;
ELSE
BEGIN
SELECT ItemId, FirstName, LastName
FROM PhoneBook
WHERE ItemId=lItemId;
END;
END IF;
END$$
DROP PROCEDURE IF EXISTS plPhoneNumber;
DELIMITER $$
CREATE PROCEDURE plPhoneNumber (lNumberId INT, lItemId INT)
BEGIN
IF (lNumberId = -1) THEN
BEGIN
SELECT PN.NumberId, PN.ItemId, PN.PhoneTypeId, PT.Name, PN.PhoneNumber, PN.Notes
FROM PhoneNumber PN
INNER JOIN PhoneType PT ON PN.PhoneTypeID=PT.TypeId
WHERE PN.ItemId = lItemId;
END;
ELSE
BEGIN
SELECT PN.NumberId, PN.ItemId, PN.PhoneTypeId, PT.Name, PN.PhoneNumber, PN.Notes
FROM PhoneNumber PN
INNER JOIN PhoneType PT ON PN.PhoneTypeID=PT.TypeId
WHERE PN.NumberId=lNumberId;
END;
END IF;
END$$
DROP PROCEDURE IF EXISTS plPhoneType;
DELIMITER $$
CREATE PROCEDURE plPhoneType (lTypeId INT)
BEGIN
IF (lTypeId = -1) THEN
BEGIN
SELECT TypeId, Name
FROM PhoneType;
END;
ELSE
BEGIN
SELECT TypeId, Name
FROM PhoneType
WHERE TypeId=lTypeId;
END;
END IF;
END$$
MySQL Delete Stored Procedures
The following stored procedures will delete a table entry where the identifier matches the id.
DROP PROCEDURE IF EXISTS pdPhoneBook;
DELIMITER $$
CREATE PROCEDURE pdPhoneBook (lItemId INT)
BEGIN
DELETE FROM PhoneBook WHERE ItemId=lItemId;
END$$
DROP PROCEDURE IF EXISTS pdPhoneNumber;
DELIMITER $$
CREATE PROCEDURE pdPhoneNumber (lNumberId INT)
BEGIN
DELETE FROM PhoneNumber WHERE NumberId=lNumberId;
END$$
DROP PROCEDURE IF EXISTS pdPhoneType;
DELIMITER $$
CREATE PROCEDURE pdPhoneType (lTypeId INT)
BEGIN
DELETE FROM PhoneType WHERE TypeId=lTypeId;
END$$
MySQL Rebuild Stored Procedures
The following stored procedure first destroys the table, then rebuilds it.
DROP PROCEDURE IF EXISTS prPhoneType;
DELIMITER $$
CREATE PROCEDURE prPhoneType()
BEGIN
DROP TABLE IF EXISTS PhoneType;
CREATE TABLE PhoneType (
TypeId INT NOT NULL AUTO_INCREMENT
, Name VARCHAR (50)
, PRIMARY KEY (TypeId)
);
END$$
Connecting To Database
Before anything can be done to the database, the code must connect with it. This can be a tricky thing depending upon the security of the database.
/* YOU SHOULD ENABLE ERROR REPORTING FOR mysqli BEFORE ATTEMPTING TO MAKE A CONNECTION */
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'MyUserName', 'MyPassword', 'DatabaseName');
/* SET THE DESIRED CHARSET AFTER ESTABLISHING A CONNECTION */
$mysqli->set_charset('utf8');
printf("Success... %s\n", $mysqli->host_info);
Adding Data
To add data to the database we will use the stored procedures entered above. The first table to populate is the PhoneType table. This is because it is needed to create the dropdown for adding the phone number. For this example instead of entering the data using a form field, we'll use an xml file. Because this can be called over and over, the table should be deleted and recreated before each population.
<script>
window.addEventListener("DOMContentLoaded", (event) => {
// DECLARE BUTTON CONSTANT
const btnAddPhoneType = document.getElementById("btnAddPhoneType");
// CREATE BUTTON EVENT HANDLER
if (btnAddPhoneType) {
btnAddPhoneType.addEventListener("click", function() {
popPhoneTypeXML();
},false);
} else {console.log("Does not exist yet");}
// PACKAGE THE PARAMETER AND MAKE AJAX CALL TO AjaxDatabase1.php
function popPhoneTypeXML() {
var data = {};
data.xml_file = "phone_type.xml";
var json_str = JSON.stringify(data);
// Create an XML HTTP Object
if (window.XMLHttpRequest) {xmlhttp = new XMLHttpRequest();}// For IE7+, All
else {xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");} // For IE6-5
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
// CALL THE RESPONSE PROCESSING FUNCTION
ProcessResponse(xmlhttp.responseText);
}
}
xmlhttp.open("POST","AjaxDatabase1.php",true);
xmlhttp.setRequestHeader("Content-Type","application/json;charset=UTF-8");
xmlhttp.send(json_str);
};
// THE RESPONSE PROCESSING FUNCTION
function ProcessResponse(respText) {
var divResult = document.getElementById("result1");
var rtObj = JSON.parse(respText);
var resp_str = "";
resp_str = "Count: "+ rtObj.count +"<br/>";
divResult.innerHTML = resp_str;
};
});
</script>
<button id="btnAddPhoneType" class="btn btn-bs-primary">Add Phone Type</button>
<div id="result1"></div>
<?xml version="1.0" encoding="utf-8"?>
<PhoneTypes>
<PhoneType>
<Name>Cell</Name>
</PhoneType>
<PhoneType>
<Name>Work</Name>
</PhoneType>
<PhoneType>
<Name>Home</Name>
</PhoneType>
<PhoneType>
<Name>Private</Name>
</PhoneType>
</PhoneTypes>
<?php
// RETRIEVE THE JSON DATA SENT FROM THE CLIENT WHICH IS THE XML FILE NAME
$json = file_get_contents('php://input');
$data = json_decode($json);
$phone_type_data = $data->xml_file;
$typeId = -1;
// CONNECT TO DATABASE
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli('localhost', 'dev', 'dirkMan69', 'refdev');
/* SET THE DESIRED CHARSET AFTER ESTABLISHING A CONNECTION */
$connection ->set_charset('utf8');
// CALL REBUILDING STORED PROCEDURE
$result = $connection->query('CALL prPhoneType');
$count = 0;
$types = '';
$errorStr = '';
// OPEN THE XML FILE
$xml = simplexml_load_file( $phone_type_data ) or die ("Cannot open XML file: " . $phone_type_data);
// LOOP THROUGH CONTENTS
foreach($xml->children() as $PhoneType) {
// CALL SP TO INSERT DATA INTO PhoneType TABLE
$prepared = $connection->prepare('CALL piuPhoneType(?,?)');
$result = $prepared->bind_param("ss", $typeId, $PhoneType->Name);
$result2 = $prepared->execute();
$count++;
}
$prepared->close();
$connection->close();
$output = array('count' => $count,
'errorStr' => $errorStr);
// SEND JSON RESPONSE
header('Content-Type:application/json');
echo json_encode($output, JSON_FORCE_OBJECT);
?>
Clicking the following button will trigger an AJAX call to the server which will then call the stored procedure prPhoneType which will destroy the existing PhoneType table and populate it with data from the XML file phone_type.xml. There's no real pragmatic reason for doing this other than to show a different means of populating a database table other than manual input.
The PhoneType table is more or less a static table whose contents will rarely change. The rest of the tables require user input. The sequence of operations should be defined in order to properly populate those tables. First let's look at the different form fields and what actions might be needed for each one.
| Control | Defined | Type | Action |
|---|---|---|---|
| ddl_phonebook | A list of phone book entries | Drop Down List | On change calls loadPhoneBookEntry() which makes an AJAX call to the PHP file AxDB_My_GetPhoneBook.php. This file first calls the stored procedure plPhoneBook which gets the first and last name. Next it calls the stored procedure plPhoneNumber which gets the list of phone numbers associated with the selected phone book entry. The retrieved data is returned as JSON |
| txtFirstName | A text field to hold the first name | Text Box | None |
| txtLastName | A text field to hold the last name | Text Box | None |
| ddl_phonenumber | A list of of phone numbers associated with the selected phone book entry | Drop Down List | This list is hidden until a phone book entry has been selected. Selecting a phone number selects a phone type and populates the phone number text box. |
| ddl_phonetype | A list of different phone number types | Drop Down List | None |
| txtPhoneNumber | A text field to enter or edit a phone number | Formatted Text Box | None |
| btnAddPhoneNumber | A button to add or update a phone book entry | Button |
Formatting The Phone Number Input
In order to make the format of the phone number to be uniform, the phone number input text field, txtPhoneNumber, forces the user to enter the number in a standardized manner.
<input type="text" id="txtPhoneNumber" name="txtPhoneNumber" placeholder="+1 (___) ___-____" data-slots="_" class="cfrm_text" minlength="3" maxlength="30" />
/* CODE TO MAKE PHONE MASK WORK */
for (const el of document.querySelectorAll("[placeholder][data-slots]")) {
const pattern = el.getAttribute("placeholder"),
slots = new Set(el.dataset.slots || "_"),
prev = (j => Array.from(pattern, (c,i) => slots.has(c)? j=i+1: j))(0),
first = [...pattern].findIndex(c => slots.has(c)),
accept = new RegExp(el.dataset.accept || "\\d", "g"),
clean = input => {
input = input.match(accept) || [];
return Array.from(pattern, c =>
input[0] === c || slots.has(c) ? input.shift() || c : c
);
},
format = () => {
const [i, j] = [el.selectionStart, el.selectionEnd].map(i => {
i = clean(el.value.slice(0, i)).findIndex(c => slots.has(c));
return i<0? prev[prev.length-1]: back? prev[i-1] || first: i;
});
el.value = clean(el.value).join``;
el.setSelectionRange(i, j);
back = false;
};
let back = false;
el.addEventListener("keydown", (e) => back = e.key === "Backspace");
el.addEventListener("input", format);
el.addEventListener("focus", format);
el.addEventListener("click", format);
el.addEventListener("blur", () => el.value === pattern && (el.value=""));
};
All Code
<?php
// CONNECT TO DATABASE
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$connection ->set_charset('utf8');
// CALL LIST STORED PROCEDURE FOR PHONE TYPE
$typeId = -1;
$prepared = $connection->prepare('CALL plPhoneType(?);');
$prepared->bind_param("i", $typeId);
$prepared->execute();
$result_pt = $prepared->get_result();
// CREATE DROP DOWN LIST OPTIONS FOR PHONE TYPE
while($row = $result_pt->fetch_assoc()) {
$ddl_pt_str .= '<option value="'. $row['TypeId'] .'">'. $row['Name'] .'</option>';
}
$prepared->close();
// CALL LIST STORED PROCEDURE FOR PHONE BOOK
$itemId = -1;
$prepared2 = $connection->prepare('CALL plPhoneBook(?);');
$prepared2->bind_param("i", $itemId);
$prepared2->execute();
$result_pb = $prepared2->get_result();
// CREATE DROP DOWN LIST OPTIONS FOR PHONE BOOK
while($row = $result_pb->fetch_assoc()) {
$ddl_pb_str .= '<option value="'. $row['ItemId'] .'">'. $row['FirstName'] .' '. $row['LastName'] .'</option>';
}
$prepared2->close();
$connection->close();
?>
<h2 id="ref_07">Phone Book Entry</h2>
<table class="def_table" style="width:100%;">
<tbody>
<tr>
<td colspan="2">
<select id="ddl_phonebook" name="ddl_phonebook" class="form-control input-sm">
<option value="-1">Select A Phone Book Entry</option>
<?php echo $ddl_pb_str; ?>
</select>
</td>
</tr>
<tr>
<td>
<input type="text" id="txtFirstName" name="txtFirstName" placeholder="First Name" class="form-control input-sm" minlength="3" maxlength="50" />
</td>
<td>
<input type="text" id="txtLastName" name="txtLastName" placeholder="Last Name" class="form-control input-sm" minlength="3" maxlength="50" />
</td>
</tr>
<tr>
<td colspan="2" style="height:4rem;">
<select id="ddl_phonenumber" name="ddl_phonenumber" class="form-control input-sm hidden_item">
<option value="-1">Select A Phone Number</option>
</select>
</td>
</tr>
<tr>
<td>
<select id="ddl_phonetype" name="ddl_phonetype" class="form-control input-sm">
<option value="-1">Select A Phone Type</option>
<?php echo $ddl_pt_str; ?>
</select>
</td>
<td>
<input type="text" id="txtPhoneNumber" name="txtPhoneNumber" placeholder="+1 (___) ___-____" data-slots="_" class="cfrm_text" minlength="3" maxlength="30" />
</td>
</tr>
<tr>
<td colspan="2">
<button id="btnAddPhoneNumber" class="btn btn-bs-primary">Add Phone Number</button>
</td>
</tr>
</tbody>
</table>
.hidden_item {
visibility:hidden;
opacity:0;
}
.revealed {
visibility:visible;
animation: show_me_slowly 1s;
}
.cfrm_text {
display:inline-block;
padding:0.375rem 0.75rem;
font-size:1rem;
font-weight:400;
line-height:1.5;
background-clip:padding-box;
border:1px solid #ced4da;
appearance:none;
border-radius:0.375rem;
transition: border-color 0.15s ease-in-out, box-shadow 0.15s ease-in-out;
}
const ddl_phonebook = document.getElementById("ddl_phonebook");
/* CREATE CHANGE EVENT FOR PHONE BOOK DROP DOWN LIST
************************************************************************************************/
if (ddl_phonebook) {
ddl_phonebook.addEventListener("change", function() {
loadPhoneBookEntry();
},false);
} else {console.log("Does not exist yet");}
/* AJAX FUNCTION TO LOAD PHONE BOOK ENTRY */
function loadPhoneBookEntry() {
let sel_ItemId = ddl_phonebook.options[ddl_phonebook.options.selectedIndex].value;
var data = {};
data.item_id = sel_ItemId;
var json_str = JSON.stringify(data);
// Create an XML HTTP Object
if (window.XMLHttpRequest) {xmlhttp = new XMLHttpRequest();}// For IE7+, All
else {xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");} // For IE6-5
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
PR_LoadPhoneBook(xmlhttp.responseText);
}
}
xmlhttp.open("POST","AxDB_My_GetPhoneBook.php",true);
xmlhttp.setRequestHeader("Content-Type","application/json;charset=UTF-8");
xmlhttp.send(json_str);
};
/* AJAX RESPONSE FUNCTION */
function PR_LoadPhoneBook(respText) {
let divResult = document.getElementById("result2");
let txtFirstName = document.getElementById("txtFirstName");
let txtLastName = document.getElementById("txtLastName");
let rtObj = JSON.parse(respText);
let tOption;
let dbStr = "";
txtFirstName.value = rtObj.firstName; /* SET FIRST NAME */
txtLastName.value = rtObj.lastName; /* SET LAST NAME */
ddl_phonenumber.classList.remove("hidden_item"); /* SHOW PHONE NUMBER LIST BOX */
ddl_phonenumber.classList.add("revealed");
Object.keys(rtObj.numbers).forEach(key => { /* LOOP THROUGH EACH ENTRY */
tOption = document.createElement("option"); /* CREATE A NEW DDL OPTION OBJ */
tOption.value = rtObj.numbers[key][0]; /* SET VALUE TO ID */
tOption.text = rtObj.numbers[key][2] +" - "+ rtObj.numbers[key][3]; /* SET TEXT TO TYPE NAME AND NUMBER */
ddl_phonenumber.appendChild(tOption); /* APPEND TO DDL */
});
divResult.innerHTML = "dbStr:" + dbStr;
};
<?php
include 'db_config.php';
$json = file_get_contents('php://input');
$data = json_decode($json);
$errorStr = '';
$firstName = '';
$lastName = '';
$numberId = -1;
$numberAr = array();
// CONNECT TO DATABASE
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$connection ->set_charset('utf8');
// GET PHONE BOOK DATA
$prepared = $connection->prepare('CALL plPhoneBook(?)');
$result1 = $prepared->bind_param("i", $data->item_id);
$result2 = $prepared->execute();
$result3 = $prepared->get_result();
while($row = $result3->fetch_assoc()) {
$firstName = $row['FirstName'];
$lastName = $row['LastName'];
}
$prepared->close();
// GET ASSOCIATED PHONE NUMBER DATA
$prepared = $connection->prepare('CALL plPhoneNumber(?,?)');
$result1 = $prepared->bind_param("ii", $numberId, $data->item_id);
$result2 = $prepared->execute();
$result3 = $prepared->get_result();
while($row = $result3->fetch_assoc()) { /* LOOP THROUGH PHONE NUMBERS */
/* ADD NUMBERS TO ARRAY */
$numberAr[] = array($row['NumberId'], $row['PhoneTypeId'], $row['PhoneNumber']);
}
$prepared->close();
$connection->close();
// CREATE RESPONSE JSON
$output = array('firstName' => $firstName,
'lastName' => $lastName,
'numbers' => $numberAr,
'errorStr' => $errorStr);
// SEND JSON RESPONSE
header('Content-Type:application/json');
echo json_encode($output, JSON_FORCE_OBJECT);
?>
const ddl_phonebook = document.getElementById("ddl_phonebook");
/* CREATE CHANGE EVENT FOR PHONE NUMBER DROP DOWN LIST
************************************************************************************************/
if (ddl_phonenumber) {
ddl_phonenumber.addEventListener("change", function() {
loadPhoneNumber();
},false);
} else {console.log("Does not exist yet");}
/* AJAX FUNCTION TO GET PHONE NUMBERS ASSOCIATED WITH PHONE BOOK SELECTION */
function loadPhoneNumber() {
let lNumber_Id = ddl_phonenumber.options[ddl_phonenumber.options.selectedIndex].value;
let data = {};
data.number_id = lNumber_Id;
var json_str = JSON.stringify(data);
// Create an XML HTTP Object
if (window.XMLHttpRequest) {xmlhttp = new XMLHttpRequest();}// For IE7+, All
else {xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");} // For IE6-5
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
PR_LoadPhoneNumber(xmlhttp.responseText);
}
}
xmlhttp.open("POST","AxDB_My_GetPhoneNumber.php",true);
xmlhttp.setRequestHeader("Content-Type","application/json;charset=UTF-8");
xmlhttp.send(json_str);
};
/* AJAX RESPONSE FUNCTION */
function PR_LoadPhoneNumber(respText) {
let txtPhoneNumber = document.getElementById("txtPhoneNumber");
var divResult = document.getElementById("result2");
var rtObj = JSON.parse(respText);
var resp_str = "";
ddl_phonetype.options.selectedIndex = -1; /* CLEAR SELECTION */
for (let i=0;i<ddl_phonetype.options.length;i++) { /* LOOP THROUGH OPTIONS */
if (ddl_phonetype.options[i].value == rtObj.PhoneTypeId) { /* CHECK FOR MATCH */
ddl_phonetype.options[i].selected = true; /* SELECT OPTION */
break; /* EXIT LOOP */
}
}
txtPhoneNumber.value = rtObj.PhoneNumber;
resp_str = "PhoneTypeId: "+ rtObj.PhoneTypeId +"<br/>";
resp_str += "PhoneNumber: "+ rtObj.PhoneNumber +"<br/>";
divResult.innerHTML = resp_str;
};
<?php
include 'db_config.php';
$json = file_get_contents('php://input');
$data = json_decode($json);
$errorStr = '';
$itemId = -1;
$phone_type_id = '';
$phone_number = '';
// CONNECT TO DATABASE
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$connection ->set_charset('utf8');
$prepared = $connection->prepare('CALL plPhoneNumber(?,?)');
$result1 = $prepared->bind_param("ii", $data->number_id, $itemId);
$result2 = $prepared->execute();
$result3 = $prepared->get_result();
while($row = $result3->fetch_assoc()) {
$phone_type_id = $row['PhoneTypeId'];
$phone_number = $row['PhoneNumber'];
}
$prepared->close();
$connection->close();
$output = array('PhoneTypeId' => $phone_type_id,
'PhoneNumber' => $phone_number,
'errorStr' => $errorStr);
// SEND JSON RESPONSE
header('Content-Type:application/json');
echo json_encode($output, JSON_FORCE_OBJECT);
?>
Phone Book Scenarios
-
Empty Phone Book Add Entry: No entries exist and so there is no phone book entries to select.
User enters a first and last name, then selects a phone type, then enters a phone number, then clicks on Add Phone Number button.
State Of Controls:
ddl_phonebook: -1
txtFirstName: (First Name Text)
txtLastName: (Last Name Text)
ddl_phonenumber: -1
ddl_phonetype: (Positive Integer)
txtPhoneNumber: (Phone Number Text)
-
Non-Empty Phone Book Add Entry: There are entries in the Phone Book, but we're adding a new entry.
User enters a first and last name, then selects a phone type, then enters a phone number, then clicks on Add Phone Number button.
State Of Controls:
ddl_phonebook: -1
txtFirstName: (First Name Text)
txtLastName: (Last Name Text)
ddl_phonenumber: -1
ddl_phonetype: (Positive Integer)
txtPhoneNumber: (Phone Number Text)
-
Phone Book Add Number: There are entries in the Phone Book, but we're adding a new number to an existing entry.
User selects an entry, then selects a phone type, then enters a phone number, then clicks on Add Phone Number button.
State Of Controls:
ddl_phonebook: (Positive Integer)
txtFirstName: (First Name Text From Selection)
txtLastName: (Last Name Text From Selection)
ddl_phonenumber: -1
ddl_phonetype: (Positive Integer)
txtPhoneNumber: (Phone Number Text)
-
Phone Book Edit Number: A phone book entry is selected, but a number is not selected.
State Of Controls:
ddl_phonebook: (Positive Integer)
txtFirstName: (First Name Text From Selection)
txtLastName: (Last Name Text From Selection)
ddl_phonenumber: (Positive Integer)
ddl_phonetype: (Positive Integer From Selection)
txtPhoneNumber: (Phone Number Text From Selection)
if (btnAddPhoneNumber) {
btnAddPhoneNumber.addEventListener("click", function() {
addPhoneNumber();
},false);
} else {console.log("Does not exist yet");}
function addPhoneNumber() {
let txtFirstName = document.getElementById("txtFirstName");
let txtLastName = document.getElementById("txtLastName");
let txtPhoneNumber = document.getElementById("txtPhoneNumber");
let data = {};
data.item_id = ddl_phonebook.options[ddl_phonebook.options.selectedIndex].value;
data.type_id = ddl_phonetype.options[ddl_phonetype.options.selectedIndex].value;
data.first_name = txtFirstName.value;
data.last_name = txtLastName.value;
if (ddl_phonenumber.classList.contains("hidden_item")) {
data.number_id = -1;
} else {
data.number_id = ddl_phonenumber.options[ddl_phonenumber.options.selectedIndex].value;
}
let json_str = JSON.stringify(data);
// Create an XML HTTP Object
if (window.XMLHttpRequest) {xmlhttp = new XMLHttpRequest();} // For IE7+, All
else {xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");} // For IE6-5
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
PR_AddPhoneNumber(xmlhttp.responseText);
}
}
xmlhttp.open("POST","AxDB_My_AddPhoneBook.php",true);
xmlhttp.setRequestHeader("Content-Type","application/json;charset=UTF-8");
xmlhttp.send(json_str);
};
<?php
include 'db_config.php';
$json = file_get_contents('php://input');
$data = json_decode($json);
$errorStr = '';
$newPhoneId = -1;
$notes = 'Entered via ADB2';
// CONNECT TO DATABASE
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$connection ->set_charset('utf8');
// ADD or UPDATE PhoneBook
$prepared = $connection->prepare('CALL piuPhoneBook(?,?,?)');
$result1 = $prepared->bind_param("iss", $data->item_id, $data->first_name, $data->last_name);
$result2 = $prepared->execute();
$result3 = $prepared->get_result();
while($row = $result3->fetch_assoc()) {
$newPhoneId = $row['NewID'];
}
$prepared->close();
// ADD ENTRY TO PHONE NUMBER
$prepared = $connection->prepare('CALL piuPhoneNumber(?,?,?,?,?)');
$result4 = $prepared->bind_param("iiiss", $data->number_id, $newPhoneId, $data->type_id, $data->phone_number, $notes);
$result5 = $prepared->execute();
$prepared->close();
$connection->close();
$output = array('newId' => $newPhoneId,
'errorStr' => $errorStr);
// SEND JSON RESPONSE
header('Content-Type:application/json');
echo json_encode($output, JSON_FORCE_OBJECT);
?>
function PR_AddPhoneNumber(respText) {
let divResult = document.getElementById("result2");
let txtFirstName = document.getElementById("txtFirstName");
let txtLastName = document.getElementById("txtLastName");
let txtPhoneNumber = document.getElementById("txtPhoneNumber");
let rtObj = JSON.parse(respText);
let response_text = "";
// CLEAR THE FORM
txtFirstName.value = "";
txtLastName.value = "";
txtPhoneNumber.value = "";
ddl_phonetype.options.selectedIndex = 0; // 0 IS FIRST ITEM
// PHONE BOOK
if (rtObj.newId == ddl_phonebook.options[ddl_phonebook.options.selectedIndex].value) {
response_text = "Existing Phone Book Entry";
if (ddl_phonenumber.options.selectedIndex == 0) {
response_text += "<br/>New Phone Number Added";
} else {
response_text += "<br/>Existing Phone Number Updated";
}
ddl_phonebook.options.selectedIndex = 0;
} else {
response_text = "New Phone Book Entry Added";
// UPDATE THE PHONE BOOK DROP DOWN
update_phone_book_ddl();
}
// DELETE THE PHONE NUMBER ENTRIES EXCEPT FIRST ITEM
for (let i=ddl_phonenumber.options.length; i>0; i--) {
ddl_phonenumber.options[i] = null;
}
ddl_phonenumber.classList.remove("revealed");
ddl_phonenumber.classList.add("hidden_item");
divResult.innerHTML = response_text;
};
function update_phone_book_ddl() {
// Create an XML HTTP Object
if (window.XMLHttpRequest) {xmlhttp = new XMLHttpRequest();}// For IE7+, All
else {xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");} // For IE6-5
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
PR_Repop_PhoneBook(xmlhttp.responseText);
}
}
xmlhttp.open("POST","AxDB_My_GetPhoneBookAll.php",true);
xmlhttp.setRequestHeader("Content-Type","application/json;charset=UTF-8");
xmlhttp.send();
};
function PR_Repop_PhoneBook(respText) {
let divResult = document.getElementById("result2");
let rtObj = JSON.parse(respText);
let tOption;
let dbStr = "";
// DELETE THE PHONE BOOK ENTRIES EXCEPT FIRST ITEM
for (let i=ddl_phonebook.options.length; i>0; i--) {
ddl_phonebook.options[i] = null;
}
Object.keys(rtObj.phonebook).forEach(key => {
tOption = document.createElement("option");
tOption.value = rtObj.phonebook[key][0];
tOption.text = rtObj.phonebook[key][1] +" "+ rtObj.phonebook[key][2];
ddl_phonebook.appendChild(tOption);
});
//divResult.innerHTML = respText;
};