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

[imgrooot]

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 June 26, 2019 by imgrooot

[chhorn]

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

[imgrooot]

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';
}

 

[Barand]

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 |
+----------+---------+-------+

 

[requinix]

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

 

[Barand]

That way is far too easy.?

(In defence of my method, it would enable the extraction of all contest/category winners with a single query)

[imgrooot]

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.

[Barand]

That answers the "how" question, but have we answered your original question, viz. "Why isn't it able to find the max value if it's less than 5?"