multiple sql selects pdo

Question

For some reason my queries aren't working in my PHP script, I'm essentially trying to make a query that gets all players whos points are greater than 0 though I'm also trying to get some players whos points are equal to 0 but whos qualified amount is greater than 0. My example code is below.

Example

 $sql = "
            SELECT * FROM allplayers WHERE points > 0 ORDER BY points DESC
            SELECT * FROM allplayers WHERE points = 0 and qualified > 0
    ";

    $q = $pdo->query($sql);
        while ($dnn = $q->fetch()) { 
              //script
        }

Table example

player       points         qualified

Alex         90             1
Amy          0              1
Jimmy        200            0
John         0              0

The query is trying to get back everyone in the table except john whos points = 0 and who hasn't qualified (qualified = 0) Thanks for reading :)

PDO does not support Multiple queries and you would need a ; between queries even if it did — RiggsFolly
– RiggsFolly, Commented Mar 29, 2018 at 13:33
@FunkFortyNiner Do you often while away the hours pondering these mystical detials :) :) :) — RiggsFolly
– RiggsFolly, Commented Mar 29, 2018 at 13:36
All you seem to need is an OR as the sort order will put the 0-point records last anyway... — jeroen
– jeroen, Commented Mar 29, 2018 at 13:37
Use one query with where points > 0 or (points = 0 and qualified > 0) — DEarTh
– DEarTh, Commented Mar 29, 2018 at 13:45

mtr.web · Accepted Answer · 2018-03-29 13:54:22Z

4

As mentioned in comments, you cannot do multiple queries, and you forgot to end the first one with a semicolon. You don't need multiple queries, however. If what you want to do is eliminate any entities that have a value of 0 for both the points and qualified columns, you just need to select any that have values > 0 for either of the columns. This should work:

EDIT: As @JitendraSoftgrid pointed out, the more appropriate answer would use your original conditions because it would account for any negative values in the points and qualified columns. Here is the combined version of your two queries:

$sql = "SELECT * FROM allplayers 
        WHERE points > 0
        OR (points = 0 and qualified > 0)
        ORDER BY points DESC";

edited Mar 29, 2018 at 13:54

answered Mar 29, 2018 at 13:35

mtr.web

1,5251 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

jonas3344 Over a year ago

Probably the qualified-cirteria alone would be enough, not?

RiggsFolly Over a year ago

And ALSO Why should the OP try this? Good answers will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer.

mtr.web Over a year ago

The OP wants to eliminate anyone who has a value of 0 for both fields. So no union is needed, and this should do the trick..

Your Common Sense Over a year ago

@jeroen that's a sensible answer. it can be improved, but the most important message "the only reason to run several queries is a lack of SQL knowledge" is all right

DEarTh Over a year ago

I think where points > 0 or (points = 0 and qualified > 0) would be better as OP wants :P ;)

|

Collectives™ on Stack Overflow

multiple sql selects pdo

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related