One of the reasons I switched to MySQL instead of using the built in phonebook was because the numbers weren’t matching the ones in the internal phonebook. The built in phonebook only allows numbers so international numbers with + or numbers with hyphens cannot be matched to phonebook entries. Using a user defined database and query will solve these problems.
We need a schema and a table to store the phonebook and I’d suggest a new user with at least SELECT privileges. The script below creates a schema called freepbx and a table called phonebook:
— Create schema freepbx
CREATE DATABASE IF NOT EXISTS freepbx;
— Definition of table `freepbx`.`phonebook`
DROP TABLE IF EXISTS `freepbx`.`phonebook`;
CREATE TABLE `freepbx`.`phonebook` (
`name` varchar(50) NOT NULL,
`number` varchar(20) NOT NULL,
`phone` varchar(10) DEFAULT NULL,
PRIMARY KEY (`name`,`number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The table contains 3 fields:
Run the following query to insert new contacts in the phonebook:
insert freepbx.phonebook (name, number, phone) values ('Danny','123456','mob');
Replace Danny, 123456. mob with your own values. The name and number (the first 2 items in the values) are required and the phone (‘mob in the example) is optional. When omitting the phone field run the following query instead:
insert freepbx.phonebook (name, number) values ('Danny','123456');
The name and number combined must be unique otherwise it won’t create an entry in the database. See below for how it will be displayed on the phone / reports.
The Siemens S68H which comes with the Siemens Gigaset 685IP will display all the caller ID if it fits on the display but the text will “just go off the screen” if it does not, only display the first part of the name. The number is always displayed below the caller ID where possible.
Log into FreePBX and go to
Tools Setup (tab) > Inbound Call Control > CallerID Lookup Sources Click the Add CID Lookup Source button to add a new phonebook directory. Select MySQL from the Source type This will change the require informations on the page. Enter the following information:
Source Description: MySQL
Source type: MySQL
Cache results: yes [tick]
Host: [server address]
SELECT CONCAT(name, IF(phone IS NOT NULL,CONCAT(' (', phone, ')'), '')) as cid FROM phonebook WHERE number like '[NUMBER]'
Source description can be any name and it will be used a reference in other settings. The Host will typically be localhost unless the MySQL database is installed on a different machine.
The query will show Danny (mob) if the phone field is populated. The brackets are automatically inserted. If the phone field is empty (NULL in the database) it will just show the name Danny
Press the Submit Changes button to save the new caller ID look up.
Setup (tab) > Inbound Call Control > Inbound Routes and select an inbound route which will use the new phonebook. Under the CID Lookup Source section select the phonebook called MySQL (or what ever it was named in the Source description) in the Source dropdown.
Press the Submit button to save the change and repeat for all other inbound routes which will use the MySQL phonebook.
Apply the changes for all the settings to take hold.
Having MySQL as a source makes it a lot more flexible and customizable. Also it can be linked to an existing directory. More technical knowledge is required to manage the contacts but that comes with the flexibility.