Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
What does var_dump($results); get you? –  Rarst Dec 3 '10 at 16:39
    
I get this, object(stdClass)#91 (1) { ["GREATEST(up, down)"]=> string(2) "74" –  wpStudent Dec 3 '10 at 16:53
add comment

2 Answers

up vote 4 down vote accepted

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' ...")
share|improve this answer
    
Thanks for the answer! I actually want up or down not both otherwise I would use a regular query. I used get_row() but now I get Trying to get property of non-object for echo $results->up; and echo $results->down;, this using the initial greatest() –  wpStudent Dec 4 '10 at 0:12
    
Yeah... so use get_var() instead, and the returned result will simply be the value of GREATEST –  TheDeadMedic Dec 4 '10 at 14:52
    
Ok I used get_var() like so $results = $wpdb->get_var($wpdb->prepare( "SELECT GREATEST(up, down) FROM $table WHERE voted_post_id = $post_id" ) ); and then I use <?php echo $results->up; ?> and <?php echo $results->down; ?> to retrieve either up or down, whichever is highest. I get Trying to get property of non-object I don't understand why.. I already have the $wpdb global –  wpStudent Dec 4 '10 at 17:41
    
maybe it's not picking up post_id ? –  wpStudent Dec 4 '10 at 17:47
    
No, using get_var(), $results is just a value (the greatest of either up or down) - there is no up or down property, as $results is not an object! –  TheDeadMedic Dec 4 '10 at 18:19
show 1 more comment

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
share|improve this answer
    
I tested GREATEST() in phpmyadmin and it works fine. It returns the highest values from either the up or down columns. Maybe my question wasn't clear, sorry. I'm just having trouble with wp –  wpStudent Dec 4 '10 at 0:16
    
also the up and down fields are bigint –  wpStudent Dec 4 '10 at 0:22
1  
Oh, I thought you were seeking the highest up minus down. If not, in that case yeah: SELECT greatest(up, down) as highest, up, down FROM $table WHERE voted_post_id = $post_id. Note the as highest –  Denis Dec 4 '10 at 9:18
    
Thanks Denis!! Sorry again for not properly explaining. –  wpStudent Dec 4 '10 at 17:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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