December 10, 2015 11:17

IPv6 addresses should be converted using MySQL's INET6_ATON function. To convert them back to their original value, use INET6_NTOA.

They should be stored as a VARBINARY(16).

This function is only available in MySQL 5.6+

In Laravel, if you're tracking visitors you could do something like this:

$ip = \DB::connection()->getPdo()->quote(request()->ip());

$visitor = Visitor::firstOrCreate([
    'longip' => \DB::raw("INET6_ATON({$ip})")
]);

Laravel, MySQL

October 13, 2015 19:36

I actually thought of this while answering a question on StackOverflow

When querying sums of columns, I typically do something like this:

$query = $account->transactions()
    ->select(\DB::raw('SUM(amount) AS total'))
    ->first();
$total = $query->total;

Here's another way to accomplish the same thing. Of course if you end up fetching thousands of rows you'll probably want to just let MySQL do the math for you, but it's way easier to read and cleaner.

$amounts = $account->transactions()->lists('amount');
$total = $amounts->sum();

Laravel, MySQL, PHP

September 12, 2011 01:41

Typically, when you try to divide by zero in MySQL, the result will be NULL. In certain cases, however, you'd like the result to be the numerator.

For example, when posts are receiving votes, we want a post with 8 yes votes and 0 no votes to have a ratio of 8 not of NULL. The cheap solution is to add +1 to both the yes and no votes, but then the ratio will be slightly inaccurate (9 in this case). The solution is to use MySQL CASE.

SELECT `posts`.`id`, 
    (CASE `vote_no`
        WHEN 0 THEN `vote_yes`
        ELSE (`vote_yes`/`vote_no`)
    END) AS `vote_ratio`
FROM `posts`

MySQL

September 8, 2011 16:47

If you have a table in MySQL with a Unix timestamp column (int 11) and you'd like to group results by date, you can run this simple query.

SELECT
    COUNT(`id`) AS `total`,
    DATE(FROM_UNIXTIME(`my_timestamp`)) AS `my_date`
FROM `my_table`
GROUP BY `my_date` DESC

If you want to group by month and year, ignoring the date, you can modify the query like this:

SELECT
    COUNT(`id`) AS `total`,
    MONTH(FROM_UNIXTIME(`my_timestamp`)) AS `my_month`,
    YEAR(FROM_UNIXTIME(`my_timestamp`)) AS `my_year`,
FROM `my_table`
GROUP BY `my_year`, `my_month` DESC

MySQL