Randomizing MySQL query results

Published on 2020-01-27 • Modified on 2020-01-27

In this snippet, we will see how to randomize MySQL query results. On this website, I am using this functionality for the random snippet feature. In this case, I only need to have one result so I add the LIMIT 1 SQL clause. On the run page, refresh the page to verify that the snippet ID changes (or not, it's random!). Be careful not to use this on large results sets (more than 10000 rows) or you may have performance problems.

---

<?php

declare(strict_types=1);

namespace App\Controller\Snippet;

use App\DBAL\Types\ArticleType;
use Doctrine\DBAL\Connection;

/**
 * I am using a PHP trait to isolate each snippet in a file.
 * This code should be called from a Symfony controller extending AbstractController (as of Symfony 4.2)
 * or Symfony\Bundle\FrameworkBundle\Controller\Controller (Symfony <= 4.1).
 * Services are injected in the main controller constructor.
 *
 * @property Connection $connection
 */
trait Snippet74Trait
{
    public function snippet74(): void
    {
        $sql = sprintf("SELECT * FROM article WHERE type = '%s' ORDER BY RAND() LIMIT 1", ArticleType::TYPE_SNIPPET);
        $article = $this->connection->fetchAllAssociative($sql)[0] ?? [];

        echo 'Random snippet id: '.$article['id'];

        // That's it! 😁
    }
}

 Run this snippet  ≪ this.showUnitTest ? this.trans.hide_unit_test : this.trans.show_unit_test ≫  More on Stackoverflow   Read the doc  More on the web

  Work with me!

<?php

declare(strict_types=1);

namespace App\Tests\Integration\Controller\Snippets;

use App\DBAL\Types\ArticleType;
use App\Tests\WebTestCase;
use Doctrine\DBAL\Connection;

/**
 * @see Snippet74Trait
 */
final class Snippet74Test extends WebTestCase
{
    private Connection $connection;

    protected function setUp(): void
    {
        $this->connection = $this->getDoctrineDefaultConnection();
    }

    /**
     * @see Snippet74Trait::snippet74
     */
    public function testSnippet74(): void
    {
        $sql = sprintf("SELECT * FROM article WHERE type = '%s' ORDER BY RAND() LIMIT 1", ArticleType::TYPE_SNIPPET);
        $article = $this->connection->fetchAllAssociative($sql)[0] ?? [];
        self::assertIsArray($article);
        self::assertSame(ArticleType::TYPE_SNIPPET, $article['type']);
    }
}

---