Creating a MySQL table for use with Dynamic Routes

1. Connect to the mysql server (can be done on command line, through a GUI tool e.g. phpMyAdmin). The example is from the command line:

mysql -u root -p

2. Define a new database:

create database `customer`;

3. Change current session to use database:

use customer

4. Create table

CREATE TABLE `customer_priority` 
( `callerid` VARCHAR(20) NOT NULL , 
  `priority` VARCHAR(10) NOT NULL , 
  PRIMARY KEY (`callerid`(20)));

5. Setup mysql user to access the database.

The command assumes the database is on the same server as FreePBX. In the following example substitute dr_user and xxxxxxxx by your chosen username and password:

grant select on customer.customer_priority to [email protected] identified by 'xxxxxxxx';
flush privileges;

6. Populate some test data, for example:

mysql -u root -p

At the mysql prompt enter:

use callerid;
INSERT INTO customer_priority (callerid,priority) VALUES ('12345678','normal'); 
INSERT INTO customer_priority (callerid,priority) VALUES ('23456789','normal'); 
INSERT INTO customer_priority (callerid,priority) VALUES ('34567890','top'); 

7. Before moving on to configure FreePBX, check that the user can access the table:

mysql -u calleriduser -p

At the mysql prompt enter:

use callerid;
select * from customers;

If all is well you should see the data that you inserted.