Category Archives: Useful Code

Laravel 8 with MySQL 5.5

Ran into a unique situation today: Running Laravel 8 with MySQL 5.5 yields some frustrating errors when migrating the database.

Laravel 8 supports MySQL 5.7+. So if you’re planning on running MySQL 5.5 (because the default Amazon Linux 2 repositories are woefully out of date), then here’s a little code you can add to your migration files to keep unique indexes from failing.

I looked around online, and couldn’t find anyone that was describing this exact situation, so I hacked together a solution.

Create a new file in app/Models/DatabaseVersion.php with the content below:

<?php

namespace App\Models;
use Illuminate\Support\Facades\DB;

class DatabaseVersion {
	static function getDBversion() {
		$label = 'version()';
		$version = DB::select("select version()")[0]->$label;
		(float)$version = mb_substr($version, 0, 6);
		return $version;
	}
}

Then in any migration files were you’re defining unique indexes, add a “use App\Models\DatabaseVersion;” and then specify the archer string length, similar to the users migration show below:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use App\Models\DatabaseVersion;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email',(DatabaseVersion::getDBversion() < '5.7.0'?191:255))->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

The key point in the file above is this little code snippet:

$table->string('email',(DatabaseVersion::getDBversion() < '5.7.0'?191:255))->unique();

This tells Laravel to set the string length to 191 characters instead of 255 for MySQL versions less than 5.7. This is critical because earlier versions of MySQL will fail when trying to create unique indexes with utf-8 varchar(255) fields.

Web Scraping Tools

I’ve been shying away from web-scraping projects because of the amount of anti-scraping tech out there lately.  But today I found a new package that makes web scraping a whole lot easier.  I’m not sure how much anti-scraping software it can get around, but it worked for a recent project, and I’m quite pleased with it.

The software is called “Goutte” (pronounced ‘goot’, i.e. it rhymes with boot and not out).

Some example code, let’s say you’re trying to get the prices for all elements on a page that include a css class of ‘dollarAmountHere’:

use Goutte\Client;
$client = new Client();
$url = 'https://somethingoranother.com/location';
$crawler = $client->request('GET', $url);

$nodeValues = $crawler->filter('span.dollarAmountHere')->each(function ($node) {
return $node->text();
});
$nodeValues now contains an array of the contents of the spans that matched the dollarAmountHere class.  Very handy!
To get a list of links, try this:
$nodeValues = $crawler->filter('a.linkClassName')->each(function ($node) { return $node->attr('href'); });