选择随机行mysqli - Select random rows mysqli

我想从表中随机选择行，并从这里的其他问题中获得了以下代码，但我有点困惑于将表名和列名放在哪里，因为我以前从未用mysqli做过这样的选择。有人能帮帮我吗？我的表名是products，列名是title。

我得到：

Fatal error: Call to a member function execute() on a non-object

这是代码：

  SELECT name
  FROM random AS r1 JOIN
   (SELECT (RAND() *
                 (SELECT MAX(id)
                    FROM random)) AS id)
    AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

我试过的：

 $mydb = new mysqli('localhost', 'root', '', 'db');
    $stmt = $mydb->prepare("SELECT title
            FROM products AS r1 JOIN
               (SELECT (RAND() *
                 (SELECT MAX(id)
             FROM random)) AS id)
            AS r2
     WHERE r1.id >= r2.id
     ORDER BY r1.id ASC
     LIMIT 1 ");
 $stmt->execute();

查看准备后是否有任何错误：

 $mydb = new mysqli('localhost', 'root', '', 'db');
    $stmt = $mydb->prepare("SELECT title
            FROM products AS r1 JOIN
               (SELECT (RAND() *
                 (SELECT MAX(id)
             FROM random)) AS id)
            AS r2
     WHERE r1.id >= r2.id
     ORDER BY r1.id ASC
     LIMIT 1 ");
if ( false===$stmt ) {
  die('prepare() failed: ' . htmlspecialchars($mydb ->error));
}

我最近为我的所有sql函数制作了一个sql驱动程序，获得随机记录就是其中之一。

在阅读评论后，我从这个博客中修改了解决方案#3，这就是我想到的：


<?php 
class MyDatabaseDriver {
    protected $DBC;
    protected $SEL_RANDOM_OFFSET;
    protected $SEL_RANDOM_IMAGE;
function __construct($uname, $password, $table="my_table") {
    $this->DBC = new mysqli('localhost', $uname, $password, $table);
    if ($this->DBC->connect_errno) {
        printf("Connect failed: %s'n", $this->DBC->connect_error);
        exit;
    }
    $this->initialize();
}
function __destruct() { $this->close(); }

protected function initialize() {
    $this->SEL_RANDOM_OFFSET = $this->DBC->prepare("SELECT ROUND(RAND() * COUNT(*), 0) AS `offset` FROM `images` WHERE `albumid` = ?");
    $this->SEL_RANDOM_IMAGE = $this->DBC->prepare("SELECT `filename` FROM `images` LIMIT ?, 1");
}
function close() {
    if (!$this->DBC) return;
    $this->SEL_RANDOM_OFFSET->close();
    $this->SEL_RANDOM_IMAGE->close();
    $this->DBC->close();
    $this->DBC = false;
}
function SelectRandomImage($gid) {
    $result = false;
    $this->SEL_RANDOM_OFFSET->bind_param("i", $gid);
    $this->SEL_RANDOM_OFFSET->execute();
    $this->SEL_RANDOM_OFFSET->bind_result($result);
    if (!$this->SEL_RANDOM_OFFSET->fetch()) {
        printf("Select random offset failed: %s'n", $this->SEL_RANDOM_OFFSET->error);
        $result = false;
        $this->SEL_RANDOM_OFFSET->reset();
        return $result;
    }
    $this->SEL_RANDOM_OFFSET->reset();
    $this->SEL_RANDOM_IMAGE->bind_param("i", $result);
    $this->SEL_RANDOM_IMAGE->execute();
    $this->SEL_RANDOM_IMAGE->bind_result($result);
    if (!$this->SEL_RANDOM_IMAGE->fetch()) {
        printf("Select random image failed: %s'n", $this->SEL_RANDOM_IMAGE->error);
        $result = false;
        $this->SEL_RANDOM_IMAGE->reset();
        return $result;
    }
    $this->SEL_RANDOM_IMAGE->reset();
    return $result;
}
} ?>


    
    afaik，这是目前不同网络主机之间兼容性最好的解决方案（从本地主机迁移到远程主机时，我在使用"get_result"等时遇到了一些问题）。它还考虑到，准备好的语句可以以更好的效率重复多次（关于#3的抱怨之一是必须重复才能得到多个结果），因此对象会被重置并保持活动状态，直到类超出范围或直接调用close。
    编辑：在我的OFFSET选择中，我使用"ROUND"，因为我的数据库ID列以1开头，如果您的ID列以0开头，您可能需要使用"FLOOR"