How to do a Count-IF

[wizzuriz]

Hello All.

When I use Numbers ( excel on a max ) I can use a count if function, but when I check the web for a countIF function in php I don't get anything.

what I like to is making a function that can count a value in a row like; ">=7" value is 7 or bigger.

=COUNTIF(C2:Q2, ">=7") that is the function in numbers, but how do I create one like it in php?

Please let me know if you got a idea.

Best regards
Wizzuriz

[libeco]

There's no such function, but it would be very easy to create. What have you tried so far? Where are the numbers stored? An array, a string?

[jacek]

If you are talking about a database, you can do

[syntax=sql]SELECT COUNT(1) FROM `table` WHERE `column` >= 7[/syntax]

If not, please explain what you are actually trying to do.

[wizzuriz]

Hello Jacek.

thanks for the help, I'll try to explain it.

I have a table with columns level, user_id, test_1, test_2, test_3, test_4, test_5.
Now I want to see how many times the user scored 3 points in the test where user_id = $uid. the result should be 5 if the user scored 3 in all 5 test. ( I want to amount of times the user scored 3 points )

Best regards
wizzuriz

[wizzuriz]

Hello Again.

It might help if you know what I'm trying to do here, please excuse my lag of talent to explain.

I'm trying to make a score system where the user can achieve a league based on the score he has.

There is 15 different tests, each test have 18 level difficulties, you have to complete a test to obtain a level.
if you test score is 0 you will start out with beginner league.
If you obtain a level 1 in a test you will become Silver League.
If you obtain a level 3 in 3 different test you will become Gold League.
If you obtain a level 5 in 5 different test you will become Diamond League.
If you obtain a level 7 in 7 different test you will become master League.

Now I made a database table where I will have a row with user_id, test_1, test_2, test_3.... test_15. I will then update the score in each column so if the user have a score of level 3 in 3 or more test he will become gold league.

Now we move on to the code thats the hard part.
[syntax=php]
// Check Each test level and count them
function count_league_score($email){
$email = mysql_real_escape_string($email);

$um = $_SESSION['email'];

$sql = "SELECT test_1, test_2, test_3, test_4, test_5, test_6, test_7, test_8, test_9, test_10, test_11, test_12, test_13, test_14, test_15
FROM
level_and_league INNER JOIN users
ON level_and_league.user_id = users.user_id
WHERE `user_email` = '{$um}'";

$result = mysql_query($sql);

if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}

if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}

$test_score = mysql_fetch_assoc($result);
return $test_score;

if ($test_score >= 7 ) // Require a count of 7 or more of the level 7
{ $league_cal = "Master League";}

elseif ($test_score >= 5 ) // Require a count of 5 or more of the level 5
{ $league_cal = "Diamond League";}

elseif ($test_score >= 3 ) // Require a count of 3 or more of the level 3
{ $league_cal = "Gold League";}

elseif ($test_score >= 1) // Require a count of 1 or more of the level 1
{ $league_cal = "Silver League";}

elseif ($test_score <= 0 and $test_score > -100 ) // Require a count of 0 or more of the level 0
{ $league_cal = "Beginner League";}

else echo mysql_error ();

return($league_cal);
[/syntax]

If you have a code that works please do let me know.

best regards
Wizzuriz

[jacek]

Do you have to store the score for each test, it might be easier to just store the one column which is their overall score.

Regarding your code, you can't have two return statements in a function, the first one will effectively cancel out the second one. The other thing I spotted is that you are not using mysql_fetch_assoc correctly, it returns an array so you can't just compare it to a number directly, you would need to do something like

[syntax=php]if ($test_score['test_1'] > 7){[/syntax]

[wizzuriz]

Hello Jacek.

To the question on the need to store each test score. Yes I will need that, last say I have a score of 5 in each test = 15*5 = 75 points, now if league require a score of 7 in 7 different test = 7*7 = 49 point then the user will obtain gold league because he scored 75 point but he don't meet the requirement of scoring min 7 point in min 7 tests. This is how I end up doing it please let me know if there would be a better way to code it.

DB_table. user_id, test_1, test_2 .... test_15
user row 1 7 7 7

column 1 = user_id, then 15 columns one for each test.
// all I want is to show the league name ( some text for example. Gold League )
this is my function.
[syntax=php]

//-------League Calculation--
function league_calculation(){

$email = mysql_real_escape_string($email);

$um = $_SESSION['email'];

$sql = "SELECT `test_1`, `test_2`, `test_3`, `test_4`, `test_5`, `test_6`, `test_7`, `test_8`, `test_9`, `test_10`, `test_11`, `test_12`, `test_13`, `test_14`, `test_15`
FROM
`test_score` INNER JOIN `users`
ON `test_score`.`user_id` = `users`.`user_id`
WHERE `user_email` = '{$um}'";

$result = mysql_query($sql);

$row = mysql_fetch_array($result);

$new_array = array_count_values($row);

while (list ($key, $val) = each ($new_array)) {

if ($key >= 0 ) {if ($val >= 0 ) { $league_cal = "Beginner League";}}
if ($key >= 1 ) {if ($val >= 1 ) { $league_cal = "Copper League";}}
if ($key >= 3 ) {if ($val >= 3 ) { $league_cal = "Bronze League";}}
if ($key >= 5 ) {if ($val >= 5 ) { $league_cal = "Silver League";}}
if ($key >= 7 ) {if ($val >= 7 ) { $league_cal = "Gold League";}}
if ($key >= 10) {if ($val >= 10) { $league_cal = "Platinum League";}}
if ($key >= 12) {if ($val >= 12) { $league_cal = "Elite League";}}
if ($key >= 15) {if ($val >= 14) { $league_cal = "Super Elite League";}}

else echo mysql_error ();
}
// $league_cal is now the league name ( text to display )
return $league_cal;
}

[/syntax]

Because the highest league is last the user will display the highest league achieved.

For example, if the user score 3*3 and 5*5 and 7*7 it will display gold league even when he meet the requirement for Bronze and Silver.

Please let me know if you think there is a problem with this code.

If any one else need a league calculation function feel free to use this and if you improve it please do let me know.

best regards
Wizzuriz

[jacek]

I'm trying to think of a way to do it more dynamically and to be honest I don’t think there is one (or I can't think of it), so you probably have done it the best way. It looks neat anyway.

[wizzuriz]

Hello Jacek,

Thanks, let me know if you come up with something.

Best regards
Wizzuriz