Problems setting up a Database on 1and1 Linux server

[MrTIMarshall]

Hello,

 

I am trying to set up a database for the GeoIP Lite on my 1and1 Linux server and got directed to a tutorial. Upon doing the first step in the tutorial, I am receiving an error message.

 

What I did:

1. Set up a new database,
   
2. Opened up the Query Window
   
3. Entered the code they said to input and hit the 'Go' button.
   

This is where I receive the errors;

 

I don't have a clue what to do as this is the first lead I have had on setting up the GeoIp, You can visit the tutorial here; http://bartomedia.blogspot.co.uk/2007/11/maxmind-geoip-setup-tutorial-using.html

 

Best Regards,

Tim

[Barand]

Have you checked that you have privileges to create databases or do they give you only a single database (in this case db458117895) and you can create tables within that?

[MrTIMarshall]

Have you checked that you have privileges to create databases or do they give you only a single database (in this case db458117895) and you can create tables within that?

 

I don't know how to check what I can and cannot do exactly. I own this server and upon my purchase it said I can make unlimited databases. If I chose from the drop down options, I can go to that one and then I see options to "Create new table on database db458117895: "

 

Best Regards,

Tim

Edited February 24, 2013 by MrTIMarshall

[MrTIMarshall]

I think I might have got somewhere here, if I skip the step which is not necessarily needed, it seems I am back on track

[Barand]

I don't know how to check what I can and cannot do exactly.

 

http://dev.mysql.com...how-grants.html

 

edit

PS may be you to connect as "root" to create databases

Edited February 24, 2013 by Barand

[MrTIMarshall]

I simply skipped the first step as I had already made the database and I am now onto the loading of the file, however, apparently I'll need to modify the tutorial to be able to load the file I wish to load.

 

The GeoLiteCity-Blocks.csv file has three columns in, these are; startIpNum, endIpNum and locId. The GeoLiteCity-Location.csv file has 9 columns which are; locId, country, region, city, postalCode, latitude, longitude, metroCode and areaCode.

 

Presumably I only need the second file, so I need to work out what adjustments do I need to make to be able to load this to my database.

[Barand]

The country in that second file is a two character code, so you may want the countries csv file to associate names with codes.

 

Also, there might be nine columns but only four are populated. The first rows had only 4 populated, it gets better as you scroll down.

Edited February 24, 2013 by Barand

[MrTIMarshall]

CREATE TABLE cc (
ci tinyint(3) unsigned NOT NULL auto_increment,
cc char(2) NOT NULL,
cn varchar(50) NOT NULL,
PRIMARY KEY (ci)
) AUTO_INCREMENT=1 ;
CREATE TABLE csv (
start_ip char(15)NOT NULL,
end_ip char(15)NOT NULL,
start int(10) unsigned NOT NULL,
end int(10) unsigned NOT NULL,
cc char(2) NOT NULL,
cn varchar(50) NOT NULL
);
CREATE TABLE ip (
start int(10) unsigned NOT NULL,
end int(10) unsigned NOT NULL,
ci tinyint(3) unsigned NOT NULL
);

 

So at the moment I'm at this stage, I've processed this and have three tables, although I don't understand why. I am at a complete loss at what to do and I don't quite understand what you mean by your last reply.

 

I appreciate all the help you have provided recently by the way Barand, I am truly grateful

 

Best Regards,

Tim

[Barand]

I mentioned it in case you ever need to translate the country code to country name.

 

Don't use "end" as a column name - it's a mysql reserved word.

[Barand]

...although I don't understand why

 

What do want to do with the data?

 

I originally thought you wanted to get the lat and long, but that isn't in there.

Edited February 24, 2013 by Barand

[MrTIMarshall]

The latitude and longitude is in the GeoLiteCity-Location.csv file.

 

My goal is to set the latitude and longitude in the day and night phrases to be personalized based on the users location, eventually tell them other things such as the weather and wahtnot.

 

Best Regards,

Tim

[MrTIMarshall]

Okay, so I've got the database set up, I need to now find something to be able to test the data...

 

I now need to somehow set up a PHP page to check the visitors IP with my database and echo the info onto the page.

 

Does anyone know how to do this?

 

Best Regards,

Tim

[jcbones]

Visitors IP (client IP) can usually be obtained by $_SERVER['REMOTE_ADDR']; 

[MrTIMarshall]

Visitors IP (client IP) can usually be obtained by $_SERVER['REMOTE_ADDR']; 

 

I tried doing the following which made a syntax error;

echo "$_SERVER['REMOTE_ADDR']";

 

Regardless, I would not know how to use this to search a database.

 

Thank you for your input.

[Barand]

I tried doing the following which made a syntax error;

echo "$_SERVER['REMOTE_ADDR']";

 

Regardless, I would not know how to use this to search a database.

 

Remove the quotes.

 

How you search the database depends on your database.

[MrTIMarshall]

Remove the quotes.

 

How you search the database depends on your database.

 

Thank you, and it is a Mysql database.

Edited February 27, 2013 by MrTIMarshall

[Barand]

Thank you, and it is a Mysql database.

 

A real mine of information, aren't you

 

If you want to PM me a zipped dump of the relevant tables I'll look at it for you

[MrTIMarshall]

A real mine of information, aren't you

 

If you want to PM me a zipped dump of the relevant tables I'll look at it for you

 

 Sorry, I don't exactly know what to say. I have a database with, I think two tables full of information. after following this tutorial I found: http://odkq.com/geolitecity

 

I don't know how else to explain or what exactly to send you.

 

Best Regards,

Tim

[Barand]

your mysql admin control program (phpMyAdmin, MysqlWorkbench or whatever) should have an option to export/dump tables

Edited February 27, 2013 by Barand

[MrTIMarshall]

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
-- 
-- Host: db458117895.db.1and1.com
-- Generation Time: Feb 27, 2013 at 07:45 PM
-- Server version: 5.1.67
-- PHP Version: 5.3.3-7+squeeze14
-- 
-- Database: `db458117895`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `blocks`
-- 

CREATE TABLE `blocks` (
  `startIpNum` int(10) unsigned NOT NULL,
  `endIpNum` int(10) unsigned NOT NULL,
  `locId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`endIpNum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `blocks`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `location`
-- 

CREATE TABLE `location` (
  `locId` int(10) unsigned NOT NULL,
  `country` char(2) COLLATE latin1_general_ci NOT NULL,
  `region` char(2) COLLATE latin1_general_ci NOT NULL,
  `city` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `postalCode` char(5) COLLATE latin1_general_ci NOT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `dmaCode` int(11) DEFAULT NULL,
  `areaCode` int(11) DEFAULT NULL,
  PRIMARY KEY (`locId`),
  FULLTEXT KEY `city` (`city`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `location`
-- 

 

Do you mean this? If you want me to download anything and send them to you, what format should I send them in?

 

Best Regards,

Tim

[Barand]

That's it. Save as .SQL and attach to PM. Zip if too big.

[MrTIMarshall]

I've been searching how to do so for a while and cannot see how to add an attachment to a message to you...

[Barand]

Click "Use full editor".

 

Same as posting a reply on the board

[MrTIMarshall]

Click "Use full editor".

 

Same as posting a reply on the board

 

I'm on the full editor, I cannot see an option to attach a file.

[MrTIMarshall]

It seems to only be you whom help me on these forums with others helping here and there when they can, therefore, I'm just going to attach it to my message on here and if needed, I can simply remove it afterwards as I just cannot find how to attack it to a personal message to you Barand.

 

 

db458117895.sql.zip