Simple internal phone list with php and mysql

Here is a simple piece of PHP code to create a internal phone list.  It has no styles applied to it so you can integrate it to your own site.  I used MySQL for the database.  You need to create a database called telephone and create a table in that database called employee.  You can also modify the db.php file if you wish to use another name.  The following sql will create the database for you.

CREATE DATABASE telephone;
USE telephone;
CREATE TABLE `employees` (
 `number` varchar(10) NOT NULL,
 `name` varchar(50) NOT NULL,
 `department` varchar(50) NOT NULL,
 `extension` varchar(10) NOT NULL,
 PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employees` (`number`, `name`, `department`, `extension`) VALUES
('1', 'Frank', 'Finance', '200'),
('2', 'Joe', 'HR', '201');

Next we need to create a php file that will connect to our database.  Replace the server name, user, and password with ones relevant to your setup.  Here is the contents of db.php

<?php
$con = mysqli_connect(<sql server address>,<user>,<password>,'telephone');
if (!$con)
 {
 die('Could not connect: ' . mysqli_error($con));
 }
mysqli_select_db($con,"telephone");

The getuser.php file is where the database query actually happens.  You will see the include (‘db.php’) at the top of the file to connect to our data.  This file returns our results as a table.

<?php
include ('db.php');
$q = $_GET['q'];
$sql="SELECT * FROM employees WHERE name LIKE '%" . $q . "%' "
 . "OR department LIKE '%" . $q . "%' order by name";
$result = mysqli_query($con,$sql);
/* determine number of rows result set */
 $row_cnt = $result->num_rows;
 if ($row_cnt > 0)
 {
 
echo "<table>
<tr>
<th></th>
<th>Name</th>
<th>Department</th>
<th>Telephone</th>
</tr>";
while($row = mysqli_fetch_array($result))
 {
 if (file_exists("images/employees/" . $row['number'] . ".jpg")) {
 $emp_pic = "images/" . $row['number'] . ".png";
} else {
 $emp_pic = "images/avatar.png";
}
 echo "<tr>";
 echo "<td><img style='height: 50px;' src='" . $emp_pic . "'/></td>";
 echo "<td>" . $row['name'] . "</td>";
 echo "<td>" . $row['department'] . "</td>";
 echo "<td>" . $row['extension'] . "</td>";
 echo "</tr>";
 }
echo "</table>";
 }
 else {
 echo "<label>Nothing found...</label>";
 }
mysqli_close($con);
?>

The last file is the part is the index.php.  This is where the user enters their search.  It has a text box that makes an ajax call to getuser.php and the results are displayed in the div ‘livesearch’.

<!DOCTYPE html>
<html>
 <head>
 <meta charset="UTF-8">
 <title>Internal Phone List</title>
 <script>
 function showResult(str)
 {
 if (str.length == 0)
 {
 document.getElementById("livesearch").innerHTML = "";
 document.getElementById("livedetails").innerHTML = "";
return;
 }
 if (str.length < 2)
 {
 document.getElementById("livesearch").innerHTML = "<label>Searching list...</label>";
 document.getElementById("livedetails").innerHTML = "";
 // document.getElementById("livesearch").style.border = "0px";
 return;
 }
if (window.XMLHttpRequest)
 {// code for IE7+, Firefox, Chrome, Opera, Safari
 xmlhttp = new XMLHttpRequest();
 }
 else
 {// code for IE6, IE5
 xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
 }
 xmlhttp.onreadystatechange = function()
 {
 if (xmlhttp.readyState == 4 && xmlhttp.status == 200)
 {
 document.getElementById("livesearch").innerHTML = xmlhttp.responseText;
 document.getElementById("livedetails").innerHTML = "";
 }
 }
 xmlhttp.open("GET", "getuser.php?q=" + str, true);
 xmlhttp.send();
 }
</script>
 </head>
 <body>
 <p class="mytext">Enter the first name, surname, department, or room to search for an extension number.</p>
 <form id='contactform' action="javascript:void(0);">
 <div class='field'>
 <label for='searchterm'>Search:</label>
 <input onclick="this.select()" name="searchterm" class="input" type="text" onkeyup="showResult(this.value)">
 </div>
<br /><br />
 
 <div id="livesearch"></div>
 </form>
</body>
</html>

The code also allows for employee images to appear.  If you have an image with a file name that matches the employee number it will appear on the list.  For my example I would require images called 1.png and 2.png.  If a matching image is not found then avatar.png is used.

Advertisements

3 thoughts on “Simple internal phone list with php and mysql

  1. Words of Little Relevance

    I like this although I’ve having difficulty getting it to work. As I enter the first char into the input box I get the “Searching list…” but nothing else.

    Running:
    Linux kernel 3.12.5-031205-generic #201312120254 SMP Thu Dec 12 07:55:20 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
    Ubuntu 13.10
    GNOME Shell 3.10.2.1
    mysql Ver 14.14 Distrib 5.5.34, for debian-linux-gnu (x86_64) using readline 6.2
    Apache/2.4.6 (Ubuntu)

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s