G4ZFE MySQL Log Search - Database

MySQL database

You first need to create a MySQL database. How this is done depends on your web server provider. For example my provider uses cpanel which allows both the database and users to be created and modified via a web GUI. If this is not provided then you can also use the MySQL client software available free from www.mysql.com.

The following is an example of how to create the database and owner from the mysql command line:

CREATE DATABASE radiolog;
GRANT SELECT, INSERT, DELETE, UPDATE on radiolog.* to username@localhost identified by 'password';

USE radiolog;

CREATE TABLE dxstation ... 
CREATE TABLE qsos ...
CREATE TABLE logfiles ...

The program assumes that the database is called "radiolog", the owner is "username" and that the password is "password".

Using phpMyAdmin connect as the database owner to the database and create the following three tables:

CREATE TABLE dxstation(
id smallint unsigned NOT NULL auto_increment,
dxcallsign varchar(30) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE qsos(
id mediumint unsigned NOT NULL auto_increment,
callsign varchar(12) NOT NULL,
op_mode varchar(6) NOT NULL,
band tinyint unsigned NOT NULL,
fk_dxstn smallint unsigned NOT NULL,
PRIMARY KEY (id),
KEY (callsign, fk_dxstn)
);

CREATE TABLE logfiles(
id smallint unsigned NOT NULL auto_increment,
filename varchar(80) NOT NULL,
qsos int unsigned NOT NULL,
filetype varchar(80) NOT NULL,
loaded DATE,
PRIMARY KEY(id)
);

dxstation table

The dxstation table conists of a sequential ID and the callsign of the logbook. For example I loaded the following data into my dxstation table:

INSERT INTO dxstation VALUES (1, '3W2ER');
INSERT INTO dxstation VALUES (2, '9M2/G4ZFE');
INSERT INTO dxstation VALUES (3, '9M2/G4ZFE/P AS-058');
INSERT INTO dxstation VALUES (4, '9M2/G4ZFE/P AS-072');
INSERT INTO dxstation VALUES (5, '9M2/G4ZFE/P AS-074');
INSERT INTO dxstation VALUES (6, 'M6A');
INSERT INTO dxstation VALUES (7, 'M7A');
INSERT INTO dxstation VALUES (8, 'SV9/G4ZFE');
INSERT INTO dxstation VALUES (9, 'M2A');

The above shows that I have nine searchable logbooks. This data is loaded manually using, for example, phpMyAdmin.

qsos table

The qsos table contains all the QSOs made for the DXpedition. Only the callsign, mode and band data is stored for each QSO along with a foreign key fk_dxstn that is used to associate the QSO with the correct logbook/DXpedition.

The QSO data is loaded using a PHP script. Both ADIF and Cabrillo format files can be uploaded.

logfile table

The logfile table holds details of all the log files that have been uploaded into the database.

> Next: PHP Upload script