Jump to content
imgrooot

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

Recommended Posts

Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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';
}

 

Share this post


Link to post
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 |
+----------+---------+-------+

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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?"

Share this post


Link to post
Share on other sites

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.