help with table

[csabi_fl]

Hi guys.
I need some help with my table.It looks something like this:
loginid|week1|total:
John|5|0
John|7|0
John|3|0
Now in the 'total' column (where all the 0's are)I want to have the sum of 'week1'(5+7+3).I think I need the UPDATE command but I don't know how to phrase it.
Something like:
UPDATE * SET total=???? WHERE loginid='John'.
Is that possible to have the total in only 1 row not in all 3 of them?
Thank you in advance.

[fenway]

Why would you do such a thing? This can and should be calculated on the fly... and if for some reason you need it instantly, you should make a summary table instead.

[csabi_fl]

I am not sure I understand.How can I do that on the fly and how do I make that summary table?I am very new to this so please explain.
Thank you again.

[fenway]

It's trivial to get the sum for each login:
[code]SELECT SUM(total) FROM yourTable GROUP BY loginid[/code]

A summary table would simply be a place where you store the result of the above query; but in general, it's not necessary, at least for MyISAM tables.

[csabi_fl]

It works great,thank you.
One more thing.How do I link this summary table from my webpage?

[fenway]

[quote author=csabi_fl link=topic=111401.msg451909#msg451909 date=1160849759]
How do I link this summary table from my webpage?
[/quote]
What do you mean?

[csabi_fl]

I want my members to be able to click on a link that takes them to this summary table so they can check their standings.A link between my webpage and mysql table is what I am looking for.Does that make sense?

[fenway]

Not really... just issue that query, and output the results.

[csabi_fl]

Hi again.
I am trying to output the results just like you said.
I use the following code:

<?php
mysql_pconnect("localhost","","");
mysql_select_db("");
$query  = "SELECT loginid,SUM(week1+week2) FROM submit1 GROUP BY loginid ORDER BY 'SUM(week1)+SUM(week2)' DESC";
$result = mysql_query($query);
while ($list = mysql_fetch_array($result)) {
      echo "{$list['loginid,SUM(week1)+SUM(week2) ']}<br>";
}
?>
I am struggling with 2 things.
1)The command DESC isn't working,numbers aren't showing up in descending order.
2)Inside the 'echo' statement something isn't right, I guess it is a syntax error,I want the output to be in a chart ,for example:
John|75
Mary|70
Mike|60 and so on.
Thank you very much for your help.

[fenway]

$query  = "SELECT loginid, SUM(week1+week2) AS weekSum FROM submit1 GROUP BY loginid ORDER BY weekSum DESC";

and then:

echo "{$list['loginid']}|{$list['weekSum']}}

[csabi_fl]

It works great.Thank you.
I want to adjust the 'echo' statement so that it will show something like this:
1) John|75
2) Marienne|70
3) Michael|60
and so on.
How do I get the order nr 1,2,3 and how do I line up the points 75,70 and 60 to show up in a column nice and neat under one another?

[fenway]

You can easily add a PHP counter, and output that as well; as for "nice" columns, you should really output a TABLE, if it's HTML, or padding the output for text (not as simple).

[csabi_fl]

I am sorry I don't really understand.I am a rookie,remember?
How do I output a php counter?I am not familiar with that.Also if you look at one of the previous posts the table is not HTML ,so if you could explain a little bit in details about padding the output for text.

[fenway]

Just initialize a PHP variable to zero outside the loop, and increment it for each iteration of the loop; then you can echo this as well, with whatever formatting you desired.  As for padding, it's going to be easiest if you "cheat" and assume that you won't exceed a given length, and simply prepend/append with spaces the difference between this length N and the length your name field.  If you want details about PHP implementation, post another thread in the PHP help forum.  We've drift far away from MySQL, so I can't really be of any more help.