Posts: 1,000
Threads: 253
Joined: Feb 2008
For example...here is one of my friends show listing page for myspace:
http://collect.myspace.com/index.cfm?fus...=294756645
str friendID="294756645"
str url.format("http://collect.myspace.com/index.cfm?fuseaction=bandprofile.listAllShows&friendid=%s" friendID)
HtmlDoc d.InitFromWeb(url)
str s=d.GetHtml
HTML Looks like this for each entry:
<hr color="#6699CC" width="100%" align="center" size="2" noshade />
<table width="615" border="0" cellspacing="0" cellpadding="0" align="center">
<form action="http://events.myspace.com/index.cfm?fuseaction=mycalendar.saveevent&Mytoken=B35A30A0-8423-4E67-BB97557CB3C82B83906290030" method="post">
<input type="hidden" name="calEvtLocation" value="The Cup in Staten Island, NY">
<input type="hidden" name="calEvtTitle" value="Joey Figgiani at The Cup in Staten Island, NY">
<input type="hidden" name="calEvtStreet" value="388 Van Duzer Street">
<input type="hidden" name="calEvtCity" value="Staten Island">
<input type="hidden" name="calEvtState" value="New York">
<input type="hidden" name="calEvtZip" value="10304">
<input type="hidden" name="calEvtDateTime" value="01-22-2009 19:00">
<input type="hidden" name="fuseout" value="mycalendar">
<tr valign="top">
<td><b>
January, 22 2009
07:00 PM</b> - <b>The Cup in Staten Island, NY</b></td>
<td rowspan="2" align="right"><input type="Submit" value="Add to My Calendar" style="background-color:6699CC; border-width:1px; width:150 px; border-left-color:AEE4FF; border-top-color:AEE4FF; border-bottom-color:003366; border-right-color:003366; color:FFFFFF; font-size:10px; font-family:Verdana, Arial, Helvetica, sans-serif; font-weight:bold;"></td>
</tr>
<tr valign="top">
<td>388 Van Duzer Street, Staten Island, New York 10304 - FREE<br /><br />The first of many collective projects for me and my buddy, Steve and my first in NY in two years. Hope to see you there...</td>
</tr>
</form>
</table>
Important stuff a little more clearly from HTML above:
<input type="hidden" name="calEvtLocation" value="The Cup in Staten Island, NY">
<input type="hidden" name="calEvtTitle" value="Joey Figgiani at The Cup in Staten Island, NY">
<input type="hidden" name="calEvtStreet" value="388 Van Duzer Street">
<input type="hidden" name="calEvtCity" value="Staten Island">
<input type="hidden" name="calEvtState" value="New York">
<input type="hidden" name="calEvtZip" value="10304">
<input type="hidden" name="calEvtDateTime" value="01-22-2009 19:00">
Need to format each listing into a database of my own
#compile CSqlite
;;NOTE For sBand the HTML contains the band name in the calEvtTitle with the calEvtLocation
;;sBand=calEvtTitle-calEvtLocation
;;Description and Cost look like they will be a little more difficult to grab.
str dbfile="$desktop$\Shows.db"
str sql
str sBand calEvtDateTime calEvtLocation calEvtStreet calEvtCity calEvtZip calEvtState sCost sDescription
CSqlite db1.Open(dbfile)
db1.Exec("CREATE TABLE IF NOT EXISTS 'Shows' ('Band','DateTime,'Venue','Address','City','Zip_Code','State','Cost','Description'")
sql.format("INSERT INTO 'Shows' VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')" 'sBand calEvtDateTime calEvtLocation calEvtStreet calEvtCity calEvtZip calEvtState sCost sDescription')
db1.Exec(sql)
Need all of the gigs on the schedule to be inputed into the database...this is way above me right night, but I am anxious to learn 
Thanks,
Jimmy Vig
Posts: 1,000
Threads: 253
Joined: Feb 2008
BTW...what's the best way to prevent duplicate entries in the database?
I'm sure this isn't very easy, but I figured I'd ask right off so it can be considered when the Myspace code is getting inputed. This way I can run the program as many time to grab new data without worrying about it just generating new rows for duplicate entries!!
Thanks in advance,
Jimmy Vig
Posts: 12,240
Threads: 144
Joined: Dec 2002
Macro
out
str friendID="294756645"
str url.format("http://collect.myspace.com/index.cfm?fuseaction=bandprofile.listAllShows&friendid=%s" friendID)
HtmlDoc d.InitFromWeb(url)
str s=d.GetHtml
;out s
;d.GetForm(1 0 _s); out _s ;;debug
#compile CSqlite
str dbfile="$desktop$\Shows.db3"
str sql
CSqlite db1.Open(dbfile)
db1.Exec("CREATE TABLE IF NOT EXISTS Shows (Band,DateTime,Venue,Address,City,Zip_Code,State,Cost,Description)")
;to prevent adding duplicates, you could use eg Band UNIQUE above,
;but in your case this will not work because need to check several fields.
;Don't know how to do it.
ARRAY(POSTFIELD) a
int i
for i 1 1000000 ;;don't know how many forms
,d.GetForm(i 0 0 a); err break
,;out a[0].name
,if(a[0].name~"calEvtLocation"=0) break
,
,str sBand calEvtDateTime calEvtLocation calEvtStreet calEvtCity calEvtZip calEvtState sCost sDescription
,sBand=a[1].value
,calEvtDateTime=a[6].value
,;...
,
,sql.format("INSERT INTO Shows VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')" sBand calEvtDateTime calEvtLocation calEvtStreet calEvtCity calEvtZip calEvtState sCost sDescription)
,db1.Exec(sql)
,;err continue;;eg error if duplicate if Band UNIQUE used
Posts: 1,000
Threads: 253
Joined: Feb 2008
Totally great. I didn't know it'd be that simple d.GetForm() never knew about that. Wouldn't have figured it out without you! Thanks a bunch.
Here is some code I worked out with notes. Changed a few things around. Need help with UNIQUE database entries. Thanks again!
#compile CSqlite
str dbfile="$desktop$\Shows.db3"
str sql
CSqlite db1.Open(dbfile)
db1.Exec("CREATE TABLE IF NOT EXISTS Band (DateTime,Venue,Address,City,Zip_Code,State,Cost,Description)")
;;To prevent duplicate entries
;;Will use Band as it's own Table
;;Cannot use FriendID because there will be non-myspace data being inputed into database as well.
;;Will use DateTime as Unique ID
;;If duplicate entry exists - Need to have new data take priority and replace old data
out
str friendID="433427838";;Glitch in mypsace isn't showing Joey Figgiani's Name at all??
str Band="Tripwire"
str url.format("http://collect.myspace.com/index.cfm?fuseaction=bandprofile.listAllShows&friendid=%s" friendID)
HtmlDoc d.InitFromWeb(url)
str s=d.GetHtml
ARRAY(POSTFIELD) a
int i
for i 1 1000000
,d.GetForm(i 0 0 a); err break
,if(a[0].name~"calEvtLocation"=0) break
,;;Replaces funky characters with " ' "
,;;Is there any other way to replace all the funky characters?
,;;Are there any other funky characters that might pop up?
,a[0].value.findreplace("[226][128][153]" "'")
,;;DEBUG DATA
;,out a[0].value;;Returns Venue
,
;,out a[1].value;;Returns Event Title
,;;(Note for a[1]: only returns text " at Venue"
,;;;checked with str s=d.GetHtml; out s
,;;;It is in the QM HTML as " at Venue"
,;;;Checked in Firefox view source and is "BandName at Venue"
,;;;Not sure if QM or MySpace glitch.
,;;;Really it's fine, if friend ID is known, band name is known...except if band changes name
,;;;Could grab band name from text "All Shows for BANDNAME" to reference what is in database
,;;;If band name changes, what happens to data?
,
,;out a[2].value;;Returns Address
,;out a[3].value;;Returns City
,;out a[4].value;;Returns State
,;out a[5].value;;Returns Zip
,;out a[6].value;;Returns DateTime
,
,out "---------------------"
,str Venue=a[0].value
,str Address=a[2].value
,str City=a[3].value
,str State=a[4].value
,str Zip=a[5].value
,str DateTime=a[6].value
,str Event.format("'%s','%s','%s','%s','%s','%s'" DateTime Venue Address City State Zip)
,out Event
Many thanks,
Jimmy Vig
Posts: 1,000
Threads: 253
Joined: Feb 2008
Two more issues:
I just thought of a problem using DateTime as the unique key...What if a band updates the time of the show?
I'll still take an example on how to work with UNIQUE database entries because I am curious to see how it works, but I think I am going to rethink the structure of the program a bit.
Second issue is that if data is not inputted correctly into the Users calendar, offsets the order QM grabs data.
(e.g. on Joey Figgiani's Calendar FriendID=294756645 there are several entries that do not contain the street address. This throws off the index of the ARRAY.)
Could be fixed by getting title and running condition statement. Will update later.
Thanks bunches again!
Jimmy Vig
Posts: 12,240
Threads: 144
Joined: Dec 2002
Macro
out
str friendID="294756645"
str url.format("http://collect.myspace.com/index.cfm?fuseaction=bandprofile.listAllShows&friendid=%s" friendID)
HtmlDoc d.InitFromWeb(url)
str s=d.GetHtml
;out s
;d.GetForm(1 0 _s); out _s ;;debug
#compile CSqlite
str dbfile="$desktop$\Shows.db3"
str sql
CSqlite db1.Open(dbfile)
db1.Exec("BEGIN TRANSACTION") ;;makes faster and safer
db1.Exec("CREATE TABLE IF NOT EXISTS Shows (Band,DateTime,Venue,Address,City,Zip_Code,State,Cost,Description)")
ARRAY(POSTFIELD) a
int i
for i 1 1000000 ;;don't know how many forms
,d.GetForm(i 0 0 a); err break
,;out a[0].name
,if(a[0].name~"calEvtLocation"=0) break
,
,;str _ss; d.GetForm(i 0 _ss); out _ss; out "-----------------------------------"; continue ;;debug
,
,str sBand calEvtDateTime calEvtLocation calEvtStreet calEvtCity calEvtZip calEvtState sCost sDescription
,;sBand=a[0].value
,;calEvtDateTime=a[6].value
,;...
,
,;NOTE: some fields of some shows are missing. For example street.
,;Then we cannot simply use calEvtDateTime=a[6].value. Need to find a[?].name.
,
,int j
,str* sp=&sBand
,for(j 0 9) sp[j].all ;;clear all variables
,
,for(j 0 a.len) if(a[j].name="calEvtLocation") sBand=a[j].value; break
,for(j 0 a.len) if(a[j].name="calEvtDateTime") calEvtDateTime=a[j].value; break
,;...
,
,;before passing strings to sqlite, necessary to escape ' to ''
,for(j 0 9) sp[j].findreplace("'" "[39]'")
,
,;delete old row(s) where band and date fields match new band and date
,db1.Exec(sql.format("DELETE FROM Shows WHERE Band='%s' AND DateTime='%s'" sBand calEvtDateTime))
,
,sql.format("INSERT INTO Shows VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')" sBand calEvtDateTime calEvtLocation calEvtStreet calEvtCity calEvtZip calEvtState sCost sDescription)
,db1.Exec(sql)
db1.Exec("END TRANSACTION")
err+
,QMERROR e=_error; e.description.formata("[][]Last SQL statement: %s" sql)
,db1.Exec("ROLLBACK TRANSACTION"); err
,end e
|
