Create MySQL 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

Please scroll downto 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 Client. To do that, please search for the icon you see on Figure 1. Click on it to run the program.

start mysql client
Figure 1 - Start Mysql client

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 Ozeki;
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. 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 tabel. Now you have all the required tbales for Ozeki SMS Gateway.

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

We hope that this guide was helpfull. If you have any problem with any of the steps, feel free to contact us at info @ ozeki.hu

More information