View source for 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:

<pre>mysql -u root -p</pre>

2. Define a new database:

<pre>create database `customer`;</pre>

3. Change current session to use database:

<pre>use customer</pre>

4. Create table

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

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:

<pre>grant select on customer.customer_priority to dr_user@localhost identified by 'xxxxxxxx';
flush privileges;</pre>

6. Populate some test data, for example:

<pre>mysql -u root -p</pre>

At the mysql prompt enter:

<pre>use customer;
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'); </pre>

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

<pre>mysql -u calleriduser -p</pre>

At the mysql prompt enter:

<pre>use customer;
select * from customers;</pre>

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