MySQL ‘UPDATE ON DUPLICATE KEY’ without a unique column?

You can create a PRIMARY or UNIQUE key out of multiple columns (called a composite key) in MySQL, which’ll allow ON DUPLICATE KEY to work just fine. // create a composite index CREATE INDEX my_composite_index ON my_table (column1, column2); // insert or update INSERT INTO my_table (column1, column2) VALUES (‘value1’, ‘value2’) ON DUPLICATE KEY UPDATE … Read more

Calculating distance between zip codes in PHP

This is mike’s answer with some annotations for the magic numbers. It seemed to work fine for me for some test data: function calc_distance($point1, $point2) { $radius = 3958; // Earth’s radius (miles) $deg_per_rad = 57.29578; // Number of degrees/radian (for conversion) $distance = ($radius * pi() * sqrt( ($point1[‘lat’] – $point2[‘lat’]) * ($point1[‘lat’] – … Read more

How do i cast char to integer while querying in django ORM?

An updated alternative without requiring the use of extra is the cast function (new in Django 1.10): >>> from django.db.models import FloatField >>> from django.db.models.functions import Cast >>> Value.objects.create(integer=4) >>> value = Value.objects.annotate(as_float=Cast(‘integer’, FloatField())).get()> >>> print(value.as_float) 4.0 From https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#cast

Set timezone in PHP and MySQL

In PHP: <?php define(‘TIMEZONE’, ‘Europe/Paris’); date_default_timezone_set(TIMEZONE); For MySQL: <?php $now = new DateTime(); $mins = $now->getOffset() / 60; $sgn = ($mins < 0 ? -1 : 1); $mins = abs($mins); $hrs = floor($mins / 60); $mins -= $hrs * 60; $offset = sprintf(‘%+d:%02d’, $hrs*$sgn, $mins); //Your DB Connection – sample $db = new PDO(‘mysql:host=localhost;dbname=test’, ‘dbuser’, … Read more