[SOLVED] ranking!

[onthespot]

Really just looking for a push in the right direction here.

 

So i have a table with stats in, they have home goals for and home goals against, and away goals for and away goals against.

 

I want to add these up which is obviously simple to make a goals for and goals against number.

 

I will then create the goal difference using both.

 

How would i then rank these using PHP? could someone help me here.

thankyou

[gevans]

How would i then rank these using PHP? could someone help me here.

 

In what sense? Do you want some code, and equation?

 

Is the ranking for the most away goals? I'm not really following. You should be able to do the whole process in a query. (apart from displaying the results)

[onthespot]

I shal reexplain.

 

the table has

home goals for

home goals against

away goals for

away goals against

 

i then do some php to make them

 

goals for (home and away)

goals against (home and away)

 

then i shal do

 

goals for - goals against = goal difference

 

then this goal difference i want to display in order of largest 1st down to smallest last, there are many rows in the original table i am taking this from.

[onthespot]

$fifa09="SELECT * FROM fifa09_stats";
$fifa09stats=mysql_query($fifa09);
while($row=mysql_fetch_assoc($fifa09stats)){
    $fifa09user=$row['user'];
	$fifa09homefor=$row['home_goals_for'];
	$fifa09homeagainst=$row['home_goals_against'];
	$fifa09awayfor=$row['away_goals_for'];
	$fifa09awayagainst=$row['away_goals_against'];
}

$fifa09for = ($fifa09homefor + $fifa09awayfor);
$fifa09against = ($fifa09homeagainst + $fifa09awayagainst);

$fifa09gd = ($fifa09for - $fifa09against);

echo $fifa09user;
echo $fifa09gd;

 

I have used this code, but it only displays the user from the bottom of the table, so the last row.

 

Any ideas?

[onthespot]

Why doesnt this echo all the rows out? and only the last one?

[gevans]

give this a try;

 

$fifa09="SELECT `user`, 
((`home_goals_for` + `away_goals_for`)-(`home_goals_against` + `away_goals_against`)) AS `goal_difference` 
FROM `fifa09_stats`
ORDER BY `goal_difference` DESC";

$fifa09stats = mysql_query($fifa09);

while($row=mysql_fetch_assoc($fifa09stats))
{
   echo $row['user'];
   echo $row['goal_difference'];
}

[onthespot]

That worked a treat mate.

 

How could i then modify this to have

home_games_played

away_games_played

added together and divide the goal difference by these to get an average goal difference.

 

Also need to have 1,2,3,4,5 etc as the ranking next to each name.

THankyou

[gevans]

Why don't you have a look at what I just did and have a think. Try it yourself before asking for help

[onthespot]

Well i added the / and then games played into the query, but it returned some silly figures that were just wrong.

So i asked you for help!

[onthespot]

Ok so i got the average working.

How do i put the 1,2,3,4,5 in?

Have no idea how to do this at all.

[gevans]

give this a go;

 

$fifa09="SELECT `user`, (`home_games_played` + `away_games_played`) AS `games_played`,
((`home_goals_for` + `away_goals_for`)-(`home_goals_against` + `away_goals_against`)) AS `goal_difference`
FROM `fifa09_stats`
ORDER BY `goal_difference` DESC";

$fifa09stats = mysql_query($fifa09);

$i = 0;
while($row=mysql_fetch_assoc($fifa09stats))
{
echo ++$i;
echo "User: " . $row['user'];
echo " Goal Difference: " . $row['goal_difference'];
echo " Average Goal Difference: " . ($row['games_played']\$row['goal_difference']);
}

[onthespot]

Unexpected character in input: '\' (ASCII=92) state=1 in

 

Thats the error I get with the line you have echoed the average difference on.

[onthespot]

Just realised the slash was th wrong way round

[onthespot]

<table cellspacing="10">
<tr>
<td>#</td>
<td>User</td>
<td>Avg GD</td>
</tr>
<?
$fifa09="SELECT `user`, (`home_games_played` + `away_games_played`) AS `games_played`,
(((`home_goals_for` + `away_goals_for`)-(`home_goals_against` + `away_goals_against`))/(`home_games_played` + `away_games_played`)) AS `avg_goal_difference`
FROM `fifa09_stats`
ORDER BY `avg_goal_difference` DESC";

$fifa09stats = mysql_query($fifa09);

$i = 0;
while($row=mysql_fetch_assoc($fifa09stats))
{
?>


<tr>
<td><? echo ++$i;?></td>
<td><? echo $row['user'];?></td>
<td><? echo $row['avg_goal_difference'];?></td>
</tr>

   
<?
}
?>
</table>

 

How could i alter this code to only display users and their goal difference if they have played at least 5 games? So when adding home and away games played, they must have at least 5 games played?

 

Thankyou

[gevans]

You really hve to put a little effort in, that is a very very easy thing to do with what you have so far....

 

 

if($row['games_played'] >= 5){
//then show it
}

 

just have to put it in the correct place.

[onthespot]

Sorry i just struggle with things that seem hard to me but are obviously really simple to you.

I have tried placing that code everywhere and yet it just wont work.

[onthespot]

Done this, had a error of my own sorry

[onthespot]

So my final question and have tried several ways of doing this to no avail.

 

I define a variable at the very beginning.

 

$game12=$_GET['game'];

 

I then input this variable into the SQL for the rankings.

 

FROM `$game12_stats`

 

However this doesnt show anything.

The variable pulls the correct game from the URL.

So when its game=fifa09, it should make the sql , fifa09_stats

It doesn't

 

Am i getting something wrong with the syntax for the sql?

[Zane]

This is a help forum...not a personal blog/journal/repository of failed attempts.  Construct something someone can actually get some use out of to help you with.  You're already up to two pages and 12 of the posts are yours.  That's like 66% of the thread.  including my post

 

[Zane]

$game12_stats = $_GET['game']

FROM `$game12_stats`

[onthespot]

Sorry mate, I understand. I will keep the posts relevant.

That reply you just gave me.

 

The table is called fifa09_stats, then fifa10_stats.

I have the url of

ranking.php?game=fifa09

or

ranking.php?game=fifa10

 

So when i put $game12 as the variable of $_GET['game'];

 

I need the sql to do the $game12 and then add _stats on the end of it.

 

I thought i had this correct but it won't work.

 

Must be the way im doing the sql.

Do i need fullstops or extra quotations?

 

[Zane]

ok..i believe we're gettin somewhere

 

you want to take this

ranking.php?game=fifa09  The bold area...the $_GET variable...known as game

 

and turn it into this

SELECT * FROM `fifa09_stats`

and if not exactly that statement..then something along the lines of that statement?...am I right? please say yes

 

If I'm right then this is the code for it

------------------------------------

$tableName = $_GET['game'] . "_stats";
$sql = "SELECT * FROM `$tableName`";

 

.. and you lost me at the fullstops and extra quotes parts...you must know something I don't ...who knows

[onthespot]

Thankyou ever so much. That has done it.

The fullstops i meant by the way in which you joined that together for the variable $tableName.

But it works and thankyou