Could someone please check my tables are correct?

RayWilk

Hello

I was given the info below to create 2 tables,

and below this info are the 2 tables I have created,

could someone please check they are correctly coded,

my 1st try at tables creating 🙂

.................................................
CREATE TABLE ip2nation (
ip int(11) unsigned NOT NULL default '0',
country char(2) NOT NULL default '',
blocked tinyint(4) NOT NULL default '0',
KEY ip (ip)
);

CREATE TABLE ip2nationCountries (
code varchar(4) NOT NULL default '',
country varchar(255) NOT NULL default '',
lat float NOT NULL default '0',
lon float NOT NULL default '0',

blocked tinyint(4) NOT NULL default '0',
PRIMARY KEY (code),
KEY code (code)
);
..........................................................

$db_desc[] = "Creating ip2nation table";
$db_query[] = "CREATE TABLE " . DB_PREFIX . "ip2nation (
ip int(11) unsigned NOT NULL default '0',
country varchar(2) NOT NULL default '',
blocked tinyint(4) NOT NULL default '0',
KEY ip (ip);";

$db_desc[] = "Creating ip2nationCountries table";

$db_query[] = " CREATE TABLE " . DB_PREFIX . "ip2nationCountries (
code varchar(4) NOT NULL default '',
country varchar(255) NOT NULL default '',
lat float NOT NULL default '0',lon float NOT NULL default '0',

blocked tinyint(4) NOT NULL default '0',
PRIMARY KEY (code),
KEY code (code);";

bradgrafelman

Here are some comments I have:

I see you're storing IP addresses as unsigned integers... but what about IPv6 addresses like 3ffe:1900:4545:3:200:f8ff:fe21:67cf?
Quotes are used to denote string values, not numeric values. In other words, '0' is not the same thing as 0; the first is represented as 0x48 while the latter is actually zero, or 0x00.
What is ip2nation.country supposed to contain? Its type definition doesn't match any of the columns from the ip2nationCountries table.
Once you resolve the above issue, you should add foreign key constraints to the ip2nation table.
Why doesn't ip2nation have a primary key?
This:
```
PRIMARY KEY (code),
KEY code (code)
```
is redundantly redundant. A PRIMARY KEY is already a KEY (hint: it's the second word in the two-word phrase 😉).
Can you explain the purpose of the two 'blocked' columns in both tables?

RayWilk

Hello bradgrafelman

thank you for your informative reply 🙂

below I have posted the info I was given to create 2 new tables and populate them,

but! I want to create the tables and populate them when I install the script, so I am adding the:

$db_desc[] = "Creating ip2nation table";

$db_desc[] = "Creating ip2nationCountries table";

to the Install.php in my script.

your help appreciated.

##########################################################

Open Proxy Country Ban System

(c) 2007

#########################################################

1. Run the Query on your SQL DB.
2. Import the enclosed file "SQLDUMP.sql" into your SQL DB.
3. Make the file edits below.
4. Upload enclosed file:  denial.php  into your ADMIN directory.
5. Upload enclosed file:  denial.tpl.php  into your admin/templates directory.
6. Upload your modified files.
7. In admin, goto the OPCBS Configuration Module and define your preferences.

==============================

RUN THE FOLLOWING SQL QUERIES:

ALTER TABLE probid_gen_setts
ADD
enable_proxy_deny TINYINT( 4 ) NOT NULL ,
ADD
enable_country_deny TINYINT( 4 ) NOT NULL ;

DROP TABLE IF EXISTS ip2nation;

CREATE TABLE ip2nation (
ip int(11) unsigned NOT NULL default '0',
country char(2) NOT NULL default '',
blocked tinyint(4) NOT NULL default '0',
KEY ip (ip)
);

DROP TABLE IF EXISTS ip2nationCountries;

===================================================================================
IN PHPMYADMIN - Click on IMPORT then browse, and select enclosed file: SQLDUMP.sql

and then click on Go. This will populate your IP database.

RayWilk

Some additional info from the sql to populate the databases, 2 examples of each.

INSERT INTO ip2nation (ip, country) VALUES(184549375, 'us');
INSERT INTO ip2nation (ip, country) VALUES(3332726783, 'ca');

INSERT INTO ip2nationCountries (code, country, lat, lon) VALUES('ad', 'Andorra', 42.3, 1.3);
INSERT INTO ip2nationCountries (code, country, lat, lon) VALUES('ae', 'United Arab Emirates', 24, 54);