totaling numbers from a dtabase matching a personns name

lppa2004 · #1 (**permalink**) 07-18-05, 06:44 AM

Okay i have a database that will hold names and numbers, i need a function that will search for the name then extract the numbers total them and relist them in ranking order..

for example...

database holds
======================
ID | NAME | POINTS
--------------------
1 | John | 100
2 | John | 200
3 | Beth | 50
4 | Beth | 100
=======================

Output would be:
========================

1st place is John with 300 points
2nd place is Beth with 150 points

========================

FiRe · #2 (**permalink**) 07-18-05, 07:06 AM

PHP Code:


			
$i = 1;

$query = mysql_query("SELECT * FROM table ORDER BY points DESC");

while($r = mysql_fetch_array($query)) {

$name = $r['name'];

$points = $r['points'];

echo "$i place is $name with $points points";

$i++;

}

xtremenw · #3 (**permalink**) 07-18-05, 02:26 PM

Quote:

Originally Posted by FiRe

PHP Code:


			
$i = 1;

$query = mysql_query("SELECT * FROM table ORDER BY points DESC");

while($r = mysql_fetch_array($query)) {

$name = $r['name'];

$points = $r['points'];

echo "$i place is $name with $points points";

$i++;

}

Change the query a little bit to show 'SELECT name, SUM(points) FROM table ORDER BY SUM(Points) DESC'

His example has two john's and two beths. So output could then show:

First place is John with 200 points.
Second place is John with 100 points.

Adding their points first would resolve this.

If you wanted to get the output closer to what you asked, create a two dim arary ('0'->'1st', '0'->'2nd','n'->'Nth') and then reference the array using the couter when you print it to the screen.

netbakers · #4 (**permalink**) 07-18-05, 11:37 PM

Simply use this query

PHP Code:


			
SELECT name, sum(points)

FROM  points 

GROUP  BY name

ORDER  BY 2  DESC

lppa2004 · #5 (**permalink**) 07-20-05, 04:02 AM

okay xtremenw and netbakers im still quite stumped with both of your ways of doing this (im an idiot what can i say, actually just learning all this still).. Okay i am still quite not sure how to do arrays, if you can give me a code example that would be great... and netbakers, same thing.. (ill post the code i have so far and the output it generates, and then ill show what i want it to look like)... Needless to say this is driving me insane, i know there has to be an easy way to do this... Wish i had the money to just pay someone to code this for me.. lol

Okay here it goes....
=============================

PHP Code:


			
<?

require ('functions.php');





connect_to_mysql();



$i = 1;

$query = mysql_query("SELECT * FROM rank_point ORDER BY TOTAL_POINTS DESC");

while($r = mysql_fetch_array($query)) {

$name = $r['PID'];

$points = $r['TOTAL_POINTS'];

echo "$i place is $name with $points points<BR>";

$i++;

}



?>

The above code outputs this
-------------------------------------------------

Code:

1 place is Jonathan Selinsky with 690 points
2 place is Jonathan Selinsky with 680 points
3 place is Jonathan Selinsky with 650 points
4 place is Steve Caldwell with 640 points
5 place is Christoph Wagner with 481 points
6 place is Charles Gary with 472 points
7 place is Jon Shirley with 445 points
8 place is Hal Kirkwood with 436 points
9 place is Chris Patterson with 312 points
10 place is Chris Hundley with 304 points
11 place is Hal Kirkwood with 280 points
12 place is Jon Shirley with 272 points
13 place is Mike Shelton with 223 points
14 place is Art Schai with 216 points
15 place is Steve Caldwell with 195 points
16 place is Chris Hundley with 188 points
17 place is Steve Caldwell with 174 points
18 place is Chris Patterson with 168 points
19 place is Chris Hundley with 150 points
20 place is Greg Kammerer with 135 points
21 place is Jon Shirley with 130 points
22 place is Tod Presutti with 106 points
23 place is Christoph Wagner with 102 points
24 place is Chris Hundley with 77 points
25 place is Steve Caldwell with 74 points
26 place is Jon Shirley with 58 points

I need this to be what it looks like instead (the actual sums will not be exact in this example since i dont wanna do all the math right now)

Code:

1 place is Jonathan Selinsky with 2690 points
2 place is Steve Caldwell with 1640 points
3 place is Christoph Wagner with 1481 points
4 place is Charles Gary with 1472 points
5 place is Jon Shirley with 445 points
6 place is Hal Kirkwood with 436 points
7 place is Mike Shelton with 223 points
8 place is Art Schai with 216 points
9 place is Chris Patterson with 168 points
10 place is Greg Kammerer with 135 points
11 place is Tod Presutti with 106 points

Basically i am working on a script/program that will act pretty much the same as the one on the following site (i am doing the same kind of poker tournament leauge deal)

http://pokerplayersinc.com/Standings...ngs.aspx?ID=21
(you will need tio choose a month and a location to see the results)

If someone can help me get this same output id greatly appreciate it, and if possible maybe just contact me through IM and help walk me through this... I really need to get this program working so i dont have to manually handle my players points every week... MY AIM SN is - djhunted, i can also be contacted via email at chris@lafayettepoker.com

lppa2004 · #6 (**permalink**) 07-20-05, 06:20 AM

o yeah i should also include how my db table is layed out
================================================== =

----------------------------------------------------------------------------------------------------------------------------------
ID | PID | TDATE | TTIME | VENUE | TPLAYERS | PLACE | RANK_POINTS | BONUS_POINTS | TOTAL_POINTS
----------------------------------------------------------------------------------------------------------------------------------

The above basically equates to:
TABLE ROW ID, PLAYER NAME, TOURNAMENT DATE, TOURNAMENT TIME, TOURNAMENT VENUE, TOTAL PLAYERS IN TOURNAMENT, WHAT PLACE THEY WENT OUT, THE POINTS THEY GET FOR THAT PLACE, THE BONUS POINTS CALCULATED FROM TOTAL PLAYERS AND THE PLACE THEY WENT OUT, AND THEN THE SUM OF RANK POINTS AND BONUS POINTS

netbakers · #7 (**permalink**) 07-20-05, 06:43 AM

I think it is working...

PHP Code:


			
<?php



$sql = mysql_query("SELECT name, sum(points) FROM  points GROUP  BY name ORDER  BY 2 DESC");



$i = 1;



while($rs = mysql_fetch_array($sql)) {



$na = $rs['name'];

$po = $rs['points'];



echo "$i place is $na with $po  points";



$i++;

}



?>

lppa2004 · #8 (**permalink**) 07-20-05, 07:24 AM

Okay I tried your example and this is the code I used
===============================================

PHP Code:


			


<?php

require ('functions.php');



connect_to_mysql();

$sql = mysql_query("SELECT PID, sum(TOTAL_POINTS) FROM  rank_point GROUP  BY PID ORDER  BY 2 DESC");



$i = 1;



while($rs = mysql_fetch_array($sql)) {



$na = $rs['name'];

$po = $rs['points'];



echo "$i place is $na with $po  points<BR>";



$i++;

}





?>

This is the output i got from it
=========================================

Code:

1 place is with points
2 place is with points
3 place is with points
4 place is with points
5 place is with points
6 place is with points
7 place is with points
8 place is with points
9 place is with points
10 place is with points
11 place is with points
12 place is with points

===============================================
Something isnt working, if you could maybe give an exampke using the exact Table layout and the names associated in it in the sql statement maybe that would help
===============================================

Also here is the actual code that im using so it will output the results in a HTML table, so maybe you could also help me adapt your example to it
================================================

PHP Code:


			
<table border="0" cellspacing="2" cellpadding="2">

<tr>

<th width="50" align="left"><font face="Arial, Helvetica, sans-serif" size="2">

Rank</font></th>

<th width="300" align="left"><font face="Arial, Helvetica, sans-serif" size="2">

Player Name</font></th>

<th width="75" align="left"><font face="Arial, Helvetica, sans-serif" size="2">

Total Points</font></th>

</tr>

<?

require ('functions.php');

connect_to_mysql();

$query="SELECT * FROM rank_point ORDER BY TOTAL_POINTS DESC";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;

$z=1;



while ($i < $num) {

$pid=mysql_result($result,$i,"pid");

$tpoints=mysql_result($result,$i,"total_points");



echo "<tr>";

echo "<td width=\"50\"><font face=\"Arial, Helvetica, sans-serif\" size=\"2\">$z</font></td>";

echo "<td width=\"300\"><font face=\"Arial, Helvetica, sans-serif\" size=\"2\">$pid</font></td>";

echo "<td width=\"75\"><font face=\"Arial, Helvetica, sans-serif\" size=\"2\">$tpoints</font></td>";

echo "</tr>";





$i++;

$z++;

}

echo "</table>";

?>

================================================== ==
You can see the rsulting output of my code here
http://lafayettepoker.com/tournament.../standings.php
================================================== ==

I really appreciate you all helping me with this, and im sorry if i seem like a complet idiot cause i cant figure this out on my own.. belive me i wish I actually had some formal PHP and MySQL training so i wouldnt have to be asking for this kind of desperate help...

QUICK SIDE NOTE: The table name is rank_point

lppa2004 · #9 (**permalink**) 07-20-05, 07:38 AM

Of course once i get this part figured out the next step will be having it sort out by tournament date so i can break each month down and ouput the players monthly points.. after that i can pretty much do the rest of this script on my own since these are the two parts i forsee having the most problems with

netbakers · #10 (**permalink**) 07-21-05, 12:03 AM

you hav mistake in

$na = $rs['name'];
$po = $rs['points'];

replace this with

$na = $rs['PID'];
$po = $rs['TOTAL_POINTS'];

Now try this.......

PHP Code:


			
<?php



$sql = mysql_query("SELECT PID, sum(TOTAL_POINTS) FROM  rank_point GROUP  BY PID ORDER  BY 2 DESC");



$i = 1;



while($rs = mysql_fetch_array($sql)) {



$na = $rs['PID'];

$po = $rs['TOTAL_POINTS'];



echo "$i place is $na with $po  points";



$i++;

}



?>