PHP

PHP: SFTP with phpseclib and ssh key

First you’ll need download / install phpseclib.

Here’s how to connect to the server with ssh key with this library:

use phpseclib\Crypt\RSA;
use phpseclib\Net\SFTP;

$sftp = new SFTP($host); // connect to host

// get the key
$key = new RSA();
$key->loadKey(file_get_contents($keyfile));

// connect to server
$sftp->login($user, $key);

// now do your sftp operations

Notes:

  • Line 8: this will return false if key isn’t loaded, eg the ssh key file doesn’t exist.
  • Line 11: this will return false login fails.

You can find examples of how to use the lib here.

Yii

Yii2: Auto-expand Parent Menu

When you have multi-level menu and when a submenu item is selected, you want the parent to auto expand and set as active.

This is one thing I wish I had gone through the documentation before spending hours extending the widget to achieve this. Because it’s already implemented by the framework!

All you need to do is to set activeParents property to true.

Ajax / Yii

Yii2: Infinite scrolling with GridView and ListView

This is one combo that I use frequently in my Yii2 projects. When the page is scrolled to the end, next set of data is retrieved and displayed automatically without the need to click the pagination link and without waiting for the page to load. This improves user experience.

Note that if you have huge amount of data, you still have to wait during the initial page load. But with infinite scrolling navigation through the any pages is smoother.

I’ve tried a few plugins but I find yii2-scroll-pager works the best with GridView and ListView widgets. This replaces the pagination links at the bottom of the page (or depends where you set it on the widget’s template).

The extension is installed via composer, or download here.

Here are the coding templates I used with these 2 widgets.

Continue »

SQL

MsSQL: Find Foreign Key References

Found via this source, this will list all foreign key relations in the database:

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id