Sequel Pro (MySql client for mac OS)


Sequeal pro is one of the best mysql client for mac. Recently after updating mysql workbench it stopped working. I searched for alternative and found one of the best free mysql client.  it’s worth giving a try.

https://www.sequelpro.com/

Advertisements

MySQL import error #1064 phpMyAdmin


1064 mysql error

To resolve #1064 mysql error during import edit sql file you want to restore and go to last line. You find there three invisible characters, Remove last line then save file.

Try to import edited file and There will not any error.

 

MySQL database best practice of choosing data types during database design


Below is list of prefered database types with lengths:

Email : varchar(254)

Domain: varchar(254)

Password: char(32)

Phone:varchar(16)

Financial Data : decimal(10,2)

Latitude: decimal(10,8)

Longitude: decimal(11,8)

Please write comment in case above information is incorrect. Please also share more fields in comment and I will update them here. This will help newbie developer.

 

Google API to get latitude longitude from address


Here is PHP code to get latitude longitude form Address.

$address = "Put Your Address Here";
$prepAddr = str_replace(' ','+',$address);
$geocode = file_get_contents('http://maps.google.com/maps/api/geocode/json?address='.$prepAddr.'&sensor=false');
$output = json_decode($geocode , true);
if ($output['status'] == 'OK') {
	$total_location_mapped++;
	$latitude = $output['results'][0]['geometry']['location']['lat'];
	$longitude = $output['results'][0]['geometry']['location']['lng'];
}

source : https://developers.google.com/maps/documentation/geocoding/

Mysql order varchar column like Excel


CREATE TABLE IF NOT EXISTS `varchar_order_by` (
  `text` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `varchar_order_by` (`text`) VALUES
('1'),
('10'),
('100'),
('101'),
('11'),
('12'),
('15'),
('1abc'),
('2'),
('23abcd'),
('2abcd'),
('3'),
('4'),
('8'),
('80'),
('85'),
('99'),
('abcd'),
('bcd'),
('dfgh');

Mysql Query

select text regexp '[a-zA-Z\s]' ,
text + 0 ,
text regexp '[a-zA-Z\s]' AND text + 0,
text from varchar_order_by
order by
text regexp '[a-zA-Z\s]' asc,
text regexp '[a-zA-Z\s]' AND text + 0 desc,
text + 0 asc,
text asc