1

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?

1

[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 );
  • thank you. what would be then the best way to proces the query for dispaly in plugin (get quote and author from $result query )? $results->quote; and $results->author; ?? – Greg Skala Mar 3 '17 at 0:10
  • Yes, that's exactly how you'd do it. WP_Query::get_results() (without the $output_type param) returns an array of PHP stdClass, the properties of which are the column names from the table. For more info, see Class Reference/wpdb – Paul 'Sparrow Hawk' Biron Mar 3 '17 at 0:23
0

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
");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.