Create MySQL database for Autoreply Database User

See how to create a simple database on your MySQL server. It is very important that the table structure is perfectly in line with the SQL query script you use. You can work similarly with Microsoft SQL Express, Oracle, Sybase, etc...

Step 1 - Download and install MySQL database server

For a step-by-step download and installation guide, please follow these screenshots and come back to Step 2, so you can find out how to create the database structure.

Please download a MySQL wampserver for Windows or install a MySQL debian package on your Linux operating system.

Step 2 - Create the database on your MySQL server

Open the command interpreter of your MySQL server. Enter the proper SQL statements to create the required database structure (Figure 1). It is essential to create your database, a table and a user. It is also required to add user privileges, so the user can work on the database. Keep in mind that the table structure in Figure 1 is in perfect relation with the example script demonstrated in these tutorials.

CREATE DATABASE ozeki;

USE ozeki;

CREATE TABLE autoreplymessage (
 id int(11) NOT NULL auto_increment,
 keyword varchar(30),
 msg varchar(160),
 PRIMARY KEY (id)
)charset=utf8;
ALTER TABLE autoreplymessage ADD INDEX (id);

CREATE TABLE log (
 id int(11) NOT NULL auto_increment,
 sender varchar(30),
 message varchar(160),
 PRIMARY KEY (id)
)charset=utf8;
ALTER TABLE log ADD INDEX (id);

CREATE USER 'ozekiuser'@'localhost' IDENTIFIED BY 'ozekipass';

GRANT ALL PRIVILEGES ON ozeki.* TO 'ozekiuser'@'localhost';
Figure 1 - Create 'autoreplymessage' table in MySQL

Step 3 - Insert data into 'autoreplymessage' table

Insert some rows into the 'autoreplymessage' so it can work with the example script demonstrated on these pages. The example script detects if the 1st word of the incoming message is 'RED', 'red', 'BLUE' or 'blue' and reacts accordingly. Please prepare the 'autoreplymessage' table with the INSERT statements seen on Figure 2. 

INSERT INTO autoreplymessage (keyword,msg) VALUES ('default','There is no data for this keyword');
INSERT INTO autoreplymessage (keyword,msg) VALUES ('red','Red is a good colour.');
INSERT INTO autoreplymessage (keyword,msg) VALUES ('blue','Blue is not the best option.');
Figure 2 - Fill 'autoreplymessage' table with some required (keyword,msg) parameter pairs

After inserting the values from Figure 2, you should see the 'autoreplymessage' table filled exactly like on Figure 3. 

> SELECT * FROM autoreplymessage;
id          keyword                        msg
----------  ------------------------------ -------------------------------------
 1          default                        There is no data for this keyword.
 2          red                            Red is a good colour.
 3          blue                           Blue is not the best option.

(3 rows affected)
Figure 3 - The values inserted into the 'autoreplymessage' table

More information