Option Compare Text
Private Mysql_Connection As New ADODB.Connection
Private rs As New ADODB.Recordset
Private Sub cmdStart_Click()
Dim scoringSummary As String, teamHome As String, teamAway As String, situationType As String, homeTeamScored As Boolean, plusMinus As String, retrievedHTML As String, playerName As String
Dim goalData() As String, cellData() As String, playersInvolved_Team() As String, playersInvolved_Individual() As String
Mysql_Connection.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;UID=root;PWD=REMOVED;DATABASE=nhl" 'open our connection to the database
For gameCounter = 1 To 78
retrievedHTML = Inet.OpenURL("http://www.nhl.com/scores/htmlreports/20072008/GS020" & Format(gameCounter, "000") & ".HTM") 'get the html for the game summary
scoringSummary = FindBetween2(retrievedHTML, ez("<td align=`center` class=`sectionheading`>SCORING SUMMARY</td>"), ez("<table id=`VPenaltySummary`"))(0) 'filter out all garbage except the scoring summary
goalData = FindBetween2(scoringSummary, "<tr class=" & Chr(34), "</tr>") 'make an array containing each goal
For x = 0 To UBound(goalData) - 1
cellData = FindBetween2(goalData(x), ez("<td align=`center`>"), "</td>") 'cellData(3) = PP/EV/SH, 4 = Team that scored
teamAway = Replace(FindBetween2(scoringSummary, ez("<td class=`heading + bborder` align=`left`>"), "</td>")(3), " On Ice", "") 'the away team
teamHome = Replace(FindBetween2(scoringSummary, ez("<td class=`heading + bborder` align=`left`>"), "</td>")(4), " On Ice", "") 'the home team
homeTeamScored = IIf(cellData(4) = teamHome, True, False) 'did the home team score?
playersInvolved_Team = FindBetween2(goalData(x), "<td>", "</td>") 'get the cell containing the players on the ice for the goal
For y = 0 To 1 '0 = away team, 1 = home team
If (y = 0 And homeTeamScored = True) Or (y = 1 And homeTeamScored = False) Then plusMinus = " - 1" Else plusMinus = " + 1" 'determine if the players in that cell on the ice for the goal either deserve a + or a -
playersInvolved_Individual = FindBetween2(playersInvolved_Team(y), ez("<font style=`cursor:hand;` title=`"), "</font>") 'get an array containing each player in the cell of players on the ice for a particular team
For z = 0 To UBound(playersInvolved_Individual) - 1
playerName = Replace(CStr(FindBetween2(playersInvolved_Individual(z), " - ", Chr(34))(0)), "'", "") 'the player name, filtering out the ' character to prevent database ****ups ie brind'amour
If InStr(cellData(3), "PP") Then
situationType = IIf(plusMinus = " + 1", "PP", "SH") 'one mans powerplay is another teams shorthanded
ElseIf InStr(cellData(3), "SH") Then
situationType = IIf(plusMinus = " + 1", "SH", "PP")
ElseIf InStr(cellData(3), "EV") Then
situationType = "EV"
End If
'Debug.Print "INSERT INTO tblPlusMinus (playerName, " & situationType & ") VALUES ('" & playerName & "','" & Replace(plusMinus, " ", "") & "') ON DUPLICATE KEY UPDATE " & situationType & " = " & situationType & plusMinus ' Uncomment this to see the queries as they are made
rs.Open "INSERT INTO tblPlusMinus (playerName, " & situationType & ") VALUES ('" & playerName & "','" & Replace(plusMinus, " ", "") & "') ON DUPLICATE KEY UPDATE " & situationType & " = " & situationType & plusMinus, Mysql_Connection 'and finally insert/update
Next z
Next y
Next x
Next gameCounter
End Sub
Private Function ez(m As String) As String 'quick function to replace the ` character with the " character
ez = Replace(m, "`", Chr(34))
End Function
Private Function FindBetween2(s As String, mStart As String, mEnd As String) As String()
On Error Resume Next
'find the content between the start and the end returned in an array with all matches
Dim Ar() As String, tmp() As String, i As Integer
ReDim tmp(0)
Ar = Split(s, mStart)
ReDim Preserve tmp(0) As String
For i = 1 To UBound(Ar)
If InStr(Ar(i), mEnd) Then tmp(UBound(tmp)) = Left$(Ar(i), InStr(1, Ar(i), mEnd) - 1): ReDim Preserve tmp(UBound(tmp) + 1) As String
Next
FindBetween2 = tmp
Erase tmp
End Function
