14 Replies Latest reply: Aug 15, 2009 4:45 AM by LouieCypher RSS

    Rugby League table nightmare please help!!

    LouieCypher Community Member

      OK I'm sure this has been done before but after much web searching I'm totally stuck.

       

      I have been asked to create league for the local Rugby Club so created two tables:

       

      LeagueTeam:

       

      LeagueTeamsID int(11) PRIMARY
      TeamName varchar (40)
      Season int(11)

       


      LeagueResult:

       

      ResID int(11) PRIMARY
      LeagueID int(11)
      SeasonID int(11)
      hteam int(11)
      ateam int(11)
      hscore varchar(3)
      ascore varchar (3)

       

      I have looked around the internet and found the following Query which seems to do what I want but, not being too good with MySQL queries I need a lot of help

       


      SELECT TeamName AS Team,SUM(P) AS P,SUM(W) AS W,SUM(D) AS D,SUM(L) AS L,
      SUM(F) AS F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts
      FROM(
      SELECT
      hteam LeagueTeam,
      1 P,
      IF(hscore > ascore,1,0) W,
      IF(hscore = ascore,1,0) D,
      IF(hscore < ascore,1,0) L,
      hscore F,
      ascore A,
      hscore-ascore GD,
      CASE WHEN hscore > ascore THEN 2 WHEN hscore = ascore THEN 1 ELSE 0 END PTS
      FROM LeagueResult
      UNION ALL
      SELECT
      ateam,
      1,
      IF(hscore < ascore,1,0),
      IF(hscore = ascore,1,0),
      IF(hscore > ascore,1,0),
      ascore,
      hscore,
      ascore-hscore GD,
      CASE WHEN hscore < ascore THEN 2 WHEN hscore = ascore THEN 1 ELSE 0 END
      FROM LeagueResult
      ) as tot
      JOIN LeagueTeam ON tot.LeagueTeam=LeagueTeamsID
      GROUP BY Team
      ORDER BY SUM(Pts) DESC, SUM(GD) DESC

       


      When I run it in DreamweaverCS3 I get a #1111 invalid use of group function.

       

      When I run it in phpmyadmin the error message occures on  the line starting  " SELECT ateam, 1, "

       


      Please can anybody help me to get this working in Dreamweaver or suggest another way of doing it?

        • 1. Re: Rugby League table nightmare please help!!
          bregent MVP

          First, I see an error with SUM(Pts) as you have defined it as uppercase PTS in your derived table. I would also suggest that you use a different alias for your summed values than their original values. For example, use something like SUM(D) AS Sum_D.That probably isn't causing the query to fail, but it is ambiguous.

           

          If you are still having problems, I would start by testing the derived table query to make sure it does not contain any syntax errors.

          • 2. Re: Rugby League table nightmare please help!!
            LouieCypher Community Member

            Thanks bregent I will try that on monday when i get back to work and let you know

             

            I  have also been told that it may be  something to do with

            • 3. Re: Rugby League table nightmare please help!!
              LouieCypher Community Member

              oooops.

               

              as I was saying I have been told it may be something to do with the version of PHP not allowing sub selections??

               

              Not sure which version of PHP DreamweaverCS3 uses?

               

              any thoughts on that.

              • 4. Re: Rugby League table nightmare please help!!
                bregent MVP

                >as I was saying I have been told it may be something

                >to do with the version of PHP not allowing sub selections??

                 

                OK, first it would have nothing to do with the verison of PHP. Possibly they meant MySQL. This is not a sub-query it is more commonly known as a derived table.  AFAIK, MySQL started support of derived tables in 4.1. What version are you running? I have also heard that there are a lot of problems with derived tables in MySQL so you just might want to use a view instead.

                • 5. Re: Rugby League table nightmare please help!!
                  LouieCypher Community Member

                  OK thanks for all your help on this  bregent .

                   

                  mySQL version is: 4.1.22-standard

                   

                  As I said I am not brillian with mySQL (I can do basic queries and sorting) so I have not come across VIEW before.

                   

                  How would that work in this situation, could I still use VIEW to ceate my results from the tables i have in place?

                   

                  Sorry to be a pain but I feel i'm getting close and this is the last thing I need to finish.

                  • 6. Re: Rugby League table nightmare please help!!
                    bregent MVP

                    VIEW are just objects in sql that are based on select statements and can be used in queries just like tables. AFAIK, views were not supported in MySQL 4.1. That's a pretty old version with very limited sql functionality. Is there any way you can have it upgraded to new version?

                    • 7. Re: Rugby League table nightmare please help!!
                      LouieCypher Community Member

                      Thats the version used on my hosting site so I guess that means moving the site to another provider with a better server suite!

                       

                      I may well need a little help when thats done if you are still willing? I'm still interested in these VIEWS?

                      • 8. Re: Rugby League table nightmare please help!!
                        SimonYorkshire

                        I don't know if this would be cheating, but have you tried setting it up in Access and then viewing the query as an SQL view.  You can cut and paste this into your work then and the difficult work is done for you.

                         

                        Alternatively, you could change codes to Union.  I am sure that would be a lot easier. 

                         

                        If you dont have a version of Access, let me know.  I might be able to throw something together for you in there.  I dont have MySQL as I tend to go down the Access and asp route I am afraid so can't really help with all the other stuff.  I have a book called Dreamweaver Demystified which I find really good.  Mine is the MX 2004 version but I am sure that Adobe will have sorted out a CS3 or CS4 version as well.

                         

                        Good luck with it

                         

                        Simon

                        • 9. Re: Rugby League table nightmare please help!!
                          LouieCypher Community Member

                          OK changed to a server with 5.1 so now I put the query in Dreamweaver pressed Test and, Presto got this:

                          Picture 1.png

                          Hooray it works perfectly . . . or so I thought!

                           

                          when i try to access the Recordset from Dreamweaver's Bindings tab I get the following message?

                           

                          MySQL Error#: 1064

                           

                          You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as tot, LeagueTeam
                          WHERE 0 = 1 GROUP BY Team' at line 1

                           

                          It seems to be including a WHERE statement?

                           

                          it also runs when i test it in MySQL on the servers so we must be looking at a Dreamweaver problem here, any Ideas, I'm so close I can taste it!!

                          • 10. Re: Rugby League table nightmare please help!!
                            bregent MVP

                            Possibly DW doesn't like you using the alias 'Team' in the group by. Try using the column name instead.

                            • 11. Re: Rugby League table nightmare please help!!
                              LouieCypher Community Member

                              Changed Team to TeamName and got the same error

                               

                              Do you think its something to do with  'tot' or tot.

                               

                              Thanks for sticking with me on this one bregent if I knew were you lived I'd buy you a beer!

                               

                              Bacca

                              • 12. Re: Rugby League table nightmare please help!!
                                bregent MVP

                                Not really sure why DW is balking. Some things to try

                                 

                                1) Make sure you have renamed PTS in the derived table so it is consistent with the naming in the outer query.

                                2) Don't use alias names that are the same as column names. Rename them!

                                 

                                If neither of those is the issue, I would go ahead and turn the derived table into a View. It will be easier to read and manage the SQL and make troubleshooting easier.

                                • 13. Re: Rugby League table nightmare please help!!
                                  LouieCypher Community Member

                                  OK Bregent this is where I am,

                                   

                                  I discoverd if I ignore the error from dreamweaver and go straight into the code and bind a column to the page using the following syntax:

                                   

                                  <td><?php echo $row_rs_Results["Team"]; ?></td>
                                        <td><?php echo $row_rs_Results["P"]; ?></td>
                                        <td><?php echo $row_rs_Results["W"]; ?></td>
                                        <td><?php echo $row_rs_Results["D"]; ?></td>
                                        <td><?php echo $row_rs_Results["L"]; ?></td>
                                        <td><?php echo $row_rs_Results["F"]; ?></td>
                                        <td><?php echo $row_rs_Results["A"]; ?></td>
                                        <td><?php echo $row_rs_Results["GD"]; ?></td>
                                        <td><?php echo $row_rs_Results["PTS"]; ?></td>

                                  (rs_Results being the name of the Redordset I created) the page displays the correct information

                                   

                                   

                                  So here is the final Query

                                   

                                  SELECT TeamName AS Team,SUM(PP) AS P,SUM(WW) AS W,SUM(DD) AS D,SUM(LL) AS L, SUM(FF) AS F,SUM(AA) AS A,SUM(GLD) AS GD,SUM(PNTS) AS PTS
                                  FROM (
                                  SELECT hteam LeagueTeam,
                                  1 PP,
                                  IF(hscore > ascore,1,0) WW,
                                  IF(hscore = ascore,1,0) DD,
                                  IF(hscore < ascore,1,0) LL,
                                  hscore FF,
                                  ascore AA,
                                  hscore-ascore GLD,
                                  CASE WHEN hscore > ascore THEN 2 WHEN hscore = ascore THEN 1 ELSE 0 END PNTS
                                  FROM LeagueResult
                                  UNION ALL
                                  SELECT ateam,
                                  1,
                                  IF(hscore < ascore,1,0),
                                  IF(hscore = ascore,1,0),
                                  IF(hscore > ascore,1,0),
                                  ascore, hscore,
                                  ascore-hscore,
                                  CASE WHEN hscore < ascore THEN 2 WHEN hscore = ascore THEN 1 ELSE 0 END FROM LeagueResult
                                  ) AS T
                                  JOIN LeagueTeam ON T.LeagueTeam=LeagueTeamsID
                                  GROUP BY TeamName
                                  ORDER BY SUM(PNTS) DESC, SUM(GLD) DESC

                                  I have just one more request, I need to only choose the results that correspond to the latest season, here is the results table showing SeasonID

                                  LeagueResult

                                  ResID int(11) PRIMARY
                                  LeagueID int(11)
                                  SeasonID int(11)
                                  hteam int(11)
                                  ateam int(11)
                                  hscore varchar(3)
                                  ascore varchar (3)

                                  The seasonID will be passed via a url variable called SeasonNow .

                                   

                                  I'm know I need a:  SELECT SeasonID and also a WHERE SeasonID = SeasonNow

                                  How do incorporate this into my recordset without messing up what is already working?

                                   

                                  hope that makes sense.

                                  Bacca

                                  • 14. Re: Rugby League table nightmare please help!!
                                    LouieCypher Community Member

                                    This one is now sorted. Many, many thanks to Bregent - you were a great help.

                                     

                                    For those who come across this problem in the future I will leave the final query:

                                     

                                    SELECT TeamName AS Team, SUM(PP) AS P,SUM(WW) AS W,SUM(DD) AS D,SUM(LL) AS L, SUM(FF) AS F,SUM(AA) AS A,SUM(GLD) AS GD,SUM(PNTS) AS PTS 
                                    FROM ( 
                                    SELECT hteam LeagueTeam,  1 PP, 
                                    IF(hscore > ascore,1,0) WW, 
                                    IF(hscore = ascore,1,0) DD, 
                                    IF(hscore < ascore,1,0) LL, 
                                    hscore FF,
                                    ascore AA,
                                    hscore-ascore GLD, 
                                    CASE WHEN hscore > ascore THEN 2
                                    WHEN hscore = ascore THEN 1 ELSE 0 END PNTS
                                    FROM LeagueResult
                                    WHERE SeasonID = SeasonNow
                                    UNION ALL 
                                    SELECT ateam,  1,
                                    IF(hscore < ascore,1,0), 
                                    IF(hscore = ascore,1,0),
                                    IF(hscore > ascore,1,0), 
                                    ascore,
                                    hscore, 
                                    ascore-hscore, 
                                    CASE WHEN hscore < ascore THEN 2 WHEN hscore = ascore THEN 1 ELSE 0 END
                                    FROM LeagueResult WHERE SeasonID = SeasonNow
                                    ) AS T 
                                    JOIN LeagueTeam ON T.LeagueTeam=LeagueTeamsID
                                    GROUP BY TeamName
                                    ORDER BY SUM(PNTS) DESC, SUM(GLD) DESC