|
|
here is my SQL query: SELECT
s_1.Number,
s_1.`Rider Name`,
s_1.`Horse Name`,
s_1.`Division Name`,
s_1.`Level`,
s_1.`Division Type`,
s_1.`Show Type`,
s_1.`Dressage Test`,
TRIM(s_1.Score1)+0 AS Score1,
TRIM(s_1.Score2)+0 AS Score2,
TRIM(s_1.Score3)+0 AS Score3,
TRIM(s_1.Score4)+0 AS Score4,
TRIM(s_1.Score5)+0 AS Score5,
TRIM(s_1.Score6)+0 AS Score6,
TRIM(s_1.Score7)+0 AS Score7,
TRIM(s_1.Score8)+0 AS Score8,
TRIM(s_1.Score9)+0 AS Score9,
TRIM(s_1.Score10)+0 AS Score10,
TRIM(s_1.Score11)+0 AS Score11,
TRIM(s_1.Score12)+0 AS Score12,
TRIM(s_1.Score13)+0 AS Score13,
TRIM(s_1.Score14)+0 AS Score14,
TRIM(s_1.Score15)+0 AS Score15,
TRIM(s_1.Score16)+0 AS Score16,
TRIM(s_1.Score17)+0 AS Score17,
TRIM(s_1.Score18)+0 AS Score18,
TRIM(s_1.Score19)+0 AS Score19,
TRIM(s_1.Score20)+0 AS Score20,
TRIM(s_1.Score21)+0 AS Score21,
TRIM(s_1.Score22)+0 AS Score22,
TRIM(s_1.Score23)+0 AS Score23,
TRIM(s_1.Score24)+0 AS Score24,
s_1.Marks,
s_1.Penalties,
s_1.`Net Results`,
s_1.`Result Total`,
s_2.Total,
s_2.rank AS RANK ,
s_1.`Time Out`,
s_1.`Time In`,
TIMEDIFF(s_1.`Time In`, s_1.`Time Out`) AS `Time Elapsed`,
s_1.`Speed Fault`,
s_1.`Optimum Time`,
GREATEST(TIMESTAMPDIFF(SECOND,s_1.`Time Elapsed`,s_1.`Speed Fault`), 0) AS `Under Time`,
GREATEST(TIMESTAMPDIFF(SECOND, s_1.`Optimum Time`, s_1.`Time Elapsed`), 0) AS `Over Time`,
s_1.`XC Time`,
s_1.`XC Jump`,
s_1.`SJ Time`,
s_1.`SJ Jump`,
s_3.`Final Total`,
s_3.rank AS Final_rank,
s_1.Scratched,
s_1.Withdrew,
s_1.Eliminated
FROM Scoring s_1
LEFT JOIN (SELECT
NUMBER,
(CASE
WHEN Scratched = '1' THEN ''
WHEN Withdrew = '1' THEN ''
WHEN Eliminated = '1' THEN ''
ELSE Total
END) AS Total,
@curRank := CASE
WHEN `Total` IS NULL THEN NULL
WHEN Scratched = '1' THEN 'S'
WHEN Withdrew = '1' THEN 'W'
WHEN Eliminated = '1' THEN 'E'
WHEN @partval = `Division Name` AND @rankval = Total THEN @curRank
WHEN @partval = `Division Name` AND (@rankval := Total) IS NOT NULL THEN @curRank + 1
WHEN (@partval := `Division Name`) IS NOT NULL AND (@rankval := Total) IS NOT NULL THEN 1
END AS RANK,
`Division Name`
FROM Scoring s ,(SELECT @curRank := 0, @partval := NULL, @rankval := NULL ) r
ORDER BY `Division Name`,Total ) AS s_2
ON s_1.number = s_2.number
LEFT JOIN (
SELECT
NUMBER,
(CASE
WHEN Scratched = '1' THEN ''
WHEN Withdrew = '1' THEN ''
WHEN Eliminated = '1' THEN ''
ELSE `Final Total`
END) AS `Final Total`,
@finalRank := CASE
WHEN `Total` IS NULL THEN NULL
WHEN Scratched = '1' THEN 'S'
WHEN Withdrew = '1' THEN 'W'
WHEN Eliminated = '1' THEN 'E'
WHEN @partval_final = `Division Name` AND @rankval_final = Total THEN @finalRank
WHEN @partval_final = `Division Name` AND (@rankval_final := Total) IS NOT NULL THEN @finalRank + 1
WHEN (@partval_final := `Division Name`) IS NOT NULL AND (@rankval := Total) IS NOT NULL THEN 1
END AS RANK,
`Division Name`
FROM Scoring s ,(SELECT @finalRank := 0, @partval_final := NULL, @rankval_final := NULL ) r
ORDER BY `Division Name`,`Final Total`) AS s_3 ON s_1.number = s_3.number
ORDER BY s_1.`Division Name`,s_2.rank
and I am unable to delete or edit anything from the site for that table, however I have code the adds the first 4 column in the statement from another table into this scoring table and that works. I also just tried and changed the number field from a lookup to just text, so I could enter a number, since thats the primary index, and it let me add, but when i went to edit that entry, got the same as below. Here are the error messages: from the edit page: <<< Record was NOT edited >>>
Unknown column 's_1.Number' in 'where clause'When trying to delete from list page: php error happened
Technical information
Error type 256
Error description Unknown column 's_1.Number' in 'where clause'
URL scoring.dynamicwebhost.net/huntersrun/testing/scoring_list.php
Error file /nacweb07/b1969/as.us/public_html/testing/connections/Connection.php
Error line 668
SQL query DELETE FROM `Scoring` WHERE ( ( s_1.`Number`=56 ) )I looked and that line just has some code to display the PHP error, so not sure whats going on, do you have any Ideas? Oh and I also tried changing the query from s_1.Number, to s_1.Number AS Number, since thats what it was before I did the ranking, to see if that would make a difference, but it didn't. Thanks for any help you can give me. |
