User count between a rang of numbers/ranks-CodePudding

I want to return x amount of members/users with a rank from 1-10, 11-24, and another from 25-35. Both users keep showing up in the NCO category. Even when one user is rank 27 and another is rank 13? I have tried messing with the symbols, changing it to just < or > or == but still run into no user showing up to both users showing up.

$stats['afenlisted'] = $pdo->query('SELECT count(*) from personnel WHERE rank >= "25" AND branch = "Air Force" ')->fetchColumn();

$stats['afnco'] = $pdo->query('SELECT count(*) from personnel WHERE rank >= "11" <= "24" AND branch = "Air Force" ')->fetchColumn();

CodePudding user response：

Thanks to Aynber, AbraCadaver, and Honk der Hase. For helping me understand my mistake and getting me the fix for it. So if anyone else is running into this issue, the fix is:

$stats['afenlisted'] = $pdo->query('SELECT count(*) from personnel WHERE rank BETWEEN 25 AND 28 AND branch = "Air Force" ')->fetchColumn();

#For your own code something like this:

$stats['Whatever you want to call it'] = $pdo->query('SELECT count(*) FROM your table name WHERE column BETWEEN x AND y AND if you have another column your trying to get from ')->fetchColumn();

Change the x AND y to whatever number you want, if you don't have another column you're trying to get more stuff for then just remove the AND after y (your number value)

Thanks again to those guys for helping me understand my mistakes and learning more about SQL stuff.

CodePudding user response：

First, your query is faulty

$stats['afnco'] = $pdo->query('SELECT count(*) from personnel WHERE rank >= "11" <= "24" AND branch = "Air Force" ')->fetchColumn();

As said by various people, you have different ways to accomplish a range check. I'll show 3 of them...

First:

SELECT .. FROM .. WHERE rank >= 11 AND rank <= 24

This would be the most compatible way (for SQL queries) to do the check, probably supported by the any database out there, as it uses only basic comparsion operations.

Second:

SELECT .. FROM .. WHERE rank BETWEEN 11 and 24

Though nowadays broadly supported, BETWEEN is a special keyword which might not be known by all database system.

Third:

SELECT .. FROM .. WHERE rank IN(11,12,13,14,15,16,17,18,19,20,21,22,22,23,24)

While this is perfectly valid, it fits better to cases when the range has gaps. (like "11 to 24, but except 17")