Country City Longitude Latitude Database setup in php

Ok, a nice Friday night here and I am working on a project that highly involves the geospatial data. For that I needed data comprising the info I needed.
Thankfully this website provided me the data as one big text file.

Then I wrote this simple snippet for porting the data to my kitchen database 😉
The script should be used only for temporary purpose and by no means on production. It doesn’t do any sanitation or what so ever the good programming.. but it does the job!
I created country_city table with the desired columns only;

CREATE TABLE `country_city` (
  `country_city_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_code` char(2) NOT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  PRIMARY KEY (`country_city_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3170001 DEFAULT CHARSET=latin1

Then I run the following code to populate the data.

   <?php
   /**
    * @author Kaleb Woldearegay 
    * Code to be used "as is". Not responsible for any consequences after using the code.
    */
   const ROWS_TO_INSERT = 5000;
   $file_handle="/path/to/worldcitiespop.txt";
   $fh=fopen($file_handle, 'r');
   $query_pool=array();
   $db_handler=mysql_connect("localhost", "root", "");
   mysql_select_db("your database here");
   //assumed the database is connected at this moment..
   while(!feof($fh))
  {
      $line=fgets($fh, 1024);
      $data=explode(',', $line);
      $city=mysql_real_escape_string($data[1]);
      $query_pool[] = "(null, '{$data[0]}', '{$city}', '{$data[3]}', {$data[5]}, {$data[6]})";
      if (count($query_pool)>=ROWS_TO_INSERT)
      {
          $query = "INSERT INTO country_city VALUES " . implode(',', $query_pool);
          if (!mysql_query($query, $db_handler)) die('Error: '. mysql_error($db_handler));
          $query_pool=array();
      }
  }

That is it,
Enjoy!

Unicode error using PDO. Mysql Unicode problem

Are you having those ???? things on your webpage when something you entered on Mr mysql is unicode character?
Here is a fix for that

Assuming you are using PDO object in your Db Class..

$db = array containing information ....
$this->pdo = new PDO("mysql:host={$db['host']};dbname={$db['database']};charset=utf8", $db['username'], $db['password']);

Now the next task to to tell Mysql to honor unicodes:

$this->query('SET NAMES utf8');
$this->query('SET CHARACTER SET utf8');
$this->query('SET COLLATION_CONNECTION="utf8_general_ci"');

where your query function could look like

public function query($query, $params=array())
	{
		$results=array();
		if (!empty($this->pdo))
		{
			try
			{
				$stmt=$this->pdo->prepare($query);
				$stmt->execute($params);
				$results=$stmt->fetchAll(PDO::FETCH_ASSOC);
			}
			catch(PDOException $exception)
			{
				echo "error occured";
				//logit or whatever
				die();
			}
		}
		return $results;
	}

Yea this would take care of the problem. There are certain things you have to do on the mysql database itself to handle unicode but I will not discuss those here
Hope it would help somebody..

Changing column datatype in mysql

From time to time I have been changing the column datatype for different reasons. Majorly being for performance and efficiency and some other time based on how the mysql engines would be strong on some types vs the other
here is the simple command i use for changing the column datatype

alter table table_name modify column column_name datatypegoeshere
eg.
ALTER TABLE members MODIFY COLUMN date_created TIMESTAMP;

Allowing others to use mysql from user machine on ubuntu- simple one!

It is customary sometimes to share the part of the database from sandbox to a fellow developer or to access it from the other machine – umm.. is it not how the servers are doing it?? what are you talking about 😉

Anyway here is a simple note to make it happen.1. We need to bind the machine name for the server. to do that
open your my.cnf – inside the /etc/mysql/my.cnf for debianish machines and search for the
[mysqld] and add the following
bind-address = your ip address goes here
you can find your ip address from

ifconfig

then save the file and restart your mysql

sudo /etc/init.d/mysql restart

There are ways to assign for a specified user from the specified ip addresses as well.
Like if you have database db1 and user1 from ip address 168.192.3.4 then

grant select, insert, update on db1.* to 'user1'@'168.192.3.4'

Adding Unicode character to mysql from Java-hibernate

How to enable/add unicode character in hibernate with mysql

Unicode is ruling, it would come handy when working with non ASCII characters like when dealing with Chinese or Ethiopian alphabet amharic characters.

With internalization being the main concern in softwares, bit it website or mobile application, there is a chance you will need unicode in your application.

Hibernate is an ORM, object relational mapper, that is being used with Java and .net. It will allow to abstract all the database related stuffs with simple interface. In this unicode hibernate tutorial, I will show how you can insert, select update and delete records with unicode.

Why do I get No provider for Entity manager Error and how should I fix it

I will show the fix from the hibernate side for MySQL. But keep in mind that unicode characters have to be enabled on the database side as well with proper encoding.

See how you can avoid could not open hibernate session error

The solution for having unicode characters to be recognized as they are passing though the hibernate world would be done on the config file.

Enabling unicode character in hibernate

Here are the steps to follow:

1. go to your hibernate.cfg.xml file

2. make your the connection url something like: jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8 – make sure the the ampersand is the html-encoded ampersand.

Do you know you can have boilerplate java app with maven?

Find k complementary numbers – algorithm in Java

Inserting data to table reading from another table in mysql

OK, we can use INSERT INTO statement to insert fresh data to mysql. But how about if the need lies on reading data from another table?
Here is how we do it in mysql.
Let us create the tables for illustration:
Log into your mysql and create the following queries
Creating database
CREATE database testdb;
USE testdb;

Creating tables

CREATE TABLE testtbl1 (id int(10) auto_increment, fname varchar(20), lname(20), primary key(id));
CREATE TABLE testtbl2 (id int(10) auto_increment, firstname varchar(20), lastname(20), age int, primary key(id));

Lets add some data to table 2 now
INSERT INTO testtbl2 (firstname, lastname, age)
VALUES
(‘ftest1’, ‘ftest1’, 35),
(‘ftest2, ‘ftest2’, 21),
(‘ftest3, ‘ftest3’, 25),
(‘ftest4’, ‘ftest4’, 38)

Now lets add data to the first table filtering persons with age less than 30

INSERT INTO testtbl1 (fname, lname) SELECT firstname, lastname FROM testtbl2 WHERE age <= 30;

How to change default MySQL directory

Originally it will be located at /var/lib/mysql

Say you want to change it to /home/myApps/mysql

First if the mysql is running stop it: sudo /etc/init.d/mysql stop
and stop the server too: sudo/etc/init.d/apache2 stop

Go to /etc/mysql/my.cnf and edit:
datadir = /var/lib/mysql to datadir = /home/myApps/mysql

Copy files from existing path to the new path
cp -R /var/lib/mysql* /home/myApp/mysql

Grant ownership to new directory for mysql

sudo chown -R mysql:mysql /home/myApp/mysql

then start up your server and mysql – voila.