Jump to content

Is this query correct? Something going wrong with getting MAX value.


imgrooot

Recommended Posts

I have a table where I would like to find the maximum value from a certain column and retrieve a single result. But it doesn't seem to work properly.

I don't get any errors. The issue is that it's retrieving the entry_id from one row and the votes from another row. But I would like the row with max value to retrieve the same row's entry_id.

Here is my code. Can you see what's wrong with it?

$find_entries = $db->prepare("SELECT entry_id, MAX(votes) as vts FROM entries WHERE votes > :votes");
$find_entries->bindValue(':votes', 0);
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['vts'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

 

Link to comment
Share on other sites

If you ever need functions like MAX or SUM or COUNT and you don't want to count the entire table at once then you need a GROUP BY clause.

If you want to select a column and then use those functions on others, you should probably be GROUPing BY that first column.

Link to comment
Share on other sites

7 hours ago, Barand said:

Perhaps you want to do something like this


SELECT entry_id
     , votes
FROM entries
WHERE votes = ( SELECT MAX(votes) FROM entries );

 

Perfect! That's exactly it. 

Here is my updated query with your fix.

$find_entries = $db->prepare("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['votes'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

 

Link to comment
Share on other sites

1 hour ago, Barand said:

No need to prepare/execute a query with no input parameters. Just use


$find_entries = $db->query("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");

 

So then it would be like this? But If I am binding parameters, then I use the prepare/execute method yes?

$find_entries = $db->query("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['votes'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

 

Link to comment
Share on other sites

You could find the one with the highest votes with ...

SELECT entry_id, votes FROM entries order by votes DESC LIMIT 1;

... but you wouldn't know if there were two or more with the same highest number. Sod's Law dictates that if you have LIMIT N then there will be N+1 tying in the top position.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.