[SOLVED] Little help with IF

[l3asturd]

ok, Now that I know how to write and read using PHP/SQL I'm trying to move on to the next step. Manipulating results.

 

Here's an example of raw table data displayed in an html <table> result:

 

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

- Name   -Score    -Purse   -Date -

___________________________

- Mike        10          $5      7/24

- Larry        8           $3      7/24

- Rob          7           $2      7/24

- Mike         9           $4      7/31

- Larry        8           $3      7/31

- Rob          4           $1      7/31

___________________________

 

 

Now I now how to sort ASC and DESC to get the scores arranged using ORDER BY, here's my challenge. I'd like to take the contents of the table, or *, look for duplicates, such as Mike's record on 7/24 and Mike's record on 7/31, and add the fields together, to display a total score and total purse. I don't want to delete or update the table, just manipulate the results so I can display totals in a <table>.

 

So...here's my code, which is a joke, to attempt to do something like this:

 

<?php
//assume db connected
$result = mysql_query("SELECT * FROM table1 ORDER BY name ASC", $conn);
while ($row = mysql_fetch_array($result))
{
//This is where I am totally lost. How can you add row scores depending on if the name field is a duplicate?
}
endwhile; 

?>

ok so that's as far as I've gotten

[trq]

You need to look into mysql's functionality. Its much more efficient to do these things within your queries.

 

As an example, given your table this query would return 19.

 

SELECT SUM(Score) FROM tbl WHERE Name  = 'Mike';

[l3asturd]

Is it really that easy? How can I change the query so that it adds duplicate names?

Like, instead of SUM(score) for 'Mike'

 

SUM(score) for duplicate names

 

Thanks for your help!

[trq]

SELECT Name, SUM(Score) AS TotalScore FROM tbl GROUP BY Name;

 

Will get you all users, and there total score.

[l3asturd]

SELECT Name, SUM(Score) AS TotalScore FROM tbl GROUP BY Name;

 

Will get you all users, and there total score.

 

Now I know why you're called a genius. Thanks.

 

Oooh, in the SQL query, is TotalScore a table field I need to add to the table? Or is it a PHP variable in the script? I'm guessing I need to add a field named TotalScore as you didn't put a $ in front of it.

 

If anyone else know's the answer please let me know.

[teng84]

actually no. that is an alias you its not reqiured it will run even without that

but when the query runs the say table to be display will use that as header

or when you do it in php an do some fetching it will be use as index

 

ok

do you remember when you display a table in the db it has sample you have fields

name and address and score

so whe you query it gives this

name address score

teng  philippines 5

 

but having the query thorpe gave you you will have

 

name address TotalScore

teng  philippines 5

 

it is rename but only for output not in your real db

[l3asturd]

thanks teng. I'll write up the new code. Thanks to every1.