Welcome to a tutorial on how to create a simple address book in PHP and MYSQL. Want to create your own address book? Or just want a project to learn more about PHP MYSQL? Well, read on for an example!
TABLE OF CONTENTS
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
PHP MYSQL ADDRESS BOOK
All right, let us now get into the steps of creating an address book with PHP and MYSQL.
STEP 1) ADDRESS BOOK DATABASE TABLE
CREATE TABLE `address_book` (
`id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`tel` varchar(255) NOT NULL,
`address` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `address_book`
ADD PRIMARY KEY (`id`),
ADD KEY `email` (`email`),
ADD KEY `name` (`name`),
ADD KEY `tel` (`tel`);
ALTER TABLE `address_book`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
Just a simple table to hold the address book entries.
Field | Description |
id |
Primary key, auto-increment. |
name |
Name of the person/company. |
email |
Email address. |
tel |
Telephone number. |
address |
Address. |
Feel free to change to your own needs – Split to first/last name, split into mobile number, home number, work number, etc…
STEP 2) ADDRESS BOOK PHP LIBRARY
<?php
class AddressBook {
// (A) CONSTRUCTOR - CONNECT TO DATABASE
private $pdo = null;
private $stmt = null;
public $lastID = null;
public $error = "";
function __construct () {
$this->pdo = new PDO(
"mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
DB_USER, DB_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
}
// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
function __destruct () {
if ($this->stmt!==null) { $this->stmt = null; }
if ($this->pdo!==null) { $this->pdo = null; }
}
// (C) EXECUTE SQL QUERY
function query ($sql, $data=null) : void {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($data);
}
// (D) GET ENTRY
function get ($id=null) {
$this->query(
"SELECT * FROM `address_book`" . ($id==null ? "" : " WHERE `id`=?"),
$id==null ? null : [$id]
);
return $id==null ? $this->stmt->fetchAll() : $this->stmt->fetch() ;
}
// (E) SAVE ENTRY
function save ($name, $email, $tel, $addr, $id=null) {
// (E1) NEW OR UPDATE
if ($id===null) {
$sql = "INSERT INTO `address_book` (`name`, `email`, `tel`, `address`) VALUES (?,?,?,?)";
$data = [$name, $email, $tel, $addr];
} else {
$sql = "UPDATE `address_book` SET `name`=?, `email`=?, `tel`=?, `address`=? WHERE `id`=?";
$data = [$name, $email, $tel, $addr, $id];
}
// (E2) RUN
$this->query($sql, $data);
return true;
}
// (F) DELETE ENTRY
function del ($id) {
$this->query("DELETE FROM `address_book` WHERE `id`=?", [$id]);
return true;
}
}
// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (H) ADDRESS BOOK OBJECT
$_AB = new AddressBook();
This looks massive at first, but keep calm and look carefully.
- (A, B, H) When
$_AB = new AddressBook()
is created, the constructor automatically connects to the database. The destructor closes the connection. - (C)
query()
A helper function to run an SQL query. - (D to F) There are only 3 address book functions!
get()
Get address book entries.save()
Add or update an address book entry.del()
Delete an address book entry.
- (G) Remember to change the database settings to your own.
STEP 3) ADDRESS BOOK AJAX HANDLER
<?php
if (isset($_POST["req"])) {
require "2-lib.php";
switch ($_POST["req"]) {
// (A) GET ENTRY/ENTRIES
case "get":
echo json_encode($_AB->get(
isset($_POST["id"]) ? $_POST["id"] : null
));
break;
// (B) SAVE ENTRY
case "save":
$_AB->save(
$_POST["name"], $_POST["email"], $_POST["tel"], $_POST["addr"],
isset($_POST["id"]) ? $_POST["id"] : null
);
echo "OK";
break;
// (C) DELETE ENTRY
case "del":
$_AB->del($_POST["id"]);
echo "OK";
break;
}
}
Next, we have an “AJAX handler”. How it works is actually very simple.
- Just send a request
$_POST["req"]
followed by the required parameters. - For example, send
$_POST["req"] = "del"
and$_POST["id"] = 123
to delete address book entry 123.
Yep, this script quite literally “maps” requests to library functions.
STEP 4) ADDRESS BOOK PAGE
4A) THE HTML
<!-- (A) ADDRESS BOOK LIST -->
<div id="abWrap">
<div id="abAdd" class="row" onclick="ab.tog(true)">+</div>
<div id="abList"></div>
</div>
<!-- (B) ADDRESS BOOK ENTRY -->
<form id="abForm" class="hide" onsubmit="return ab.save()">
<label>Name</label>
<input type="text" id="abName" required>
<label>Email</label>
<input type="email" id="abEmail" required>
<label>Telephone</label>
<input type="text" id="abTel" required>
<label>Address</label>
<textarea id="abAddr" required></textarea>
<input type="hidden" id="abID">
<input type="button" value="Back" onclick="ab.tog()">
<input type="submit" value="Save">
</form>
This is not the best-looking interface design, but it should be pretty self-explanatory.
<div id="abWrap">
List of address book entries. We will use Javascript to get the entries from the server and draw the HTML list.<form id="abForm">
Address book entry form.
4B) JAVASCRIPT – INITIALIZE
var ab = {
// (A) HTML ELEMENTS
hWrap : null, // address book list wrapper
hList : null, // address book list
hForm : null, // address book form
hfName : null, // name field
hfEmail : null, // email field
hfTel : null, // tel field
hfAddr : null, // address field
hfID : null, // id field
// (B) INIT - GET HTML ELEMENTS + LOAD LIST
init : () => {
ab.hWrap = document.getElementById("abWrap");
ab.hList = document.getElementById("abList");
ab.hForm = document.getElementById("abForm");
ab.hfName = document.getElementById("abName");
ab.hfEmail = document.getElementById("abEmail");
ab.hfTel = document.getElementById("abTel");
ab.hfAddr = document.getElementById("abAddr");
ab.hfID = document.getElementById("abID");
ab.list();
},
// (C) HELPER - AJAX FETCH
ajax : (data, after) => {
// (C1) FORM DATA
let form = new FormData();
for (let [k,v] of Object.entries(data)) { form.append(k,v); }
// (C2) AJAX FETCH
fetch("3-ajax.php", { method:"post", body:form })
.then(res => res.text())
.then(res => after(res))
.catch(err => console.error(err));
},
// ...
};
window.onload = ab.init;
To not overwhelm yourself with a whole load of Javascript, study it section by section. The first couple of sections are pretty much “baseline” and “initialize”.
var ab = {}
contains all the mechanics of the address book.- (A & B) On window load,
ab.init()
will run. Basically, get all the HTML elements. - (C)
ab.ajax()
is a helper function that does an AJAX call to3-ajax.php
.
4C) JAVASCRIPT – LIST & TOGGLE
// (D) TOGGLE HTML SECTION
tog : id => {
// (D1) ADD/EDIT ADDRESS BOOK ENTRY
if (id) {
ab.hWrap.classList.add("hide");
ab.hForm.classList.remove("hide");
ab.hForm.reset();
if (Number.isInteger(id)) {
ab.ajax({ req : "get", id : id }, e => {
e = JSON.parse(e);
ab.hfName.value = e.name;
ab.hfEmail.value = e.email;
ab.hfTel.value = e.tel;
ab.hfAddr.value = e.address;
ab.hfID.value = id;
});
} else { ab.hfID.value = ""; }
}
// (D2) SHOW ADDRESS BOOK LIST
else {
ab.hForm.classList.add("hide");
ab.hWrap.classList.remove("hide");
}
},
// (E) LOAD ADDRESS BOOK LIST
list : () => ab.ajax({ req : "get" }, entries => {
ab.tog();
entries = JSON.parse(entries);
ab.hList.innerHTML = "";
if (entries.length>0) { for (let e of entries) {
let row = document.createElement("div");
row.className = "row";
row.innerHTML = `<div class="rInfo">
<div class="rOne">${e.name}</div>
<div class="rTwo">${e.email} | ${e.tel}</div>
<div class="rTwo">${e.address}</div>
</div>
<button class="rBtn" onclick="ab.del(${e.id})">X</button>
<button class="rBtn" onclick="ab.tog(${e.id})">✎</button>`;
ab.hList.appendChild(row);
}}
}),
Remember that the HTML page has an “address book list” and “address book form”?
- (D)
ab.tog()
Pretty much toggles between the list and form. - (E)
ab.list()
Shows the list of address book entries.
4D) JAVASCRIPT – SAVE & DELETE
// (F) SAVE ADDRESS BOOK ENTRY
save : () => {
// (F1) FORM DATA
let data = {
req : "save",
name : ab.hfName.value,
email : ab.hfEmail.value,
tel : ab.hfTel.value,
addr : ab.hfAddr.value,
id : ab.hfID.value
};
if (data.id=="") { delete data.id; }
// (F2) AJAX SAVE
ab.ajax(data, res => {
if (res=="OK") { ab.list(); }
else { alert(res); }
});
return false;
},
// (G) DELETE ADDRESS BOOK ENTRY
del : id => { if (confirm("Delete entry?")) {
ab.ajax({ req : "del", id : id }, res => {
if (res=="OK") { ab.list(); }
else { alert(res); }
});
}}
Lastly, save and delete address book entries.
- (F)
ab.save()
Save an address book entry when the form is submitted. - (G)
ab.del()
To delete an address book entry.
EXTRAS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
BAREBONES SYSTEM ONLY
Yes, it’s working. But many more things can be added to make the project better – Login protection, export to list, send the contact via email, better user interface, and so much more… But that is your homework. I shall leave a couple of links below that may help.
LINKS & REFERENCES
- How to Develop a Web Application With PHP MySQL – Code Boxx
- Simple AJAX examples with PHP – Code Boxx
- Login Without Database – Code Boxx
- PDO Manual – PHP
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!
ADDRESS BOOK
Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘test.address_book’ doesn’t exist in D:\CakePHP\htdocs\direktori\2-lib-address-book.php:67 Stack trace: #0 D:\CakePHP\htdocs\direktori\2-lib-address-book.php(67): PDOStatement->execute(NULL) #1 D:\CakePHP\htdocs\direktori\3-list.php(50): AddressBook->get() #2 {main} thrown in D:\CakePHP\htdocs\direktori\2-lib-address-book.php on line 67
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘test.address_book’ doesn’t exist
https://code-boxx.com/faq/#no-db