Sully Syed

Use Amazon S3 to create a proper HTTP and HTTPS redirect for a domain name

Fairly often we purchase a new domain or decommission an existing one and need to redirect all queries to it to a specific URL on a wholly different domain and website. Practically all services where you can register domain like GoDaddy, name.com and Namecheap offer a redirect function, but nearly all fail to work if the user attempts to go to a HTTPS URL on the domain you are redirecting.

If you’re already making use of Amazon AWS Cloud, a very low cost and foolproof solution is to utilize Amazon S3’s static website hosting feature along with Amazon CloudFront to handle the redirect:

  1. Create a new Amazon S3 bucket (eg. domain-redirect-olddomaincom) with ACLs disabled, the checkbox to block all public access unchecked (as we wish to allow public access) and bucket versioning disabled; create the bucket.
  2. Edit the newly created S3 bucket; under the Properties tab, edit the Static Website Hosting properties.
  3. In the Static Website Hosting properties page:
    • Static website hosting: Enable
    • Hosting type: Host a static website
    • Index document: index.html
    • Redirection rules: Copy/paste the following, with the appropriate changes made to the values of HostName and ReplaceKeyPrefixWith: 
      [
    {
        "Redirect": {
            "HostName": "www.newdomain.com",
            "HttpRedirectCode": "301",
            "Protocol": "https",
            "ReplaceKeyPrefixWith": "path/to/redirect/to/"
        }
    }
]
  4. Hit the Save Changes button to finalize the redirect using the S3 bucket; it should work immediately and allow you to test it via the Bucket Website Endpoint URL shown on the page.
  5. Create a new Amazon CloudFront distribution, making sure to:
    1. Create Origin: Ensure that it points to the static website endpoint of your new S3 bucket (eg. domain-redirect-olddomaincom.s3-website.ca-central-1.amazonaws.com, not the default of domain-redirect-olddomaincom.s3.ca-central-1.amazonaws.com)
    2. Alternate domain name (CNAME): Enter all versions of the domain you wish to be handled and redirected (eg. olddomain.com and www.olddomain.com)
    3. Custom SSL certificate: Request a certificate that handles the domains you entered above; this is the (free) option that allows your newly set up domain redirect to respond properly to both HTTP and HTTPS requests
  6. Add the Amazon CloudFront domain (eg. d1gyvh82u10kd6.cloudfront.net) to your domain name’s DNS records for the root and subdomains you wish to redirect.

You’re done! While a bit long-winded as a process, this appears to be the cheapest and most foolproof way to set up a domain redirect for the long term.

 

Sully Syed

Get your MySQL table sizes in MB via a SQL query

Figuring out what tables are making your database excessively large can be really helpful when working with production database exports and keeping various environments up to date. This SQL query outputs that information for all databases on a given server:

SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY table_schema, (data_length + index_length) DESC;

Reference:

Sully Syed

Generate MySQL INSERT statements for a few existing records

Occasionally, you’ll want to duplicate a couple of records from your production MySQL database for local use without dumping the entire table (which could be huge). As a solution, the mysqldump command allows you to specify a table and a WHERE query for that table, allowing you to select specific record ID #s and retrieve only those records as INSERT statements:

mysqldump -h {host} -P {port} -u {username} -p{password} {database_name} {table_name} --where="ID = 1" --no-create-info --no-create-db

Reference:

Sully Syed

Import Environics demographics data into a SQL database via Laravel CLI command

Environics Analytics provides incredibly useful demographics, psychographics and segmentation data for Canada, but the raw data is challenging if the goal is to use it in a general SQL query: The raw data is in one file, the meanings of the columns are in a ‘metadata’ file, and the data provided doesn’t necessarily just list postal codes but other geographic areas as well.

Linked below is a Laravel CLI command that outputs a .SQL file that will handle the creation and insertion of data and definitions for you. The CLI command is invoked as follows:

php artisan environics_demographics:import {csv_demographics_definitions} {csv_demographics_data} {year_demographics_data} {dir_output_data}

Find the full script on GitHub Gist, or below:

Sully Syed

Merge a PHP array without duplicates (array_merge_recursive_distinct)

Often enough in PHP, you’ll grab objects from a variety of sources and want to merge them into a single array of results. To merge without duplicates, add the following function to your codebase and make use of array_merge_recursive_distinct the same way you would array_merge_recursive:

// From: https://www.php.net/manual/en/function.array-merge-recursive.php#92195
if (! function_exists('array_merge_recursive_distinct')) {
    /**
     * @param array<int|string, mixed> $array1
     * @param array<int|string, mixed> $array2
     *
     * @return array<int|string, mixed>
     */
    function array_merge_recursive_distinct(array &$array1, array &$array2): array
    {
        $merged = $array1;
        foreach ($array2 as $key => &$value) {
            if (is_array($value) && isset($merged[$key]) && is_array($merged[$key])) {
                $merged[$key] = array_merge_recursive_distinct($merged[$key], $value);
            } else {
                $merged[$key] = $value;
            }
        }

        return $merged;
    }
}

Reference:

Sully Syed

Git pull in a remote branch and accept all incoming changes

Do a git pull that overwrites local conflicts and accepts all remote changes via:

git pull -s recursive -X theirs <remoterepo or other repo>

For example, overwrite your local master branch with the remote origin‘s master branch:

git pull -s recursive -X theirs origin master

If it’s just a single file you need to overwrite, use git checkout:

git checkout --theirs path/to/file

Sully Syed

Regular expressions: Find instances when the string does not equal a certain value

Another typical regex scenario: You’re looking through some data (like a JSON object) and are looking for when a value is actually being set. Let’s say our string contains:

"puttLength": null,

Typically, you’d write a regular expression to capture the <em> tag by writing this:</em>

"puttLength": ((?!null).)*$

The use of ?! is the instruction to make a negative lookahead match, ensuring null does not exist before the line can be returned as a match. A terrific example / tool to test in your browser exists at https://www.regextester.com/15 .

Sully Syed

Getting a recursive list of files sorted by last modified date in Linux

Here’s a quick way to get a recursive list of files, sorted in descending order by the last modified date, from any folder in *nix:

find . -type f -printf "%T@ %p\0" | sort -zk1nr | xargs -0 printf "%s\n"

Sully Syed

Making scheduled posts in WordPress public so they can be scheduled / scraped on social media

The basics of scheduling in WordPress are quite simple: If you give a WordPress post a published date set in the future, it’ll remain “hidden” on your website until that date and time arrives. It’ll then appear, right on time, at the top of your list of public posts.

But here’s an interesting problem: Social media is now a major driver (maybe the driver) of traffic to digital media websites. When you schedule your post in WordPress, you’ll also naturally want to schedule that post to appear on Facebook and Twitter – but that would require the WordPress post to be public, which in its “future” post status isn’t yet.

The code snippet below can be placed in your theme’s functions.php file, and allows non-logged-in website visitors to view posts in the “future” post status as well as merely “publish”ed posts.

// Allow site visitors (people not logged in to WordPress) to view posts in the "future" post status.
function show_future_posts_pre_get_posts($query) {
    if ( is_single() && !is_admin() && $query->is_main_query() ) { 
        if ( !is_user_logged_in() ) {
            $query->set("post_status", array("publish", "future")); 
        }
    } 
}
add_filter('pre_get_posts', 'show_future_posts_pre_get_posts');

Reference:

Sully Syed

How to drop a MS SQL Server database

Dropping a database in MS SQL Server is not a one-step process; you’ll need to do the following:

USE master;
ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [databasename];

Reference: