Jump to content


Photo

help with table


  • Please log in to reply
13 replies to this topic

#1 csabi_fl

csabi_fl
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 13 October 2006 - 03:04 PM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 October 2006 - 03:37 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 csabi_fl

csabi_fl
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 13 October 2006 - 04:11 PM

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.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 October 2006 - 07:26 PM

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

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 csabi_fl

csabi_fl
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 October 2006 - 06:15 PM

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

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 October 2006 - 09:37 PM

How do I link this summary table from my webpage?

What do you mean?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 csabi_fl

csabi_fl
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 October 2006 - 09:46 PM

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?

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 October 2006 - 03:50 PM

Not really... just issue that query, and output the results.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 csabi_fl

csabi_fl
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 16 October 2006 - 06:46 PM

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.

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 October 2006 - 03:41 PM

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

and then:

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 csabi_fl

csabi_fl
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 17 October 2006 - 06:23 PM

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?

#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 October 2006 - 10:11 PM

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).
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#13 csabi_fl

csabi_fl
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 18 October 2006 - 03:43 AM

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.

#14 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 October 2006 - 04:10 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users