mrkeck Sharing the Love

4May/170

MySQL Get Consecutive Dates for Date Range

Source - http://stackoverflow.com/questions/2157282/generate-days-from-date-range

Not exactly what I was looking for, but I found the technique very intriguing


select a.dt
from (
select '2018-01-01' - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as dt
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.dt between '2017-01-01' and '2017-05-21'
ORDER BY a.dt

Results:
2017-01-01
2017-01-02
2017-01-03
2017-01-04...
2017-05-20
2017-05-21

Filed under: mySQL No Comments
29Nov/160

Multi-line Javascript

Turns out you can use a backtick (`) to write on multiple lines in javascript

console.log(`
This is on more
than one line
`);

Documentation

27Aug/160

Ionic 2 Geolocation.watchPosition Parameter 2 is not a function

Was getting this error "'watchPosition' on 'Geolocation': The callback provided as parameter 2 is not a function."
Turns out is a bug in "/node_modules/ionic-native/dist/plugins/geolocation.js"
Should have 3 params, was missing the 2nd parameter in "var watchId = navigator.geolocation.watchPosition(cb, options);"
So I added a function called cbe and put it in the 2nd param. (see below)

Geolocation.watchPosition = function (options) {
        return new Observable_1.Observable(function (observer) {
            var cb = function (data) { return observer.next(data); };
            var cbe = function (data) { return "err"; };
            var watchId = navigator.geolocation.watchPosition(cb, cbe, options);
            return function () { return navigator.geolocation.clearWatch(watchId); };
        });
    };

Extra backstory, in case it helps.
This line below threw no errors

this.watch = Geolocation.watchPosition().subscribe((position) => {...

But if you wanted to have options, like enableHighAccuracy: true, it would fail

this.watch = Geolocation.watchPosition({enableHighAccuracy: true}).subscribe((position) => {

In "/node_modules/ionic-native/dist/plugins/geolocation.d.ts" it tells it to use the default options "GeolocationOptions" if none are provided. But if you provide them is when it was running into the bug

28Jul/161

Phantomjs – command not found

Using a Mac, downloaded the zip and unzipped. Then tried to run phantomjs but said "Command not found" eventhough I was in the directory. If I used ./phantomjs in the dir it worked fine. No idea why

Filed under: phantomJs 1 Comment
28Jun/160

mySQL Start of Week on any Day

Needed query to show start of weeks based on Wednesday

DATE_SUB(exampleTimestamp), INTERVAL ( ( DAYOFWEEK(exampleTimestamp) + (6-DAYyouWANT) ) % 7 ) DAY) startOfWeek

0 - Sunday
...
3 - Wednesday
...
6 - Saturday

Filed under: mySQL No Comments
21Jun/160

Linux See if Process including Argument is Running

Problem I had was when run.php 13 was running it also thought run.php 1 was also running since the 13 contains 1.
Look for only processes run by root and ends with the argument matching the id

$file = '/var/www/html/run.php 1';
$isRunning = array();
exec('ps aux | grep "' . $file . '"', $isRunning);
$run = true;
    foreach($isRunning as $process){
        if( substr($process, 0, 4) == 'root'){
             $lastPart = array_pop(explode(' ', $process));
             //echo $lastPart;
           if( $lastPart == $sites[$i]['id'] ){
              $run = false;
           }
         }
     }
Filed under: EC2 No Comments
17Jun/160

PHP sudo as user with no password required

Was getting: "sudo: no tty present and no askpass program specified php"
When running:
echo shell_exec("cd /var/www/html/repo/;sudo -u ec2-user /usr/bin/git pull 2>&1");

sudo vim /etc/sudoers
%www ALL = (ALL) NOPASSWD: ALL

sudo usermod -a -G www ec2-user
sudo usermod -a -G www apache
Puts users ec2-user and apache are part of group www

May have to restart server

Filed under: EC2, PHP No Comments
16Jun/160

EC2 Amazon Linux LAMP Bitbucket Setup

sudo yum update -y
sudo yum install -y httpd24 php56 mysql55-server php56-mysqlnd
sudo service httpd start
sudo chkconfig httpd on
chkconfig --list httpd
sudo groupadd www
sudo usermod -a -G www ec2-user
exit
#Log back in
groups
sudo chown -R root:www /var/www
sudo chmod 2775 /var/www
find /var/www -type d -exec sudo chmod 2775 {} \;
find /var/www -type f -exec sudo chmod 0664 {} \;
ssh-keygen
#Enter 3 times
ssh-agent /bin/bash
ssh-add ~/.ssh/id_rsa
ssh-add -l
cat ~/.ssh/id_rsa.pub
#copy that to Bitbucket
vim ~/.ssh/config
"
Host bitbucket.org
IdentityFile ~/.ssh/id_rsa
"
:w
:q
chmod 600 ~/.ssh/config
cd /var/www/html
git clone git@bitbucket.org:ACCOUNT/NAME.git

Filed under: EC2 No Comments
9Oct/150

DELETE From Same MySQL Table

From http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql

Quoted in case the page goes away one day

DELETE FROM NAMES
WHERE id NOT IN (SELECT *
FROM (SELECT MIN(n.id)
FROM NAMES n
GROUP BY n.name) x)

I guess you have to sneak it past by putting inside another subquery. Worked!

Filed under: mySQL No Comments
25Sep/150

Google Sheets Conditional Formatting for Errors (#N/A)

Used this to highlight "#N/A".
Google Sheets Conditional Formatting Errors

KEY: Use the first cell in the range in the formula. It will do the rest.

Filed under: Google, Sheets No Comments