Compare two tables to get all unlinked entries

I have the problem on RolandRadio, that a lot of people try to modify the charts and register to vote. All users not verified by Mail are deleted after 24 hours to avoid fake users but some of them already mass-voted on titles.

So i needed to find out if there are ratings by users already non existent in the users table. I came up with this mySQL-Solution:

select * 
from ratings A
Where Not exists (select 1 from users B Where A.model_id = B.id)

Or without the aliasing:

select * 
from ratings
WHERE NOT exists (select 1 from users Where ratings.model_id = users.id)

So i select all from the table ratings and get all those entries where the model_id-column of the ratings-table is not in the id of the users-table.

This brought up 1600 ratings of already deleted users. i hate it that people are so manipulative on the charts. Now i need to do a cron-tab-command to delete ratings of deleted users every day. Also i included

     if (Auth::user()->verified != 1) {
            session()->flash('message', 'You must verify your email first!');
        } 

into the rating function to avoid users not verified to rate on titles.

1071 Specified key was too long;

If you use an older Version of mySQL or MariaDB Laravel throws an error on the first migration on your database. Thats cause the defaultStringLentth needs to be set to a maximum length of 191 characters.

    public function boot()
{
Schema::defaultStringLength(191);
}
}

Add this to your AppServiceProvider.php File and rerun your migration

Dont forget to add

use Illuminate\Support\Facades\Schema;

Adding a command to laravel deleting all unwanted users :-)

As spammer start flooding my users table with fake-accounts i startet to develop a command for laravel to run in a cron job for deleting all users which didnt verified their email within the last 24 hours.

A nice how-to do a laravel console command for cron can be found at: https://tutsforweb.com/how-to-set-up-task-scheduling-cron-job-in-laravel/

This is my routine to select all users which haven’t confirmed their email and their register date is older than 24 hours.

    public function handle()
    {
         $results = User::whereNull('email_verified_at')->get();
        $results = $results
            ->where('created_at', '<', Carbon::now()->subDays(1)->toDateTimeString());

        foreach ($results as $result)
        {
            $result->delete();
        }

    }

Added the following command to the crontab by crontab -e on the webserver to execute the command:

cd /var/www/vhosts/rolandradio.net/rolandradio2020 && /opt/plesk/php/7.4/bin/php artisan schedule:run >> /dev/null 2>&1

Set Laravel-Project to “down” during updates

Everytime i start an update of the whole project it takes hours to upload all the new updated files to my web server.

To avoid a massive downtime for the user without informing them about the status and displaying any errors i just touch a file within the project:

project->storage->framework

there i add a file called “down” and the site goes into “down-mode” – atfer i finished my work i rename the file to “down-” to bring up the site agein. I configured the Error 503 page to display a nicer version of the error.

How to disable the Cache in Laravel

Sometimes its necessary to disable the caching in Laravel. To do so a new cache-driver “none” must be added to the cache-configuration-file in config/cache.php

'stores' => [
    //...
    'none' => [
        'driver' => 'null',
    ],
  ],

After that you have to set this cache-driver in your .env-File and you Cache is disabled

Edit .env and change the line
CACHE_DRIVER=file
to
CACHE_DRIVER=none

Clearing some Caches in Laravel

Clear Application Cache:

php artisan cache:clear

Clear Route Cache

Throws an error if CACHE_DRIVER=none

php artisan route:clear

Clear Configuration Cache

Throws an error if CACHE_DRIVER=none

php artisan config:clear 

 Clear Compiled Views Cache

Throws an error if CACHE_DRIVER=none

php artisan view:clear 

How to install XAMP, Laravel and PHPStorm on Windows 10

Installing XAMP on Windows 10

First i installed XAMP 64-bit-version from https://www.apachefriends.org/de/download.html This package installs a web-server and a database-server, so the basics to do some nice online development in a local environment. As said i only installed Apache, mySQL-Server and FakeMail. No TomCat, no Mailserver, no Statistics, no Perl, cause no other packages are needed for my Laravel and mySQL-Project. If i want to test mail functionality later on i will use a real external mail server to be sure everything works as expected.

Add your software to your environment path

After installing XAMP i added php and mysql to the environment path of my user, so i can start php and the mysql command on every path i will be in my terminal. That is needed to run laravel artisan and other php-based commands from the shell.

To do this type “path” in your Windows search and start the system settings to add more directories to your path:

Install Node.js and Composer

Next i installed the current 64 bit version of Node.js from https://nodejs.org/en/ without any extras, following with the Windows Version of Composer from https://getcomposer.org/download/ – First entry on this page is “Windows Installer”. Load the .exe and install Composer. Make sure Composer finds your PHP-Directory from XAMP, which should be c:\xampp\php\php.exe  but may differ in your setup and depends on the directory you installed XAMP first. No options where installed with composer either.
Check in your terminal shell if composer is available with the command composer -V 

Install Laravel

Now install Laravel by typing composer global require laravel/installer  on your shell in your home directory

and add %USERPROFILE%\AppData\Roaming\Composer\vendor\bin  to your path.
you can check your path with the command “PATH” and will get some like this result:

Create a new Laravel-Project

You can add a new project by typing laravel new projectname  but i prefer doing it on the IDE side, so i start up PHPSTORM and open a new Project. chose laravel/laravel as package, leave version on “default” to use latest Laravel version and just click create. Now wait some time and your first laravel project will be installed. This will take several minutes. Stay tuned. Don’ panic 🙂

After that PHPStorm asks if you want to install the npm dependencies. Yes you will. You can do it manual by typing npm install  in your project directory. this also will take some time. oh, boy.

Finally check if everything is installed OK and start you laravel project by typing “php artisan serve” and check your localhost in your browser if there is a laravel page.
you also will see the favicon served to your browser in the protcol of the laravel server

Done. Happy developing 🙂