Select random rows mysqli

Question

I would like to select random rows from table and got the below code from other questions here but I am kinda confused where to put the table name and the column name as I never did such selection before with mysqli. can someone please help me out? my table name is products and the column name is title.

I am getting:

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

Here is the code:

  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

what I tried:

 $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();

Does this answer your question? [mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Dharman, Mar 21 '20 at 14:36

score 0 · Accepted Answer · answered Aug 21 '13 at 23:44

0

See if you have any error after prepare:

 $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));
}

answered Aug 21 '13 at 23:44

Sujit Poudel

541
7
19

I have fixed the errors but when I try using `$result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { echo $row['title']; }` to fetch the rows I am getting error. How should I fetch the row? Thanks for the help – amdvb Aug 21 '13 at 23:53
Thanks this works. I just had forgotten `execute`. Thanks very much for the help. – amdvb Aug 21 '13 at 23:56
You can bind the result and fetch them. Have a look here:http://php.net/manual/en/mysqli-stmt.fetch.php – Sujit Poudel Aug 21 '13 at 23:58
A much simpler solution syntax-wise is select * from tbl order by rand() limit 1; because it doesn't require reading the max(id) from the table. – developerwjk Aug 22 '13 at 20:51

SilverX · Answer 2 · 2013-10-11T17:00:33.643

I recently made an sql driver for all my sql functions and obtaining a random record was one of them.

I adapted solution #3 from this blog after reading the comments and this is what I came up with:



<?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, this is the solution with the best compatibility among different webhosts at the moment (I had some issues using 'get_result' etc when migrating from local to remote hosting). It also takes into account that prepared statements can be repeated many times with better efficiency (which was one of the complaints about #3 is that it has to be repeated to get more than one result) so the objects are reset and kept alive until the class falls out of scope or close is called directly.

EDIT: In my OFFSET selection I use "ROUND" because my database ID column starts with 1, if your ID column starts with 0 you may want to use "FLOOR"

Select random rows mysqli

2 Answers2