# Select last 5 entries

I have a query that selects the last five entries just fine. After selecting the last five I want to display the smallest number here is my query and results.

``````<?php
\$result = mysql_query("SELECT * FROM rounds where username ='\$user'");
\$num_rows = mysql_num_rows(\$result);
if (\$num_rows == 1)
echo "You have not added any rounds yet.";
else
echo "You have \$num_rows rounds in the system.";
echo "<br />";
\$result = mysql_query("SELECT * FROM rounds WHERE username = '\$user' ORDER BY datePlayed ASC LIMIT 5");
while (\$row=mysql_fetch_array(\$result)) {echo "Current Handicap: ",\$row['handicap'],"";}
?>
``````

Results:
You have 7 rounds in the system.
Current Handicap: -2.2Current Handicap: -2.2Current Handicap: -2.2Current Handicap: -2.2Current Handicap: -3.0

I only want the first -2.2 to be displayed but it has to be to lowest of the last 5 entries any help is appreciated.

Pull the records, shove them into an array, rsort it, and read entry 0.

(PS: -3 is the one you want displayed, not -2.2, because that’s the lowest.)

After thinking and messing around with the code more, I’m trying to make a handicap calculator…
these are the requirements for a accurate calculation

As we see in the above example, this golfer with a handicap of 12.7, has also turned in a score of 97. Thus, when he has a bad round of 95 or higher, people are likely to sneer and say, “Are you sure your are a 12 handicap?”
This is because people, in general, do not really understand the golf handicap system. Your golf handicap represents your best rounds of golf not your average rounds of golf.
As you play more golf, you factor in more scores. With five rounds of golf, only the one score is factored. With seven rounds of golf, you average your two lowest differential numbers, and then multiply by .96.
Finally, once you have played 20 rounds of golf, you take the average of your 10 lowest differentials and multiply that number by .96. Obviously then, the more golf you play, the more accurate your handicap becomes.
When a user submits a round the validation.php page does most of the calculation. This is my validation page

``````\$selectedcourse = \$_POST['course'];

//if the name exists it gives an error
\$result = mysql_query("SELECT * FROM courses WHERE courseName = '\$selectedcourse'");
while (\$row=mysql_fetch_array(\$result)) {
\$par = \$row['par'];
\$rating = \$row['courseRating'];
\$slope = \$row['slopeRating'];

\$difference = \$_POST['score'] - \$par;
\$coursescore = \$_POST['score'] - \$rating;
\$coursemultiply = \$coursescore * 113;
\$differental = \$coursemultiply / \$slope;
\$handicap = \$differental * .96;

// now we insert it into the database

\$insert = "INSERT INTO rounds (username, dateplayed, courseplayed, par, score, difference, differental, handicap, coursecondition, weather, wind)

VALUES ('".\$_SESSION['username']."', '".\$_POST['dateplayed']."', '".\$_POST['course']."', '".\$row['par']."', '".\$_POST['score']."', '".\$difference."', '".\$differental."', '".\$handicap."', '".\$_POST['condition']."', '".\$_POST['weather']."', '".\$_POST['wind']."')";

``````

So in the “rounds” table the differential and the handicap is already present and correct. On the main profile for the member I want to show their current handicap using the above guidelines. I have been trying to do this like so

``````\$result = mysql_query("SELECT * FROM rounds where username ='\$user'");
\$num_rows = mysql_num_rows(\$result);
if (\$num_rows >= 1)
echo "You have \$num_rows round in the system.";
else
echo "You have \$num_rows rounds in the system.";
echo "<br />";
\$result = mysql_query("SELECT * FROM rounds WHERE username = '\$user' ORDER BY differental ASC");
while (\$row=mysql_fetch_array(\$result))
\$num_rows = mysql_num_rows(\$result);
if (\$num_rows == 0) { echo"";}
elseif (\$num_rows == 1){
\$handicap = \$row['differental'] * 0.96;
echo "Current Handicap: ",\$handicap,"";}
elseif (\$num_rows == 2)  echo"test";
``````

The above code should work if only one round is in the table but the result of the if statement is
You have 1 round in the system.
Current Handicap: 0
Im pretty sure the above code will never work since I have no way to get the average differential. I think I need to use a function and I dont have a lot of experience with function and am not sure if I can add date from database in a function.

Thanks StarLion, and yes my bad -3 should be the only result showing… Would an array still be the way to go considering my last post?

if (\$num_rows >= 1)

Fairly sure you meant == here, not >=.

As far as the calculations go… you should be storing the differentials, not trying to do the handicap math on an individual game. http://en.wikipedia.org/wiki/Handicap_(golf)

I now understand how to calculate the handicap. The part I am having trouble with is figuring out how to code it. To start out they may only have 1 to 4 rounds in the system. So I want to have a message saying "You have not entered enough rounds to calculate a handicap."But once they enter 5 or more rounds to calculate the handicap according to the table below

Number of rounds Differentials to use
5 or 6 lowest 1
7 or 8 lowest 2
9 or 10 lowest 3
11 or 12 lowest 4
13 or 14 lowest 5
15 or 16 lowest 6
17 lowest 7
18 lowest 8
19 lowest 9
I have changed the validation and insert query to only store the differentials of each round not the handicap of each round. Not sure how to get it to sort out and get the average

I tried to do this in one query, but my mind cant come up with one to do it all at once.

``````
\$res = mysql_query(\$q1);
list(\$num) = mysql_fetch_row(\$res);
if(\$num < 6) {
echo "Not enough rounds.";
die();
} elseif (\$num < 17) {
\$limit = ceil(\$num / 2) - 2;
} elseif (\$num < 20) {
\$limit = \$num - 10;
} else {
\$num = 20;
\$limit = 10;
}
\$q2 = "SELECT diff FROM rounds WHERE username = '".\$someusername."' ORDER BY datePlayed DESC LIMIT".\$num;
\$res = mysql_query(\$q2);
\$diffs = array();
while(list(\$diff) = mysql_fetch_row(\$res)) {
\$diffs[] = \$diff;
}
rsort(\$diffs);
\$handicap = array_sum(array_slice(0,\$limit)) / \$limit;

``````

Thanks starLion… Do you mind explaining the code a little so I can under stand why Im and getting this error

Current handicap:
Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in C:\wamp\www\condition\members.php on line 96

Warning: array_slice() expects parameter 1 to be array, integer given in C:\wamp\www\condition\members.php on line 100

Warning: array_sum() expects parameter 1 to be array, null given in C:\wamp\www\condition\members.php on line 100
0

``````\$user = \$_SESSION['username'];
\$result = mysql_query("SELECT * FROM members WHERE username = '\$user'");
while (\$row=mysql_fetch_array(\$result)) {
echo "<h2 class='title'>Welcome back ",\$row['firstname'],"! </h2>
<div class='entry'>
<p>";}}?>
<?php
\$result = mysql_query("SELECT * FROM rounds where username ='\$user'");
\$num_rows = mysql_num_rows(\$result);
if (\$num_rows >= 1)
echo "You have \$num_rows round in the system.";
else
echo "You have \$num_rows rounds in the system.";
echo "<br />";
echo "Current handicap: ";\$q1 = "SELECT COUNT(username) FROM rounds WHERE username = '".\$user."'";
\$res = mysql_query(\$q1);
list(\$num) = mysql_fetch_row(\$res);
if(\$num < 6) {
echo "Not enough rounds.";
die();
} elseif (\$num < 17) {
\$limit = ceil(\$num / 2) - 2;
} elseif (\$num < 20) {
\$limit = \$num - 10;
} else {
\$num = 20;
\$limit = 10;
}
\$q2 = "SELECT diff FROM rounds WHERE username = '".\$user."' ORDER BY datePlayed DESC LIMIT".\$num;
\$res = mysql_query(\$q2);
\$diffs = array();
while(list(\$diff) = mysql_fetch_row(\$res)) { // LINE 96
\$diffs[] = \$diff;
}
rsort(\$diffs);
\$handicap = array_sum(array_slice(0,\$limit)) / \$limit;
``````

I marked the line 96 with a comment.

i don’t do php but it looks like you forgot the space between LIMIT and 20

Yup, Rudy’s right (as he usually is when it comes to my SQL queries ) There should be a space between the word LIMIT and the double quote.

This error is covered under the “Common PHP Errors” sticky thread.

I tried echoing everything out like the “Common PHP errors” suggested. Everything seems to be fine but I am still getting

Current handicap:
Warning: array_slice() expects parameter 1 to be array, integer given in C:\wamp\www\condition\members.php on line 94

Warning: array_sum() expects parameter 1 to be array, null given in C:\wamp\www\condition\members.php on line 94
0
this is line 94 now

``````\$handicap = array_sum(array_slice(0,\$limit)) / \$limit;
``````

the lone 0 in the above quote is when I echo “\$handicap”. And another thing I trying to understand the code all together this is my full PHP code.

``````<?php
else {
\$result = mysql_query("SELECT * FROM members WHERE username = '\$user'");
while (\$row=mysql_fetch_array(\$result)) {
echo "<h2 class='title'>Welcome back ",\$row['firstname'],"! </h2>
<div class='entry'>
<p>";}}

echo "Current handicap: ";\$q1 = "SELECT COUNT(username) FROM rounds WHERE username = '".\$user."'";
\$res = mysql_query(\$q1);
list(\$num) = mysql_fetch_row(\$res);
if(\$num < 6) {
echo "Not enough rounds.";
die();
} elseif (\$num < 17) {
\$limit = ceil(\$num / 2) - 2;
} elseif (\$num < 20) {
\$limit = \$num - 10;
} else {
\$num = 20;
\$limit = 10;
}
\$q2 = "SELECT differential FROM rounds WHERE username = '".\$user."' ORDER BY datePlayed DESC LIMIT ".\$num;
\$res = mysql_query(\$q2);
echo mysql_error();
\$diffs = array();
while(list(\$diff) = mysql_fetch_row(\$res)) {
\$diffs[] = \$diffs;
}
rsort(\$diffs);
\$handicap = array_sum(array_slice(0,\$limit)) / \$limit;
echo \$handicap;
?>
``````

When is was rewriting the code you StarLion posted I noticed that in this line

``````\$q2 = "SELECT diff FROM rounds WHERE username = '".\$user."' ORDER BY datePlayed DESC LIMIT ".\$num;
``````

That in my table I do not have a column called diff so I changed it to differential I’m not sure that that cased any issue because I tried changing it back to diff and got this error when I did

Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in C:\wamp\www\condition\members.php on line 90
.I hope you dont think I just want someone else to just write the code for me I am only learning PHP for a challenge that keeps me busy.

Another thing I am not sure on is where in the above code is the differential being multiplied by .69?

Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in C:\wamp\www\condition\members.php on line 90

This error means “The previous mysql_query failed. Go back and fix it.” If you dont have a diff field in the table, then trying to query it will cause a failure. Set it to differential if thats what your field is named.

Now, the other error…
Is my stupidity for forgetting to give it WHAT to slice. I must have been tired while writing this.
Array slice takes 2 (or more) parameters.
In this case, it will take the Array to Be Sliced, the Start position, and the Length of the slice.

Change that line to:

``````\$handicap = array_sum(array_slice(\$diffs,0,\$limit)) / \$limit;
``````

AND

``````rsort()
``````

should just be sort(). sort is lowest-to-highest. Man i really must have been tired.
and it should work fine.

If you have specific questions about what the code does, let me know… i’m not going to go line-by-line on a block that size

Thanks StarLion…
Adding \$diffs to the array stopped the errors. However when I call \$handicap I always get 0. I have 7 rounds in the table. Im confused on which part of the code actually deals with the differential from the query. If \$diffs = array() and its written just like that what data in being entered in the array? Is that why I always getting 0 when \$num is greater then or equal to 6?

``````echo "Current handicap: ";\$q1 = "SELECT COUNT(username) FROM rounds WHERE username = '".\$user."'";
\$res = mysql_query(\$q1);
list(\$num) = mysql_fetch_row(\$res);
if(\$num < 6) {
echo "Not enough rounds.";
die();
} elseif (\$num < 17) {
\$limit = ceil(\$num / 2) - 2;
} elseif (\$num < 20) {
\$limit = \$num - 10;
} else {
\$num = 20;
\$limit = 10;
}
\$q2 = "SELECT differential FROM rounds WHERE username = '".\$user."' ORDER BY datePlayed DESC LIMIT ".\$num;
\$res = mysql_query(\$q2);
echo mysql_error();
\$diffs = array();
while(list(\$diff) = mysql_fetch_row(\$res)) {
\$diffs[] = \$diffs;
}
sort(\$diffs);
\$handicap = array_sum(array_slice(\$diffs,0,\$limit)) / \$limit;
echo \$handicap;
?>
``````

\$diffs = \$diffs;

should be
\$diffs = \$diff;