Jump to content

Why isn't it able to find the max value if it's less than 5?


imgrooot

Recommended Posts

The issue I'm having is that this query won't find a max value record if it has less than 5 votes. It'll only show the results of an entry has 5 or greater votes. Why is that?

$contest_id = 5;
$category_id = 8;

$find_entries = $db->prepare("SELECT entry_id, user_id, votes FROM contest_entries 
WHERE contest_id = :contest_id AND category_id = :category_id AND e_status = :e_status AND votes = (SELECT MAX(votes) FROM contest_entries) LIMIT 1");
$find_entries->bindParam(':contest_id', $contest_id);
$find_entries->bindParam(':category_id', $category_id);
$find_entries->bindValue(':e_status', 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'];
    $user_id   =	$row['user_id'];
    $votes     =	$row['votes'];
  }
  echo $entry_id;
  
} else {
	echo 'nothing';
}

 

Edited by imgrooot
Link to comment
Share on other sites

5 minutes ago, chhorn said:

For what data? Use SQLite in-memory to mock a databse.

Not sure what you mean.

But here is another version of the code. This seems to work fine.

$contest_id = 5;
$category_id = 8;

$find_entries = $db->prepare("SELECT entry_id, user_id, votes FROM contest_entries 
WHERE contest_id = :contest_id AND category_id = :category_id AND e_status = :e_status AND votes > :votes ORDER BY votes DESC LIMIT 1");
$find_entries->bindParam(':contest_id', $contest_id);
$find_entries->bindParam(':category_id', $category_id);
$find_entries->bindValue(':e_status', 0);
$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'];
    $user_id   =	$row['user_id'];
    $votes     =	$row['votes'];
  }
  echo $entry_id;
  
} else {
	echo 'nothing';
}

 

Link to comment
Share on other sites

I can't see your data but one possible explanation is that you have a situation like this

mysql> select * from contest_entries;
+----------+------------+-------------+---------+-------+----------+
| entry_id | contest_id | category_id | user_id | votes | e_status |
+----------+------------+-------------+---------+-------+----------+
|        1 |          5 |           8 |     101 |     2 |        0 |
|        2 |          5 |           8 |     102 |     3 |        0 |
|        3 |          5 |           8 |     103 |     4 |        0 |
|        4 |          1 |           9 |     104 |     1 |        0 |
|        5 |          1 |           9 |     105 |     3 |        0 |
|        6 |          1 |           9 |     106 |     5 |        0 |
+----------+------------+-------------+---------+-------+----------+

The MAX(votes) is 5, but that was in contest 1, category 9. Your query for contest 5 therefore returns no match.

mysql> SELECT entry_id
    ->      , user_id
    ->      , votes
    -> FROM contest_entries
    -> WHERE contest_id = 5
    ->       AND category_id = 8
    ->       AND e_status = 0
    ->       AND votes = (SELECT MAX(votes) FROM contest_entries)
    -> LIMIT 1;
Empty set (0.00 sec)

Possible solution...

SELECT entry_id
     , user_id
     , votes 
FROM contest_entries c
     JOIN (
            SELECT contest_id
                 , category_id
                 , MAX(votes) as votes
            FROM contest_entries
            GROUP BY contest_id, category_id
          ) mx USING (contest_id, category_id, votes)
WHERE contest_id = 5 
      AND category_id = 8 
      AND e_status = 0 
LIMIT 1;
+----------+---------+-------+
| entry_id | user_id | votes |
+----------+---------+-------+
|        3 |     103 |     4 |
+----------+---------+-------+

 

Link to comment
Share on other sites

4 hours ago, requinix said:

Maybe it's just me but why not


SELECT entry_id, user_id, votes
FROM contest_entries 
WHERE contest_id = :contest_id AND category_id = :category_id AND e_status = :e_status
ORDER BY votes DESC
LIMIT 1

 

Which I came up with in my 2nd reply. It works.

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.