在SQLite中没有返回数据时选择随机数据

Question

I am using SQLite for one of my application I’ve a condition that I first run a query and if it returns result then I retrieve its data but if the first query doesn’t return anything I want to run another query that ll just select any random row from the database and ll return the results.

I’ve devised following code its working fine if it matches the data in the first case but this is not working for the second case.

$sql_query = "SELECT (select count(*)) as count,* FROM item WHERE combo LIKE '%" . $ans_combo . "%' LIMIT 1;";
        $sql_query_bu = "SELECT * FROM item ORDER BY RANDOM() LIMIT 1;";
        ini_set('display_errors', true);
        error_reporting(E_ALL);

        try {
            $dbh = new PDO("sqlite:src/appdb.s3db");
            $dbh -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $dbh -> setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $stmt = $dbh -> prepare($sql_query);
            $stmt -> execute();
            foreach ($stmt as $row) {
                if ($row['count'] != '1') {
                    echo $sql_query_bu . "<br/>";
                    $stmt = $dbh -> prepare($sql_query_bu);
                    $stmt -> execute();
                    foreach ($stmt as $row) {
                        echo $row['name'], " ", $row['name'], " ", $row['name'], "
";
                    }
                }
                echo "Count: " . $row['count'];
                echo $row['name'], " ", $row['name'], " ", $row['name'], "
";
            }
        } catch(Exception $ex) {
            var_dump($ex);
        }

        unset($dbh);
        unset($stmt);

Kindly guide me through this.

Thank you.

Answer 1

If there is no record that matches the WHERE clause the first call to fetch() will return FALSE. In that case you can simply send the ORDER BY RANDOM()* query and fetch the first record.

self-contained example:

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($pdo);


$stmt = $pdo->prepare('SELECT * FROM soFoo WHERE combo=? LIMIT 1');
$stmt->execute( array('comboF') ) ;
$row = $stmt->fetch();
$stmt = null;

if ( !$row ) {
    $row = $pdo->query('SELECT * FROM soFoo ORDER BY RANDOM() LIMIT 1')->fetch();
}
var_dump($row);



function setup($pdo) {
    $pdo->exec('
        CREATE TABLE soFoo (
            combo TEXT,
            x TEXT
        )
    ');

    $stmt = $pdo->prepare('INSERT INTO soFoo (combo,x) VALUES (?,?)');
    $stmt->execute( array('comboA','A') );
    $stmt->execute( array('comboB','B') );
    $stmt->execute( array('comboC','C') );
    $stmt->execute( array('comboD','D') );
}

---

(*) ORDER BY RANDOM() is e.g. in MySQL rather costly. I doubt that SQLite has a special routine for this case. Better search for a good alternative for ORDER BY RANDOM()