greatest() function returns undefined property

Question

I'm using the mysql GREATEST() function to compare two table fields and display in a loop either one with the highest integer value. The 2 fields consist of vote scores for posts: UP or DOWN.

function vote_results($post_id) { 
  global $wpdb; 
  $table = $wpdb->prefix . "post_votes";    
  $results = $wpdb->get_row( "SELECT GREATEST (up, down) FROM $table WHERE voted_post_id = $post_id" );
  echo $results->up; //echo if highest value
  echo $results->down; //echo if highest value

}

Then in my loop I call the function but get Notice: Undefined property: stdClass::$up for

echo $results->up;

and the same notice for down. I'm not sure what I'm doing wrong here.

Answer 1

I think you want to use get_var(), not get_row() - looking at the documentation, MySQL's GREATEST just returns a single value.

$results will then just be the value of the greatest (be it up or down).

If you want the greatest up and the greatest down, use;

$wpdb->get_row("SELECT GREATEST(up) AS 'up', GREATEST(down) AS 'down' ...")

Answer 2

Agreeing with TheDeadMedic... If you use GREATEST(), please do yourself a favor by knowing what you're doing. It returns a bigint when fed ints, and your query doesn't give the resulting column any name. So $wpdb will obviously complain when you try to access a column which is not available. :-)

I believe you're seeking this:

SELECT up - down as total, up, down FROM $table WHERE voted_post_id = $post_id