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.

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.

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?

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

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!!

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

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