Bad code vs "bad a__" code - I love this quote

"
Writing code that is unreadable because it's full of tricks isn't "bad ass" so much as just "bad".
"
Credits: http://stackoverflow.com/questions/1789945/method-like-string-contains-in-javascript#comment-10691511

Dynamic Tables With Mustache

Problem: Generate dynamic tables from a dynamic array with Mustache given that:

1. Total column count is unknown
2. Only one or two column names are known and must be rendered conditionally
3. Helper functions many not be used
4. Data is only provided in arrays. Not model classes

Typical data-set with variable column count where ID is the only column know to always be provided:

[id*]   [Col-1]    [Col-2]    [Col-3]   ...(more)
 1      'Foo'      'Bar'      'Baz'    ...(more)
 2      'Foo'      'Bar'      'Baz'    ...(more)
 3      'Foo'      'Bar'      'Baz'    ...(more)
 ...
(more)

Solution: Mix variating key names with constant key name

In this example below, the variating keys are based on the various column names provided dynamically from the datasource which are ("id"; "name"; "legal_name"; "email"; "signon_email"; "editable") and the constant key name is "field"

array (size=6)
  0 => 
    array (size=2)
      'id' => string '10' (length=2)
      'field' => string 'id' (length=2)
  1 => 
    array (size=2)
      'value' => string 'J. Doe' (length=8)
      'field' => string 'name' (length=8)
  2 => 
    array (size=2)
      'value' => string 'Jane Doe' (length=8)
      'field' => string 'legal_name' (length=8)
  3 => 
    array (size=2)
      'value' => string 'Jane@Doe.com' (length=12)
      'field' => string 'email' (length=12)

array (size=6)
  0 => 
    array (size=2)
      'id' => string '11' (length=2)
      'field' => string 'id' (length=2)
  1 => 
    array (size=2)
      'value' => string 'Jon Doe' (length=8)
      'field' => string 'name' (length=8)
  2 => 
    array (size=2)
      'value' => string 'John Doe' (length=8)
      'field' => string 'legal_name' (length=8)
  3 => 
    array (size=2)
      'value' => string 'John@Doe.com' (length=12)
      'field' => string 'email' (length=12)

The original data source's column name is provided which can be used in Mustache to know what field is being iterated whilst "field" key provides an invariable token name which can be used in Mustache to reference the value for the field as follows:

{{#rows}}
<tr>{{#fields}}
        <td>{{#id}}<a href="foo/{{id}}">{{id}}</a>{{/id}}
            {{^id}}{{field}}: {{value} {{/id}}
        </td>
    {{/fields}}
</tr>
{{/rows}}

The following is produced:

<tr>
        <td><a href="foo/10">10</a></td>
        <td>name: J Doe</td>
        <td>legal_name: Jane Doe</td>
        <td>email: Jane@Doe.com</td>
</tr>
<tr>
        <td><a href="foo/11">11</a></td>
        <td>name: Jon Doe</td>
        <td>legal_name: John Doe</td>
        <td>email: John@Doe.com</td>
</tr>

I have a new blog for Laravel

http://laravel-help.blogspot.com/

How to find distances relative to points of data in the same database

If you have a scenario where you need to calculate the distance in miles, between various point given as longitude and latitude, and these points are in the same database, then simply use the Haver-sine formula to calculate the distances:

 select 
    `origin`.`id` AS `o_id`,
    `destination`.`id` AS `d_id`,
    cast((3959 * 
        acos((((cos(radians(`origin`.`latitude`))
        * cos(radians(`destination`.`latitude`)))
        * cos((radians(`destination`.`longitude`)
        - radians(`origin`.`longitude`))))
        + (sin(radians(`origin`.`latitude`))
        * sin(radians(`destination`.`latitude`))))))
   as decimal (4 , 1 )) AS `miles_apart`
from `point_of_origin` `origin` join `destinations` `destination`;

mysqldump doesn't backup views as views

mysqldump does not dump views as views, but instead, dumps them as tables. Even then, the tables columns all come out as TINYINT(4). If you're not fortunate enough to have Percona's slick backup tools then you'll need another solution.

Here is an over simplified PHP script to get the job done. Simply pipe the output to a .sql file to get it into a neat SQL script file.

<?php
// Configuration for the DB Connection
$_db_name = "test";
$_db_host = "127.0.0.1";
$_db_user = "root";
$_db_pass = "";
$_db_port = '3306';
// ---- end configuration section
$mysqli = new mysqli($_db_host, $_db_user, $_db_pass, $_db_name, $_db_port);
if(!$mysqli) die("Connection error. Aborting\n");
$result = $mysqli->query("SELECT v.TABLE_NAME, v.VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS `v` WHERE v.TABLE_SCHEMA='test';");
if (!$result) die("The 1st query failed to return results. Aborting.\n");
$row = $result->fetch_all();
foreach($row as $a => $b)
{
    echo "CREATE VIEW `".$b[0]."` AS ".$b[1].";\n\n";
}

Find the nearest location by latitude and longitude in MySQL using the Haversine formula

If you need to find the nearest location of any point of interest by latitude and longitude in MySQL, then you can use the Haversine formula. Following here is and example that assumes you have a table named "destinations" which contains amongst other things, the latitude and longitude of those 'destinations'. An example of valid destinations in a real-world application would be a table listing hotels and their lat/long coordinates which would enable one to write a query to return the nearest hotels from that table based on your lat/long coordinates. (The distance provided is in miles. To get the distance in kilometers, exchange 3539 with 6371)

SET @my_lat = 35.229205 ; -- the source latitude
SET @my_lon = -114.26811 ;-- the source longitude

SELECT *, ( 3959 * acos( cos( radians(
-- latitude
@my_lat) ) * cos( radians( 
destination.latitude ) ) * cos( radians( 
destination.longitude ) - radians(
-- longitude
@my_lon) ) + sin( radians(
-- latitude
@my_lat) ) * sin( radians( 
destination.latitude ) ) ) ) AS distance 
-- table containing targets to compare distance
FROM destination
ORDER BY distance LIMIT 1 ;

Full example at SQL Fiddle ---> http://sqlfiddle.com/#!2/abba1/4/0 ( PLEASE DONATE )

I recently needed to use this to find the official timezone for a city closest to a person based on their latitude, longitude coordinates in MySQL, so as not to have to depend on look-ups to a web service. For anyone interested, city-timezone data can be downloaded from the geonames.org website and loaded as the 'destinations' table.

Reference:
http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula

Load timezone data by city and country into MySQL

If ever you needed to look-up timezone data in your MySQL database you know that you need to find a nice source for that data and load it into your database.

This is how I did it:

1. Download timezone information by cities from this site: http://citytimezones.info/cms/pending_requests.htm (this example uses the CSV format - download that file)
2. Unzip the file and note the location (full path) of the "cities.txt" file.
3. Use the following SQL to create a table to hold the data:

   CREATE TABLE `timezone_cities` (
     `city` varchar(100) NOT NULL,
     `timezone` varchar(250) NOT NULL,
     `country` varchar(45) NOT NULL,
     `latitude` double NOT NULL,
     `longitude` double NOT NULL,
     `timezone_name` varchar(200) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

4. Adapt and run the following SQL statement in MySQL to load the data:

   LOAD DATA LOCAL INFILE '/Users/nerd/Downloads/cities_csv/cities.txt'
   INTO TABLE timezone_cities
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\r\n';
   

Make sure that when you run the LOAD DATA query, that there are no errors or warnings.
Note that I did not optimize the design of the table (field sizes) to match the data ( DIY )