Database tables for storing sms in mysql

Send SMS from MySQL (part 2/4)
Create Database Tables

In this video series you can see how to connect Ozeki SMS Gateway and MySQL database server for SMS messaging. This video shows how to create the proper database table structure by using the command line of MySQL.

Video content
1. Connect to MySQL
2. Copy CREATE TABLE statement

Video 1 - How to create the proper database table structure by using the command line of MySQL (Video tutorial)

Please scroll down to copy the SQL statements (Figure 1) used in the video. If you have created the database in MySQL, you can jump to the next video.

The first step is to open MySQL Shell. To do that, please search for the icon you see on Figure 1. Click on it to run the program.

start mysql shell — Figure 1 - Start Mysql shell

Now if you have the program opened, please copy the code, you can see on Figure 3. You can see that we have pasted the code, and it has created our database, on Figure 2.

create ozeki database — Figure 2 - Create Ozeki database

MySQL CREATE TABLE statements to copy:

    CREATE DATABASE ozekidb;

Figure 3 - Create database statement

MySQL CREATE TABLE statements to copy:

CREATE TABLE ozekimessagein (
  id int(11) NOT NULL auto_increment,
  sender varchar(255) default NULL,
  receiver varchar(255) default NULL,
  msg text default NULL,
  senttime varchar(100) default NULL,
  receivedtime varchar(100) default NULL,
  operator varchar(100) default NULL,
  msgtype varchar(160) default NULL,
  reference varchar(100) default NULL,
  PRIMARY KEY (id)
  ) charset=utf8;
  ALTER TABLE ozekimessagein ADD INDEX(id);


CREATE TABLE ozekimessageout (
  id int(11) NOT NULL auto_increment,
  sender varchar(255) default NULL,
  receiver varchar(255) default NULL,
  msg text default NULL,
  senttime varchar(100) default NULL,
  receivedtime varchar(100) default NULL,
  reference varchar(100) default NULL,
  status varchar(20) default NULL,
  msgtype varchar(160) default NULL,
  operator varchar(100) default NULL,
  errormsg varchar(250) default NULL,
  PRIMARY KEY (id)) charset=utf8;
  ALTER TABLE ozekimessageout ADD INDEX (id);

Figure 4 - CREATE TABLE statements to copy

Now if you have the database created, you have to create two tables. One for inbox and one for outbox mails. Please copy the code you see on Figure 4. It will make all the necessary data fields. Highlight the code and press "CTRL + C" to copy it to your clipboard.

copy ozekimessagein create table statement — Figure 3 - Copy ozekimessagein CREATE TABLE statement

Now if you have the code on your clipboard, please paste it. If you have pasted it into the MySQL Command Line, press enter, and the application will build the tables for you. Feel free to modify the code to your liking, if you are familiar with the SQL language.

paste ozekimessagein create table statements and run it on the mysql database server — Figure 4 - Paste ozekimessagein CREATE TABLE statements and run it on the MySQL database server

Now you need to do the last 2 steps you have done, but with the “ozekimessageout” table (Figure 5). You can copy it with “CTRL + C”, just as you did on Figure 3.

copy ozekimessageout create table statement — Figure 5 - Copy ozekimessageout CREATE TABLE statement

Now you just need to paste in the last copied code into the MySQL Command Line. Press "CTRL + V" to paste the code from your clipboard. If you have the code, please press Enter. now the Command Line will build your second table. Now you have all the required tables for Ozeki SMS Gateway. (Fiugre 6)

paste ozekimessageout create table statemets and run it on the mysql database server — Figure 6 - Paste ozekimessageout CREATE TABLE statements and run it on the MySQL database server

How to create a MySQL user

The following SQL commands (Figure 7) can be used to create a user called 'ozekiuser', with a password of 'ozekipass'. This is a MySQL user account. You can use this user account to connecto to the ozeki database, because the GRANT command has given access to this database.

create ozekiuser for database — Figure 7 - Create ozekiuser for database

CREATE USER 'ozekiuser'@'localhost' IDENTIFIED BY 'ozekipass';
GRANT ALL PRIVILEGES ON ozeki.* TO 'ozekiuser'@'localhost';
FLUSH PRIVILEGES;

How to create a MySQL user

More information