Could someone please check these tables?

RayWilk · February 29, 2012, 1:57pm

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);";

r937 · February 29, 2012, 2:04pm

not sure why you posted this in the (x)html forum – php seems more apprpriate, although you shouldn’t really need to create tables via php, seeing as how you’d only need to do it once and it seems rather wasteful to write a php program to do it when you could simply submit the CREATE statements to mysql directly

if you declare PRIMARY KEY (code), then KEY code (code) is redundant

how are these two tables supposed to be related? how will you be using them?

RayWilk · February 29, 2012, 2:12pm

Hello,

thank you for your reply,

Hmmmm, I was sure I clicked on Database-SQL

I am following the data I was given, it says Primary Key & Key, so that is what I did

1 will be populated with Country codes, eg: nz, aus, ch

2 will be populated with ip address numbers for the countries,

could you please assist me in getting the Table Creation code correct?

r937 · February 29, 2012, 2:29pm

what you were given is of dubious quality, eh

yeah, but are the tables supposed to be related?

how were you planning to use them?

RayWilk · February 29, 2012, 2:40pm

Hello r937

yes dubious quality only to your eye, I was working on they know what they are doing…lol

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.

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

Open Proxy Country Ban System

(c) 2007

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

Run the Query on your SQL DB.
Import the enclosed file “SQLDUMP.sql” into your SQL DB.
Make the file edits below.
Upload enclosed file: denial.php into your ADMIN directory.
Upload enclosed file: denial.tpl.php into your admin/templates directory.
Upload your modified files.
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;

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)
);

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

IN PHPMYADMIN - Click on IMPORT then browse, and select enclosed file: SQLDUMP.sql and then click on Go. This will populate your IP database.

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

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);

your help appreciated

r937 · February 29, 2012, 3:05pm

you’re welcome

good luck

RayWilk · February 29, 2012, 3:08pm

Hello r937

wait!

could you please correct these for me ?

$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);";

r937 · February 29, 2012, 3:15pm

sorry, man, i don’t do php

ServerStorm · February 29, 2012, 10:46pm

Hi Ray,

I know that I am jumping into this thread late so I apologize for it, but reinforcing in a big way what r937 indicated this is normally a really, really bad idea to have php constructing tables like this.

I can not think of one good reason (albeit feeling very un-creative currently) why this is needed.

You can write sql scripts that you run that drop and recreate your table structures as well as data. When testing and you have operations that require a ‘fresh’ database it takes only seconds (depending on the size of the data) normally to drop a polluted table and use your sql script to recreate a fresh one. You can initialize these scripts in PHPMyAdmin, MySQL Workbench or from the command line. You can even write your php file to issue the command line creation of these scripts.

I hope you know what you are getting into.

Regards,
Steve

RayWilk · March 1, 2012, 4:30am

Hi Steve, always good to hear from you, jumping in late or early is always welcome

yes I understand what you are saying, and would like to learn more about creating a new table then populating it,

my lack of knowledge in that method led me to trying to create the new table when the script Installs,

I thought I could just add one more Creating table command

At this time I have not succeeded, I get a SQL Error when the Installation process reaches my new table creating command,

All info/advice keenly received

r937 · March 1, 2012, 11:23am

let me guess what that error message said…

hmm…

nope, sorry, i have no idea

would you mind revealing it?

RayWilk · March 1, 2012, 11:32am

Hello r937

all is well

I gave up on the Install method for the 2 databases,

I decided to create the 2 new tables within phpMyAdmin, then populated them,

so far so good, still in the process of completing and checking

ServerStorm · March 1, 2012, 2:59pm

I decided to create the 2 new tables within phpMyAdmin, then populated them,
Good move Ray!

RayWilk · March 1, 2012, 3:47pm

Hi Steve,

yes creating the tables and populated them was quick and easy, But! I had a problem here:

Topic		Replies	Views
Could someone please check, and correct these tables for me? PHP	7	562	October 8, 2014
Create table not working Databases	4	559	July 10, 2011
PHP Create Table not working PHP	4	711	July 2, 2010
Help needed on mysql queries. (submission tomorrow ) Databases	8	870	January 23, 2011
Mysql 1286 Unknown table engine 'InnoDB'' Databases	11	3603	October 8, 2014

Could someone please check these tables?

Open Proxy Country Ban System

(c) 2007

RUN THE FOLLOWING SQL QUERIES:

IN PHPMYADMIN - Click on IMPORT then browse, and select enclosed file: SQLDUMP.sql and then click on Go. This will populate your IP database.

Related topics