0

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 :)

JasperDaDolphin
  • 172
  • 1
  • 9

1 Answers1

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";
mtr.web
  • 1,505
  • 1
  • 13
  • 19
  • Probably the qualified-cirteria alone would be enough, not? – jonas3344 Mar 29 '18 at 13:36
  • 3
    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. – RiggsFolly Mar 29 '18 at 13:37
  • 1
    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.. – mtr.web Mar 29 '18 at 13:38
  • Right; I was never a fan of "Try this" myself @RiggsFolly – Funk Forty Niner Mar 29 '18 at 13:38
  • Assuming `points` cannot be negative, this would do. Otherwise you would need the extra condition to check for 0-values. – jeroen Mar 29 '18 at 13:39
  • 1
    @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 – Your Common Sense Mar 29 '18 at 13:41
  • So, the answerer can't speak for themselves, and [gets someone else to add detail](https://stackoverflow.com/revisions/49557513/2); *ahem*. – Funk Forty Niner Mar 29 '18 at 13:42
  • 2
    I think `where points > 0 or (points = 0 and qualified > 0)` would be better as OP wants :P ;) – DEarTh Mar 29 '18 at 13:44
  • @FunkFortyNiner and @RiggsFolly, I have edited the answer to explain it in clear terms. @jeroen, that is a good point, and it could be avoided by using `points <> 0 or qualified <> 0`, but I get the impression that this is a non-negative number from the question – mtr.web Mar 29 '18 at 13:45
  • @JitendraSoftgrid, in the OP's example, that would exclude Amy who has points = 0 and qualified = 1. It doesn't seem that the OP wants those people excluded. – mtr.web Mar 29 '18 at 13:47
  • @JitendraSoftgrid, that was my misunderstanding. I will edit the answer to reflect – mtr.web Mar 29 '18 at 13:50
  • Works perfect, Thanks a lot. I'll have to revise my SQL ;) – JasperDaDolphin Mar 29 '18 at 14:03
  • I am glad it worked for you. If it doesn't require any revisions, don't forget to accept the answer so that others with similar problems might benefit. – mtr.web Mar 29 '18 at 14:25