adding points

[Justafriend]

Ok i have 3 columms right now i have  date  player and points and what i want is for this script to add up each players points and send it to page  as totals decending i have got the page to show them individually but when i do group it only gets one record so i tried to add in the  sum points and it gives me a  ) Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in so i am kind of lost as to where adding the sum(points) would cause this below is the code where the problem is any help would be much appreciated

$r = mysql_query("select  * from player_points Sum(points) as total where date_entered > '$d' Group by player order by  total DESC");

[jcbones]

 

$r = mysql_query("select  sum(points) as total from player_points where date_entered > '$d' Group by player order by total DESC") or trigger_error(mysql_error());

 

*Note*, You shouldn't be using mysql, it is ver` old, and a `neer do well.  Stick with PDO, or Mysqli.

[Justafriend]

i am right now in a rush to get this little project done  eventually im gonna start looking at mysqli  but all the code that i have is mysql so i got to tweak it then get it running then switch  and learn about mysqli i just tried the solution you gave me but came across a weird output 

The numbered column for their place is all there but  the names and points arent showing  ill just paste the top 10 and then also paste the source that the page shows up

 

Extracting data since 2014-09-01

Place PlayerName Points

1    

2    

3    

4    

5    

6    

7    

8    

9    

10  

 

and the source ends up

<tr><td>2</td><td></td><td></td></tr>
<tr><td>3</td><td></td><td></td></tr>
<tr><td>4</td><td></td><td></td></tr>
<tr><td>5</td><td></td><td></td></tr>
<tr><td>6</td><td></td><td></td></tr>
<tr><td>7</td><td></td><td></td></tr>
<tr><td>8</td><td></td><td></td></tr>

so its not outputting the names or points to the table i really appreciate yourr help

Edited September 2, 2014 by Justafriend

[QuickOldCar]

paste your code here in the code tags for more assistance

[Justafriend]

here it is i know it isnt the best but  im trying to merge the code someone gave me and work with it as i learn

<table>

			<thead>

			<td>Place</td>

			<td>PlayerName</td>

			<td>Points</td>

			</thead>

			<?php
					mysql_connect("localhost", "root", ""); //Put login and password for mysql here. Replace null with mysql server address if provided by host.
		mysql_select_db("test") or die(mysql_error());//Put databasename here

				$i = 0;

				$d = date("2014-09-01");



				//$d = preg_replace("/-(\d+) /", "-01 ", $d);

				echo("Extracting data since $d<br/>");



$r = mysql_query("select  sum(Points) as total from player_points where date_entered > '$d' Group by player order by total DESC") or trigger_error(mysql_error());
				while ($v = mysql_fetch_assoc($r))

				{

					++$i;

					echo("<tr><td>$i</td><td>${v["player"]}</td><td>${v["points"]}</td></tr>\n");

				}

			

			?>

			</table>

[QuickOldCar]

Not fetching player from the table so it won't show as $v['player'] in the results

 

You are returning the sum only

 

Use a subquery, * is all...you can return only what you need

$r = mysql_query("select *,(select sum(Points) as total ) from player_points where date_entered > '$d' Group by player order by total DESC") or trigger_error(mysql_error());

[QuickOldCar]

This is easier

echo "<tr><td>".$i."</td><td>".$v['player']."</td><td>".$v['points']."</td></tr><br />";

[Justafriend]

after i substituted that  for what i had i got 2 errors one looks almost french which is weird and that is

Notice: Champ 'total' inconnu dans order clause in C:\wamp\www\points.php on line 29

and the second is

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\points.php on line 30

here is the revised code

<table>

			<thead>

			<td>Place</td>

			<td>PlayerName</td>

			<td>Points</td>

			</thead>

			<?php
					mysql_connect("localhost", "root", ""); //Put login and password for mysql here. Replace null with mysql server address if provided by host.
		mysql_select_db("test") or die(mysql_error());//Put databasename here

				$i = 0;

				$d = date("2014-09-01");



				//$d = preg_replace("/-(\d+) /", "-01 ", $d);

				echo("Extracting data since $d<br/>");



$r = mysql_query("select *,(select sum(Points) as total ) from player_points where date_entered > '$d' Group by player order by total DESC") or trigger_error(mysql_error());
				while ($v = mysql_fetch_assoc($r))

				{

					++$i;

					echo("<tr><td>$i</td><td>${v["player"]}</td><td>${v["points"]}</td></tr>\n");

				}

			

			?>

			</table>

[jcbones]

You should be able to pull the name without a sub-query.

 

"select player, sum(Points) as total  from player_points where date_entered > '$d' Group by player order by total DESC"

Edited September 2, 2014 by jcbones

[jcbones]

You should also be looking for:

 

$v['player'];
$v['total'];

according to your query.

[Justafriend]

Thank you very much both of you now one more question and you can tell me forget it  i really do appreciate the help you have given me  is there anyway of telling the numbering that if  using an example to show

1 sajoma 58

2 Atlfan 55

3 BikerChick 47

4 PRO_milagros 45

5 Rustedfish 41

6 TM7_LOVIN_LIFE_ 40

7 A_Baddboy 35

8 I_DontPlay 34

9 EU_IT_mirejo 32

10 J1moyer 32

11 mokan40 30

12 BigCube 30

that instead of a result like this it would be like this cause 9 and 10 are tied as well as 11 and 12 so the resulting would look like

1 sajoma 58

2 Atlfan 55

3 BikerChick 47

4 PRO_milagros 45

5 Rustedfish 41

6 TM7_LOVIN_LIFE_ 40

7 A_Baddboy 35

8 I_DontPlay 34

9 EU_IT_mirejo 32

9 J1moyer 32

11 mokan40 30

11 BigCube30

 

if it cant be done its not a big deal

Edited September 2, 2014 by Justafriend

[Barand]

It can be done, but things get a little more complicated and require user variables and sub-queries

SELECT 
     @rank := IF(tot=@prevtot, @rank, @rank+1) as rank
    , player
    , @prevtot := tot as total
    FROM 
        (SELECT player
        , SUM(points) as tot
        FROM player_points
            JOIN (SELECT @rank:=0, @prevtot:=0) as init
        GROUP BY player
        ORDER BY tot DESC
        ) as tots
ORDER BY rank;