March 4th, 2013, 05:27 PM
|
|
|
|
Dont insert the data in the table if that data already exists
I have 3 tables in my database, MEMBERS, TOKENS, CHECKINS
I have members signed up on my website.
Some of my members (NOT ALL) approved me to get their data from other websites so I saved their tokens in the TOKENS table.
My codes are currently messed up, they keep inserting the data even if it is saved in the database before.
1. Match each user from MEMBERS table with his saved token in the TOKENS table. NO ERROR
2. Pull all the check in data for each user from another website using his saved token in the TOKENS table. NO ERROR
3. Check whether the data pulled is already in the CHECKINS table; insert it only if it was not inserted before. ERROR
PHP Code:
SQL = "SELECT M.MEMBERID, M.ACTIVE"
SQL = SQL & " FROM MEMBERS M"
SQL = SQL & " WHERE M.ACTIVE = 1"
Set objMembers = objConn.Execute(SQL)
Do While NOT objMembers.EOF
SQL = "SELECT T.TOKENID, T.MEMBERID, T.TOKENTYPE, T.TOKEN_CODE"
SQL = SQL & " FROM TOKENS T"
SQL = SQL & " WHERE T.MEMBERID = " & objMembers("MEMBERID") &" AND T.TOKENTYPE = """& strTokenType &""""
Set objCheckins = objConn.Execute(SQL)
If NOT objCheckins.EOF Then
Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xmlhttp.open "GET", strURLC & "oauth_token=" & objCheckins("TOKEN_CODE") & "&v=" & FormatVersionDate(Date()), false
xmlhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
xmlhttp.send
TOKEN = xmlhttp.responseText
Set Info = JSON.parse(TOKEN)
On Error Resume Next
Set FirstItem = Info.response.checkins.items.shift()
On Error Resume Next
strVenueName = FirstItem.venue.name
On Error Resume Next
strlat = FirstItem.venue.location.lat
On Error Resume Next
strlng = FirstItem.venue.location.lng
On Error Resume Next
strCity = FirstItem.venue.location.city
If strCity = "" Then
strCity = "N/A"
End If
On Error Resume Next
strCountry = FirstItem.venue.location.country
On Error Resume Next
strcreatedAt = FirstItem.createdAt
strcreatedAt = DateAdd("s", strcreatedAt, "01/01/1970 00:00:00")
On Error Resume Next
Set FirstItem = nothing
' SQL = ("SELECT COUNT(MEMBERID) AS CHECKINCOUNT FROM CHECKINS WHERE MEMBERID = "& objMembers("MEMBERID") &"")
' Set objCheckinCount = objConn.Execute(SQL)
' strCheckinCount = objCheckinCount("CHECKINCOUNT")
' CheckinCountArray = CheckinCountArray + (strCheckinCount & ",")
' SQL = ("SELECT MEMBERID, VENUECREATEDAT FROM CHECKINS WHERE MEMBERID = "& objMembers("MEMBERID") &"")
' Set objCheck = objConn.Execute(SQL)
' If NOT objCheck.EOF Then
' Do While NOT objCheck.EOF
' If objCheck("VENUECREATEDAT") = strcreatedAt Then
' strCheck = 0
' Else
' strCheck = 1
' End If
' strCheck = strCheck + strCheck
' objCheck.Movenext
' Loop
' Else
' strCheckArray = 1
' End If
' strrCheckArray = strrCheckArray + (strCheck & ",")
' Response.Write "<br>check in times:" & strCheckinCount & "member:" & objMembers("MEMBERID") & " check:" & strCheck
' If strCheck > 0 Then
' SQL = "INSERT INTO CHECKINS (CHECKINPLATFORM, MEMBERID, VENUENAME, VENUELAT, VENUELNG, VENUECITY, VENUECOUNTRY, VENUECREATEDAT, CHECKINDATEENTERED) VALUES ('"& strTokenType &"', '"& objMembers("MEMBERID") &"', '"& strVenueName &"', '"& strlat &"', '"& strlng &"', '"& strCity &"', '"& strCountry &"', '"& FormatDateMySQL(strcreatedAt) &"', '"& FormatDateMySQL(NOW) &"')"
' Set objAddC = objConn.execute(SQL)
' SQL = "UPDATE MEMBERS SET IMAGEURL = '"& strImageURL &"' WHERE MEMBERID = '"& objMembers("MEMBERID") &"'"
' Set objAddI = objConn.execute(SQL)
'x End If
End If
objMembers.Movenext
Loop
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
