1 / 29

SQL, HTML, PHP

SQL, HTML, PHP. Week 10. Review and Questions. Topics from last lecture T erminology Questions?. Objectives. SQL Review Auto Increment Group By Joins HTML / PhP Forms Drop Down List Action Scripts – Insert. SQL. Auto Increment. Key Word for MS SQL – Identity

robin-noble
Download Presentation

SQL, HTML, PHP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL, HTML, PHP Week 10

  2. Review and Questions • Topics from last lecture • Terminology • Questions?

  3. Objectives • SQL Review • Auto Increment • Group By • Joins • HTML / PhP • Forms • Drop Down List • Action Scripts – Insert

  4. SQL

  5. Auto Increment • Key Word for MS SQL – Identity • Can specify at time of table creation – The easiest • CREATE TABLE PlayerDetails( PlayerID INT Primary Key Identity, PlayerFirstName Char(30) Not Null, … ); • Or alter tables to include Identity

  6. Happy Valley Golf

  7. Altering Player ID to include -- PlayerID Alter Table PlayerHandicap drop constraint PLAYERID_FK; Alter Table Player_Rounds drop constraint PLAYERID2_FK; Alter Table PlayerDetails drop constraint PLAYERID_PK; Alter Table PlayerDetails Drop column PlayerId; Alter Table PlayerDetails Add PlayerIDintidentity; Alter Table PlayerDetails Add constraint pk_PlayerID primary key(PlayerId); Alter Table PlayerHandicap Add Constraint PLAYERID_FK Foreign Key (PlayerId) References PlayerDetails(PlayerId); Alter Table Player_Rounds Add Constraint PLAYERID2_FK Foreign Key (PlayerId) References PlayerDetails(PlayerId);

  8. Group By • The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. • How many rounds of Golf have been played? • How many rounds of Golf have been played at each golf course? (Just need the id for the course)

  9. Group By • How many rounds of Golf have been played? • SELECT Count(RoundId) AS NumberofRounds FROM GolfRounds; • How many rounds of Golf have been played at each golf course? (just need id for the course) • SELECT CourseID, Count(RoundId) AS NumberofRounds FROM GolfRounds GROUP BY CourseID;

  10. Having • Allows for the results to be displayed for only a selection of the results of the function (count, average, etc). • How many multiple rounds of Golf have been played at each golf course (display results for only courses that have more than 1 round)? (just need id for the course)

  11. Having • How many multiple rounds of Golf have been played at each golf course (display results for only courses that have more than 1 round)? (just need id for the course) • SELECT CourseID, Count(RoundId) AS NumberofRounds FROM GolfRounds GROUP BY CourseID; HAVING COUNT(RoundId) > 1;

  12. One Step Farther • How many rounds of Golf have been played at each golf course and what golf course have they been played at (name of the course)? • SELECT CourseName, Count(RoundId) AS NumberofRounds FROM GolfRoundsAS GR , GolfCourses AS GC WHERE GR.CourseID= GC.CourseID GROUP BY GR.CourseID, CourseName;

  13. Join • Needed when the results displayed come from multiple tables. • If the results to display come from one table you can use a subquery

  14. Inner Joins • Returns the rows when there is at least one match in both tables. • SELECT CourseName, Count(RoundId) FROMGolfRounds AS GR , GolfCourses AS GC WHERE GR.CourseID = GC.CourseID GROUP BY GR.CourseID, CourseName;

  15. Join … On • Will give the same results as the join query on the previous slide • SELECT CourseName, Count(RoundId) FROM GolfRoundsAS GR JOIN GolfCourses AS GC ON GR.CourseID= GC.CourseID GROUP BY GR.CourseID, CourseName;

  16. Outer Join • Returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. • How many rounds of Golf have been played at each golf course and what golf course have they been played at (name of the course)? Display all golf courses even if there has been no rounds played. • SELECT CourseName, Count(RoundID) FROM GolfRounds AS GR RIGHT JOIN GolfCourses AS GC ON GR.CourseID = GC.CourseID GROUP BY GR.CourseID, CourseName;

  17. Outer Join • Can use left Join as well. • SELECT CourseName, Count(RoundID) FROM GolfCourses AS GC LEFT JOINGolfRounds AS GR ON GR.CourseID= GC.CourseID GROUP BY GR.CourseID, CourseName;

  18. Multiple Joins • You can use multiple tables together, not just two. • Show player’s names along with their average hole score and their handicap, for all players that have a handicap and have recorded round scores. • SELECT PlayerFirstName, PlayerLastName, Avg(HoleScore) AS AvgHoleScore, Avg(HandicapScore) AS AvgHandicapScore FROM PlayerDetails AS PD, Player_Rounds AS PR, PlayerHandicap AS PH WHERE PD.PlayerID = PR.PlayerID AND PR.PlayerID = PH.PlayerID GROUP BY PlayerLastName, PlayerFirstName

  19. Multiple Joins .. Using JOIN…. ON • Show all player’s names along with their average hole score and their handicap • SELECT PlayerFirstName, PlayerLastName, Avg(HoleScore) AS AvgHoleScore, Avg(HandicapScore) AS AvgHandicapScore FROM(PlayerDetails AS PD JOINPlayer_Rounds AS PR ONPD.PlayerID= PR.PlayerID)JOIN PlayerHandicapAS PH ONPR.PlayerID = PH.PlayerID GROUP BY PlayerLastName, PlayerFirstName

  20. Multiple Joins – Outer Join • Show all player’s names along with their average hole score and their handicap • SELECT PlayerFirstName, PlayerLastName, Avg(HoleScore) AS AvgHoleScore, Avg(HandicapScore) AS AvgHandicapScore FROM (PlayerDetails AS PD LEFT JOIN Player_Rounds AS PR ON PD.PlayerID = PR.PlayerID) LEFT JOIN PlayerHandicap AS PH ON PR.PlayerID = PH.PlayerID GROUP BY PlayerLastName, PlayerFirstName

  21. Practice • 1.) Display each golf courses name, the average yards and par for holes at each course. • 2.) Display each golf courses name, the average yards and par for holes at each course, and the number of holes played at the course. (Include all courses) • 3.) Display each golf courses name, the average yards and par for holes at each course, and the number of rounds played at the course. (Include all courses)

  22. Question 1 • Display each golf courses name, the average yards and par for holes at each course. • SELECT CourseName, Avg(YardsForHole) AS AvgYardsForHole, Avg(ParForHole) AS ParForHole FROM GolfCourses AS GC LEFT JOIN CourseHoleDetails AS CHD ON GC.CourseID = CHD.CourseID GROUP BY GC.CourseID, CourseName

  23. Question 2 • Display each golf courses name, the average yards and par for holes at each course, and the number of holes played at the course. (Include all courses) • SELECT CourseName, Count(RoundId) AS NumberofHolesPlayer, Avg(YardsForHole) AS AvgYardsForHole, Avg(ParForHole) AS ParForHole FROM (GolfCourses AS GC LEFT JOIN GolfRounds AS GR ON GR.CourseID = GC.CourseID) RIGHT JOIN CourseHoleDetailsAS CHD ON GC.CourseID = CHD.CourseID GROUP BY GR.CourseID, CourseName;

  24. Question 3 • Display each golf courses name, the average yards and par for holes at each course, and the number of rounds played at the course. (Include all courses) • SELECT CourseName, Count(DISTINCTRoundId) AS NumberofHolesPlayer, Avg(YardsForHole) AS AvgYardsForHole, Avg(ParForHole) AS ParForHole FROM (GolfCourses AS GC LEFT JOIN GolfRounds AS GR ON GR.CourseID = GC.CourseID) RIGHT JOIN CourseHoleDetails AS CHD ON GC.CourseID = CHD.CourseID GROUP BY GR.CourseID, CourseName;

  25. HTML / PHP

  26. Forms <body> <h1>My Lab 10 Form Page</h1> <h2>Add A Golf Handicap:</h2> <form action="lab10_action.php" method="post"> Handicap Date: <input name="HandicapDate" type="text" /> (format: 2013-1-31)<br /> Handicap Score: <input name="HandicapScore" type="text" /> (format: 1 or -1)<br /> <input type="submit" /> </form> </body>

  27. Drop Down List <body> <h1>My Lab 10 Form Page</h1> <h2>Add A Golf Handicap:</h2> <form action="lab10_action.php" method="post"> //add Stuff Here for selection box Handicap Date: <input name="HandicapDate" type="text" /> (format: 2013-1-31)<br /> Handicap Score: <input name="HandicapScore" type="text" /> (format: 1 or -1)<br /> <input type="submit" /> </form> </body>

  28. Drop Down List <p>Select a player: <select name="PlayerID"> <?php //Set Server, User name, Password, DB Variables //connection to the database //select a database to work with //declare the SQL statement that will query the database $query = "SELECT * FROM PlayerDetails "; //execute the SQL query and return records $result = mssql_query($query); $numRows = mssql_num_rows($result); echo "(" . $numRows . " Player" . ($numRows == 1 ? "" : "s") . " Found )"; //display the results while($row = mssql_fetch_array($result)){ echo "<option value='" . $row["PlayerID"] ."'>" . $row["PlayerFirstName"] . $row["PlayerLastName"]. "</option>”;} //close the connection ?>

  29. Action Script - Insert <html> <head><title>Lab 10 Action Page</title> </head> <body> <h1>My Lab 10 Action Page (add a Handicap)</h1> <p><?php //All the beginning Connection Stuff. //declare the SQL statement that will query the database $query = "INSERT INTO PlayerHandicap"; $query .= "(PlayerID, HandicapDate, HandicapScore)"; $query .= "VALUES ($PlayerID, '$HandicapDate', '$HandicapScore' )"; //execute the SQL query and return records $result = mssql_query($query); echo "1 record added <br />"; echo "You posted a handicap of ". $HandicapScore . " on " . $HandicapDate . " for player "; echo " " . $PlayerID ."<br /><hr />"; //close the connection ?></p> </body> </html>

More Related