I have custom table where I store quotes and author:

function quote_install(){
global $wpdb;
global $quote_db_version;
$table_name = $wpdb->prefix . 'quote'; 

// create sql your table             
$sql = "CREATE TABLE " . $table_name . " (
  ID int(11) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,   
  quote text NOT NULL,
  author text NOT NULL,
  qtag ENUM('G', 'W', 'Z', 'H', 'M') NOT NULL default 'G',
  PRIMARY KEY  (ID)
);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql); }

Then I want to get single row with quote and author to display.

function read_single_Quote( $id=NULL ) {
global $wpdb;

 $table_name = $wpdb->prefix . 'quotes';
// random select
 if($id ==NULL){
    $sql = $wpdb->prepare( "
        SELECT *
        FROM {$wpdb->prefix}'quotes'
        ORDER BY RAND()
        LIMIT 1
    ");
 } //get the row id = $id
else {
    $sql = $wpdb->prepare( "
    SELECT *
    FROM {$wpdb->prefix}'quotes'
    WHERE ID = %d
    LIMIT 1
    ", $id );
 }

$result = $wpdb->get_results( $sql );

// databse error, return false
if ( ! $result ) { return false; }

// return first result
return $result[0];

}

What is wrong? What is the most efficient way to get random row from custom table?

I have custom table where I store quotes and author:

function quote_install(){
global $wpdb;
global $quote_db_version;
$table_name = $wpdb->prefix . 'quote'; 

// create sql your table             
$sql = "CREATE TABLE " . $table_name . " (
  ID int(11) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,   
  quote text NOT NULL,
  author text NOT NULL,
  qtag ENUM('G', 'W', 'Z', 'H', 'M') NOT NULL default 'G',
  PRIMARY KEY  (ID)
);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql); }

Then I want to get single row with quote and author to display.

function read_single_Quote( $id=NULL ) {
global $wpdb;

 $table_name = $wpdb->prefix . 'quotes';
// random select
 if($id ==NULL){
    $sql = $wpdb->prepare( "
        SELECT *
        FROM {$wpdb->prefix}'quotes'
        ORDER BY RAND()
        LIMIT 1
    ");
 } //get the row id = $id
else {
    $sql = $wpdb->prepare( "
    SELECT *
    FROM {$wpdb->prefix}'quotes'
    WHERE ID = %d
    LIMIT 1
    ", $id );
 }

$result = $wpdb->get_results( $sql );

// databse error, return false
if ( ! $result ) { return false; }

// return first result
return $result[0];

}

What is wrong? What is the most efficient way to get random row from custom table?

• wpdb
• table

Share Improve this question edited May 25, 2019 at 3:13 butlerblog 5,13133 gold badges2828 silver badges4444 bronze badges asked Mar 2, 2017 at 23:23 Greg SkalaGreg Skala 811010 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

[Note: @marwyk87 posted his answer while I was composing this, which represents another way to fix the problem]

You've got a simple syntax error in your SQL, because of the way you're referencing the table name. You should just say

$sql = $wpdb->prepare( "
    SELECT *
    FROM {$wpdb->prefix}quotes
    ORDER BY RAND()
    LIMIT 1
    ");

and

$sql = $wpdb->prepare( "
    SELECT *
    FROM {$wpdb->prefix}quotes
    WHERE ID = %d
    LIMIT 1
    ", $id );

I think you might have an syntax problem in your TSQL. If your doing

$table_name = $wpdb->prefix . 'quotes';

then you can have

$sql = $wpdb->prepare( "
    SELECT *
    FROM ".$table_name."
    ORDER BY RAND()
    LIMIT 1
");