PHP Scripts and MYSQL Tables

[Rhyan]

I desperately need to know how to do the following:<br>I have a mysql table with a field entitled Wins. There will be new entries for certain people with a value in the Wins column. Example, each week someone submits a Win value and I have it stored in the database. I need to know how to write a php script that would ADD up the values stored in the Wins Column for each different person:<br><br>Name Wins<br>Tom&nbsp;&nbsp;&nbsp;3<br>Tom&nbsp;&nbsp;&nbsp;2<br>Ed&nbsp;&nbsp;&nbsp;&nbsp;6<br>Bob&nbsp;&nbsp;&nbsp;5<br>Bob&nbsp;&nbsp;&nbsp;2<br>Bob&nbsp;&nbsp;&nbsp;1<br><br>For example, I'd like the script that would display calculate the total of the wins column for each person and display it in html<br><br>Bob 8<br>Ed&nbsp;&nbsp;6<br>Tom 5<br><br>Any help would be appreciated. I have only been learning php for a month or so now and have advanced pretty well until this problem<br><br>Rhyan

 

[ati]

Rhyan,<br><br>don't worry, everyone is sometime a beginner. Your Problem can be solved in on SQL line:<br><br>I suppose that your table name is MyTable!<br><br><br>SELECT Name,SUM(Wins) FROM MyTable GROUP BY Name;<br><br><br>This will deliver the result you're looking for. I suggest that you start reading the MySQL Manual, which is very good, or buy a book.<br><br><br>Regards<br><br>Andreas

 

[Rhyan]

Hi, thanks for your reply, I tried what you said and tryed variations with no luck, maybe you could assist me easier if I show you exactly what I have, Thanks a lot<br><br>Table Schema<br><br>Field&nbsp;&nbsp;&nbsp;&nbsp;Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Null Key Default Extra Action <br>Winner varchar(25)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>Loser&nbsp;&nbsp;varchar(25) YES&nbsp;&nbsp;&nbsp;&nbsp;<br>Wins&nbsp;&nbsp;&nbsp;varchar(4)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>Losses tinyint(4)&nbsp;&nbsp;YES&nbsp;&nbsp;&nbsp;&nbsp;<br>Week&nbsp;&nbsp;&nbsp;varchar(10) YES&nbsp;&nbsp;<br><br>Table Data<br><br>Winner&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loser&nbsp;&nbsp;&nbsp;&nbsp;Wins&nbsp;&nbsp;Losses&nbsp;&nbsp;Week&nbsp;&nbsp;Action <br>BAA_Zed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BAA_Pip&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;no Key <br>BAA_Adam&nbsp;&nbsp;&nbsp;&nbsp;BAA_Kitkat 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;no Key <br>BAA_Kitkat&nbsp;&nbsp;BAA_Pip&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;no Key <br>BAA_Pamjimo BAA_Kitkat 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;no Key <br>BAA_Zed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BAA_Pam&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;no Key <br>BAA_Pip&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BAA_Zed&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;Week 1&nbsp;&nbsp;&nbsp;no Key <br>BAA_Pip&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BAA_Kitkat 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;Week 2&nbsp;&nbsp;&nbsp;no Key <br><br>MY Script to Display Standings, or Total of Wins for Each Winner<br><br>////<br><br>$query = &quot;SELECT Winner,SUM(Wins)FROM submitwk1 GROUP BY Winner Order BY Wins DESC&quot;; <br><br>$mysql_result = mysql_query($query,$connection); <br><br>print (&quot;&lt;B&gt;&quot ;<br>print (&quot;&lt;Center&gt;&quot;<br>print (&quot;&lt;TABLE BORDER=\&quot;4\&quot;&gt;\n&quot; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Member Name&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Points (Wins)&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br><br>print (&quot;&lt;TR&gt;\n&quot;<br><br><br>while ($row = mysql_fetch_array($mysql_result)) { <br>$Winner = $row[&quot;Winner&quot;]; <br>$Wins = $row[&quot;SUM(Wins)&quot;]; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$Winner&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$Wins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br>} <br><br>mysql_free_result($sql_result); <br>mysql_close($connection); <br>?&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;/tr&gt;<br>&nbsp;&nbsp;&lt;/table&gt;<br>&nbsp;<br>And Heres the Output<br><br>Member Name Points(Wins) <br><br>BAA_Adam&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 <br><br>BAA_Zed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6 <br><br>BAA_Pip&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 <br><br>BAA_Kitkat&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 <br><br>BAA_Pamjimo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 <br><br>As you can see, it did not sort in DESC order or else Zed would be listed first.<br><br>Bear in mind that I will have between 1-4 recurring posts by the same Person (Winner)<br><br>Id like to know how to get totals from a post like <br>Week&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Winner&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loser&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Wins&nbsp;&nbsp;&nbsp;&nbsp;Losses<br>Week 1&nbsp;&nbsp;BAA_Kitkat&nbsp;&nbsp;BAA_Pip&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>Week 2&nbsp;&nbsp;BAA_Kitkat&nbsp;&nbsp;BAA_Pamjimo&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<br>Week 2&nbsp;&nbsp;BAA_Pamjimo&nbsp;&nbsp;BAA_Kitkat&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br><br>In theory, the displayed results would be<br><br>BAA_Kitkat 6<br>BAA_Pamjimo 2<br>BAA_Pip&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br><br>Thereby grabbing the positive values from the Wins column for the Winner person and adding them together to display Total Wins and adding the positive values of the Losses column together to get the Total Wins for the Loser<br><br>I am a founder of a club for people who play backgammon on the MSN Gaming Zone and I started learning PHP and MYSQL in order to automate things for the league, its getting so big it takes forever to do things manually. This particular table is the Posted Match Table from which I'm trying to create a Standings table, by grabbing the values of Results and adding them up and displaying in html for the Standings so people can see their total wins.<br><br>I have already gotten CORE PHP book which has helped me a lot to get to where I am now, but I am stuck now with league start approaching monday. :-(<br><br>I'd appreciate it dearly, any help I can get with this.<br><br>Sincerely,<br>Rhyan<br>

 

[ati]

Rhyan,<br><br>you're really close! The problem you have is the database design. But don't worry, I have a solution.<br><br>It's surprising, that BBA_ZED is not on the first row, even if he has 6 Wins. But the reason is that you sorting by Wins. Have look at the table:<br><br>On every single entry in Wins, BAA_Adam has 5, so he comes first. Then BAA_Zed, he has 3 as his maximum and all others have less than 3. <br><br>All you need is to sort for SUM(Wins). You can do this by using the following SQL query:<br><br><font color=blue><b>SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY Winner ORDER BY sWins DESC;</b></font><br><br>Do you see the difference? All I do is to create an alias for the result SUM(Wins) and use this to sort the result.<br><br>I hope this will help you, but as I said before: Go and read a book about MySQL. I know the book CORE PHP. It's not worth the money, because everything what's in there is coverd in the PHP Manual shipped with the packages. This book is talking about PHP and not SQL at all.<br><br>Regards<br><br>Andreas<br><br>

 

[Rhyan]

Thank you, that did the trick as far as sorting in order goes!!!! <br><br>I have one more thing and I believe my automation of the league site will be complete. <br><br>As it is now, it gets the Winners Name and Wins Value and displays correctly, summed and all.<br><br>The only problem is, when the winner submits the match results the Losers Name and their Wins, aka the Winners Losses is not computed in the Sum function. <br><br>What I need to do is somehow send the Losers Name and Losses Value, which is the Losers Wins, into the submitwk1 table so that the losers points appears as well.<br><br>I thought of creating another table just for standings separate from the submitwk1, which is the results table.<br>I figured I could have the info from the form send the Winners name and Wins, and the Losers Name and Wins)Winner's Losses, to the table and thereby have the Standings script display all the winners and losers and their Wins, but I cannot figure out how to write a query which sends info from one form, into 2 different processing pages before being displayed. Heres the form I'm referring to:<br><br>//&lt;form method=&quot;post&quot; action=&quot;inputwin.php3&quot;&gt;<br>&nbsp;&nbsp;&lt;table border=&quot;0&quot; width=&quot;100%&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;tr&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;b&gt;Week&lt;/b&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;!--webbot bot=&quot;Validation&quot; S-Display-Name=&quot;Week&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B-Value-Required=&quot;TRUE&quot; --&gt;&lt;select size=&quot;1&quot; name=&quot;Week&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;Week 1&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;Week 2&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;Week 3&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;Week 4&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/select&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;/tr&gt;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;tr&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;font&gt;&lt;b&gt;Winner&lt;/b&gt;&lt;/font&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;input type=&quot;text&quot; name=&quot;Winner&quot; size=&quot;24&quot;&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;/tr&gt;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;tr&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot; valign=&quot;top&quot;&gt;&lt;font&gt;&lt;b&gt;Loser&lt;/b&gt;&lt;/font&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;input type=&quot;text&quot; name=&quot;Loser&quot; size=&quot;24&quot;&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;/tr&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;tr&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;font&gt;&lt;b&gt;Wins&lt;/b&gt;&lt;/font&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;!--webbot bot=&quot;Validation&quot; S-Display-Name=&quot;Wins&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B-Value-Required=&quot;TRUE&quot; --&gt;&lt;select size=&quot;1&quot; name=&quot;Wins&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;3&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;2&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/select&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;/tr&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;tr&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;font&gt;&lt;b&gt;Losses&lt;/b&gt;&lt;/font&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td width=&quot;50%&quot;&gt;&lt;!--webbot bot=&quot;Validation&quot; S-Display-Name=&quot;Losses&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B-Value-Required=&quot;TRUE&quot; --&gt;&lt;select size=&quot;1&quot; name=&quot;Losses&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;0&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;option&gt;1&lt;/option&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/select&gt;&lt;/td&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;/tr&gt;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&lt;/table&gt;<br>&nbsp;&nbsp;&lt;p&gt;&nbsp;&lt;input type=&quot;submit&quot; value=&quot;Submit&quot; name=&quot;submit&quot;&gt;<br>&nbsp;&nbsp;&lt;input type=&quot;reset&quot; value=&quot;Reset&quot; name=&quot;reset&quot;&gt;&lt;/p&gt;<br>&lt;/form&gt;<br>Here is the Processing page<br><br><br><br>Example, right now when this is submitted...<br>Week&nbsp;&nbsp;&nbsp;Winner&nbsp;&nbsp;Loser&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Wins&nbsp;&nbsp;Losses<br>Week 1 BAA_Pip BAA_Kitkat&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br><br>I am getting the Winners Name and Total wins just fine, but how can I send the Loser's Name and Wins to the same table into the Winner Column so that their wins be summed and displayed as well? Thereby have a single list with winners and losers names and their respective Points totals.

 

[ati]

Rhyan,<br><br>now it's becoming tricky for me. I can't really figure out what exactly your Application is supposed to do. Nevertheless, as fas as I can see, you were trying to have a total of wins a losses per Player. All information you need is stored in this table. When you send this query above, it returns the result of wins, but just for won games, right? I guess that you will also add the result of a single win from a lost game. Let's say someone scored 3:1 in two games, so his result with this query will be 6. In an other game he lost with 1:3, but his result will still be 6. If this is your problem, here is the solution:<br>All you need to do is to run this query above. When you fetch the result wiht mysql_fetch_row you send an other query to get the amount of losses from a particular player. E.g. when first row contains BAA_Kitkat and as summary 6 then you form a query like:<br>&nbsp;SELECT SUM(Losses) FROM table WHERE Loser='$LoserName';<br>In $LoserName is stored the name of the last sql result, in&nbsp;&nbsp;this case BAA_Kitkat. The result of the second query will be just one row. Add the result of the first query and this query to create a summary of all wins.<br><br>I hope this was clear enough.<br><br>Andreas

 

[Rhyan]

Hi Andreas, <br><br>First, I want to say that you are very kind for taking the time to assit me. You've given me help that I appreciate dearly. <br><br>You are correct in assuming that I need to add the Positive values that show up in the Losses column to the corresponding Loser as his wins and total those up for each different player.<br><br>I guess I am just clueless here because I cannot figure out how to set up what you suggested to do. <br><br>I'm sorry to bug you again, its just that this league is starting up Monday and I wanted to have this done by then, I didn't think it would be this difficult lol. And you are correct, core php doesnt say a whole lot of mysql. MYSQL must be popular because I cant find any books on it at my local Barnes and Noble, I had to order one and it won't be in for a week or so.<br><br>Getting this Standings script to work is my final blockade to finishing up this league and I appreciate you taking the time to explain a little more specifically how to code your previous solution.<br><br>Heres what I got:<br><br>///<br>// create SQL statement <br><br>$query = &quot;SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY <br>Winner ORDER BY sWins DESC&quot;; <br><br><br><br>// execute SQL query and get result <br>$mysql_result = mysql_query($query,$connection); <br><br><br>// start results formatting <br>print (&quot;&lt;B&gt;&quot ;<br>print (&quot;&lt;Center&gt;&quot;<br>print (&quot;&lt;TABLE BORDER=\&quot;4\&quot;&gt;\n&quot; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Member Name&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Points (Wins)&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br><br>print (&quot;&lt;TR&gt;\n&quot;<br>// format results by row <br><br>$query2 = &quot;SELECT SUM(Losses) FROM submitwk1 WHERE Loser='$LoserName'&quot;;<br><br>$mysql_result2 = mysql_query($query2,$connection) ; <br><br><br>while ($row = mysql_fetch_array($mysql_result2))<br>&nbsp;{ <br>$LoserName = $row[&quot;Losses&quot;];<br>$Winner = $row[&quot;Winner&quot;]; <br>$Wins = $row[&quot;sWins&quot;] + $row[&quot;Loser&quot;]; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$Winner&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$Wins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br>} <br><br><br><br>// free resources and close connection <br>mysql_free_result($sql_result); <br><br><br>

 

[ati]

Try this:<br><br><br><br><br>$query = &quot;SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY Winner ORDER BY sWins DESC&quot;; <br><br>$mysql_result = mysql_query($query,$connection); <br>while ($row = mysql_fetch_array($mysql_result))<br>&nbsp;{ <br>$name=row[0]; # this will return the Winner's name!<br>$wins=row[1]; # this will return the Winner's score!<br><br>$query2 = &quot;SELECT SUM(Losses) FROM submitwk1 WHERE Loser='$name'&quot;; # now create a sql query to get a summary of wins from all lost games<br><br>$mysql_result2 = mysql_query($query2,$connection); <br>$row1=mysql_fetch_row($mysql_result2);<br><br>$SumWins=$wins+$row1[0]; #here we have the summary of all wins per Player!<br><br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$name&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$SumWins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br><br>} <br><br><br><br>// free resources and close connection <br>mysql_free_result($sql_result); <br><br><br>

 

[Rhyan]

Hi Andreas,<br><br>I put it all together and I get an error, sigh :-(<br><br>Heres what the page looks like&lt;html&gt;<br><br>&lt;?php <br><br>// create connection <br>// test connection <br>// select database <br>// test selection <br><br><br>$query = &quot;SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY Winner ORDER BY sWins DESC&quot;; <br><br>$mysql_result = mysql_query($query,$connection); <br>while ($row = mysql_fetch_array($mysql_result))<br>&nbsp;{ <br>$name = row[0]; <br>$Wins = row[1]; <br><br>$query2 = &quot;SELECT SUM(Losses) FROM submitwk1 WHERE Loser='$name'&quot;; # now create a sql query to get a summary of wins from all lost games<br><br>$mysql_result2 = mysql_query($query2,$connection); <br>$row1 = mysql_fetch_row($mysql_result2);<br><br>$SumWins=$Wins+$row1[0]; #here we have the summary of all wins per Player!<br><br>print (&quot;<br>&nbsp;&lt;table&gt;<br>&nbsp;&nbsp;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$name&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$SumWins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br><br>} <br><br><br><br>// free resources and close connection <br>mysql_free_result($sql_result); <br>?&gt;<br>&nbsp;<br>Not sure what the problem is, it looked ok to me, but I get this error when attempting to view the page<br><br>Parse error: parse error in /league/standingstest11.php3 on line 48<br>and 48 is this line&nbsp;&nbsp;$name = row[0]; <br><br>Thank you again<br>Rhyan<br><br>

 

[ati]

change this <br><br>$name = row[0]; <br>$Wins = row[1]; <br><br>to this:<br><br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>Futhermore, check the html tags. I was more looking for the php code and did not focus on the html stuff. You need to send &lt;/table&gt; and probably you want some more nice html created from the script!!!<br><br>

 

[Rhyan]

Well, With your help, I now have an automated standings table, driven off my leauge results table. It works perfectly now and I am so grateful to you I can not even explain. )))<br><br>At the pace I was learning MYSQL and PHP I figured I'd figure out how to do this task within about a month or two of learning. Then as you helped me, I realized that I might not have ever figured that out lol.<br><br>So, I do appreciate your assistance and I am very impressed with my experience here so far. <br><br>Do you get paid to do this, or are you strictly a volunteer, I am curious.<br><br>Thanks Eternally<br>Rhyan ))))))))))

 

[ati]

It's my pleasure.<br><br>No, I don't get paid for helping someone, I even never met. <br>But I am working in the IT business quite long and even I was a beginner. Therefore that you can't study to become a Bachelor or a Master in PHP, I know that someone has to give some advise at some stage. I guess it will happen, that I have to ask for help and I hope that someone will do so.<br><br>So have fun this weekend and impess others with your work!<br><br><br>cheers<br><br>Andreas

 

[Rhyan]

Well, its Great that you do this, I'm sure you make a lot of people happy, which is pretty much why I do what I do, with this club of mine. It sure ain't because of the pay, there isnt any lol<br><br>Guess What? I spoke too soon, it looks like it adds up everything correctly, but the sorting order is not right now lol<br><br>Heres what the results look like<br><br>BAA_Kitkat 13 <br><br>BAA_Adam 11 <br><br>BAA_Pip 12 <br><br>BAA_Zed 7 <br><br>a 4 <br><br>baa 3 <br><br>dfdfdfdf 3 <br><br>testerrrrrrrr 3 <br><br>fp 3 <br><br>BAA_Pamjimo 3 <br><br>test 1 <br><br>Heres the complete script, I did try to Order By Name, Winner, Loser, Losses, Wins etc<br><br>And I even tried the AS&nbsp;&nbsp;and Group BY thing, neither worked.&nbsp;&nbsp;:-(<br>// create connection <br>// test connection <br>// select database <br>// test selection <br><br>$query = &quot;SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY Winner ORDER BY sWins DESC&quot;; <br><br>$mysql_result = mysql_query($query,$connection); <br>print (&quot;&lt;B&gt;&quot ;<br>print (&quot;&lt;Center&gt;&quot;<br>print (&quot;&lt;TABLE BORDER=\&quot;4\&quot;&gt;\n&quot; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Member Name&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Points&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br><br>print (&quot;&lt;TR&gt;\n&quot;<br><br><br><br>while ($row = mysql_fetch_array($mysql_result))<br>&nbsp;{ <br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>$query2 = &quot;SELECT SUM(Losses) FROM submitwk1 WHERE Loser='$name'&quot;; # now create a sql query to get a summary of wins from all lost games<br><br>$mysql_result2 = mysql_query($query2,$connection); <br>$row1 = mysql_fetch_row($mysql_result2);<br><br>$SumWins = $Wins+$row1[0]; #here we have the summary of all wins per Player!<br><br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$name&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$SumWins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br><br>} <br><br><br><br>// free resources and close connection <br>mysql_free_result($sql_result); <br><br>Thanks,<br>Rhyan<br><br>You have a good weekend too, unfortunately I still have more non PHP work to do this weekend lol<br><br>

 

[ati]

This sorting problem is related to the table design.<br>I will take to much time to explain what the best design might be. But here is an other workaround:<br><br>Instead of directly printing out the results, store them in an array an then sort them. This needs a few line in PHP.<br>&nbsp;<br>Or if you are not very strong in programming at all, then create an other table called 'Temp'. Use this table to store your result.<br><br>Try this ( I know it'd not a proper solution, but it does the job):<br><br><br>$query = &quot;SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY Winner ORDER BY sWins DESC&quot;; <br><br>$mysql_result = mysql_query($query,$connection); <br>print (&quot;&lt;B&gt;&quot ;<br>print (&quot;&lt;Center&gt;&quot;<br>print (&quot;&lt;TABLE BORDER=\&quot;4\&quot;&gt;\n&quot; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Member Name&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Points&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br><br>print (&quot;&lt;TR&gt;\n&quot;<br><br>$mytable=md5(uniqid(rand())); #create a unique name<br>$sql=&quot;CREATE TABLE $mytable (name varchar(100),wins int(10));&quot;;<br>$result=mysql_query($sql,$connection);<br><br><br>while ($row = mysql_fetch_array($mysql_result))<br>&nbsp;{ <br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>$query2 = &quot;SELECT SUM(Losses) FROM submitwk1 WHERE Loser='$name'&quot;; # now create a sql query to get a summary of wins from all lost games<br><br>$mysql_result2 = mysql_query($query2,$connection); <br>$row1 = mysql_fetch_row($mysql_result2);<br><br>$SumWins = $Wins+$row1[0]; #here we have the summary of all wins per Player!<br><br>$sql=&quot;INSERT INTO $mytable (name,wins) VALUE ('$name','$wins');&quot;;<br>$result=mysql_query($sql,$connection);<br><br>}<br><br>$sql=&quot;SELECT * FROM $mytable ORDER BY wins;&quot;;<br>$result=mysql_query($sql,$connection);<br>while ($row = mysql_fetch_array($result))<br>&nbsp;{ <br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$name&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$SumWins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br>}<br>&nbsp;<br><br>###########<br><br>This should do the job!<br><br><br>

 

[ati]

opps I was to fast!<br><br>Just add this at the end:<br><br>$sql=&quot;DROP TABLE $mytable;&quot;;<br>$result=mysql_query($sql,$connection);<br><br><br>You need to create a table and then delete this one after the job is done. With MySQL 3.23 you can use the TEMPORARY flag, then you don't need to drop the table at the end!<br><br><br>

 

[Rhyan]

Hmmm, I put it all together and now nothing is displayed, except for the Heading:<br><br>Heres how the code looks<br><br>$query = &quot;SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY Winner ORDER BY sWins DESC&quot;; <br><br>$mysql_result = mysql_query($query,$connection); <br>print (&quot;&lt;B&gt;&quot ;<br>print (&quot;&lt;Center&gt;&quot;<br>print (&quot;&lt;TABLE BORDER=\&quot;4\&quot;&gt;\n&quot; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Member Name&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Points&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br><br>print (&quot;&lt;TR&gt;\n&quot;<br><br>$mytable = md5(uniqid(rand())); #create a unique name<br>$sql = &quot;CREATE TABLE $mytable (name varchar(100),Wins int(10));&quot;;<br>$result = mysql_query($sql,$connection);<br><br><br>while ($row = mysql_fetch_array($mysql_result))<br>&nbsp;{ <br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>$query2 = &quot;SELECT SUM(Losses) FROM submitwk1 WHERE Loser='$name'&quot;; # now create a sql query to get a summary of wins from all lost games<br><br>$mysql_result2 = mysql_query($query2,$connection); <br>$row1 = mysql_fetch_row($mysql_result2);<br><br>$SumWins = $Wins+$row1[0]; #here we have the summary of all wins per Player!<br><br>$sql = &quot;INSERT INTO $mytable (name,Wins) VALUE ('$name','$Wins');&quot;;<br>$result = mysql_query($sql,$connection);<br><br>}<br><br>$sql = &quot;SELECT * FROM $mytable ORDER BY wins;&quot;;<br>$result = mysql_query($sql,$connection);<br>while ($row = mysql_fetch_array($result))<br>&nbsp;{ <br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$name&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$SumWins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br>}<br>$sql = &quot;DROP TABLE $mytable;&quot;;<br>$result = mysql_query($sql,$connection);<br><br><br>// free resources and close connection <br>mysql_free_result($sql_result); <br>mysql_close($connection); <br>?&gt;<br><br>Thanks for your help<br>Rhyan<br><br><br><br><br>

 

[ati]

you have a type here:<br><br>$sql = &quot;SELECT * FROM $mytable ORDER BY Wins;&quot;;<br>$result = mysql_query($sql,$connection);<br>while ($row = mysql_fetch_array($result))<br><br><br>you typed wins but you defined the fiels as Wins!<br><br>&nbsp;<br>

 

[Rhyan]

hmm, I corrected that but still no results show :-( It's just the headings. Is there anyway you could test this script and see whats happening, I have no idea <br><br><br>// create connection <br>// test connection <br>// select database <br>// test selection <br>$query = &quot;SELECT Winner,SUM(Wins) as sWins FROM submitwk1 GROUP BY Winner ORDER BY sWins DESC&quot;; <br><br>$mysql_result = mysql_query($query,$connection); <br>print (&quot;&lt;B&gt;&quot ;<br>print (&quot;&lt;Center&gt;&quot;<br>print (&quot;&lt;TABLE BORDER=\&quot;4\&quot;&gt;\n&quot; <br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Member Name&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD BGCOLOR=\&quot;#cccccc\&quot;&gt;&lt;B&gt;&lt;font size='8'&gt;Points&lt;/font&gt;&lt;B&gt;&lt;/TD&gt;\n&quot;<br><br>print (&quot;&lt;TR&gt;\n&quot;<br><br>$mytable = md5(uniqid(rand())); #create a unique name<br>$sql = &quot;CREATE TABLE $mytable (name varchar(100),Wins int(10));&quot;;<br>$result = mysql_query($sql,$connection);<br><br><br>while ($row = mysql_fetch_array($mysql_result))<br>&nbsp;{ <br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>$query2 = &quot;SELECT SUM(Losses) FROM submitwk1 WHERE Loser = '$name'&quot;; # now create a sql query to get a summary of wins from all lost games<br><br>$mysql_result2 = mysql_query($query2,$connection); <br>$row1 = mysql_fetch_row($mysql_result2);<br><br>$SumWins = $Wins+$row1[0]; #here we have the summary of all wins per Player!<br><br>$sql = &quot;INSERT INTO $mytable (name,Wins) VALUE ('$name','$Wins');&quot;;<br>$result = mysql_query($sql,$connection);<br><br>}<br><br>$sql = &quot;SELECT * FROM $mytable ORDER BY Wins;&quot;;<br>$result = mysql_query($sql,$connection);<br>while ($row = mysql_fetch_array($result))<br>&nbsp;{ <br>$name = $row[0]; <br>$Wins = $row[1]; <br><br>print (&quot;&lt;TR&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$name&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TD&gt;&lt;font size='5'&gt;$SumWins&lt;/font&gt;&lt;/TD&gt;\n&quot;<br>print (&quot;&lt;TR&gt;\n&quot;<br><br>}<br>$sql = &quot;DROP TABLE $mytable;&quot;;<br>$result = mysql_query($sql,$connection);<br><br><br>// free resources and close connection <br>mysql_free_result($sql_result); <br>mysql_close($connection); <br>?&gt;<br><br>

 

[ati]

Yes there is, if you would send me a dump of your database inculding table and structure. But I think that this will probably come to late and your Venue this weekend would be without this script. However, if you want, send me your script and Database dump to <A HREF="mailto:atmail@gmx.net">atmail@gmx.net</A> and&nbsp;&nbsp;I will have a look on Monday morning.<br><br>