I am trying to parse the following link
http://api.sports.yahoo.com/ncaab/V1/getGamesByDate?date=2007-03-23
and can't seem to get any of the demos to work with this content for some reason.. I first just tried feeding the link directly and it says autodetect failed, then I tried pasting the source and this didn't work either.
What I am trying to accomplish is to build a mysql database of current and past games & scores using this data to be later accessed via php to turn it back into a simple xml file. Can this be done with this source?
The info I would need to extract out of this xml would be the following:
event-status
date-coverage-type
code-key
event-metadata-basketball period-value
period-time-remaining
team-metadata team-key - alignment, name full, team-stats score, event-outcome
Any help would be GREATLY appreciated!! I am with a local university and am trying to do this on my own for our basketball department but I think I've taken on more then I can handle. If someone would be interested in building this out for me, I have a small budget I can work from as well if need be as well. Thanks so much for reading this and look forward to hearing your response.
Chris
Thank you!! I think I am close but do you recommend using the explode command to put it in mysql or is there something else I should be using? I don't know if this would matter in the way I get this data in but if a game is in progress I would like it to update the score field for that particular game and not just add a new record because it is different. Thanks again for your help, I will be purchasing your product today!
Chris
Hi Chris,
No problem; looking at the data you can use "SPORTS-EVENT-ID" to see if a record for the current game already exists, and then either update or insert as necessary. Personally, I would not use the explode function - simply refer to the elements of $record directly. Here's an example (not complete):
<?php
function myRecordHandler($record)
{
// extract the required game variables from $record
// this isn't strictly necessary, but keeps the code neater
$id = $record["SPORTS-EVENT-ID"];
$home_score = $record["TEAM/TEAM-STATS@1-SCORE"];
$away_score = $record["TEAM/TEAM-STATS-SCORE"];
// see if the record already exists
$sql = "SELECT id FROM games WHERE id='".$id."'";
$result = mysql_query($sql);
if (mysql_num_rows($result))
{
// record already exists, so update
$sql = "UPDATE games SET home_score='".$home_score."',away_score='".$away_score."' WHERE id='".$id."'";
}
else
{
// create a new record
$sql = "INSERT INTO games SET id='".$id."',home_score='".$home_score."',away_score='".$away_score."'";
}
mysql_query($sql);
}
?>
Hope this helps!
Cheers,
David.
Ok, after adding in magicparser, does this all look ok?
<?php
header("Content-Type: text/plain");
require("MagicParser.php");
function myRecordHandler($record)
{
// extract the required game variables from $record
// this isn't strictly necessary, but keeps the code neater
$url = "http://api.sports.yahoo.com/ncaab/V1/getGamesByDate?date=2007-03-23";
$id = $record["SPORTS-EVENT-ID"];
$home_score = $record["TEAM/TEAM-STATS@1-SCORE"];
$away_score = $record["TEAM/TEAM-STATS-SCORE"];
// see if the record already exists
$sql = "SELECT id FROM games WHERE id='".$id."'";
$result = mysql_query($sql);
if (mysql_num_rows($result))
{
// record already exists, so update
$sql = "UPDATE games SET home_score='".$home_score."',away_score='".$away_score."' WHERE id='".$id."'";
}
else
{
// create a new record
$sql = "INSERT INTO games SET id='".$id."',home_score='".$home_score."',away_score='".$away_score."'";
}
mysql_query($sql);
}
MagicParser_parse($url,"myRecordHandler","xml|SPORTS-CONTENT/SCHEDULE/SPORTS-EVENT/");
?>Hi Chris,
That looks almost there.... Remember that the following line only really exists to make the output of print_r() legible without doing any HTML formatting:
header("Content-Type: text/plain");...so depending upon you final script you probably don't need that. Your final solution will also require the mysql connection code outside of the record handler, as before you can call mysql_query you have to have done a mysql_connect() followed by mysql_select_db(). See the following page for example code using these functions:
http://uk.php.net/manual/en/function.mysql-connect.php
and:
http://uk.php.net/manual/en/function.mysql-select-db.php
This would make you final script similar to the following:
<?php
header("Content-Type: text/plain");
require("MagicParser.php");
function myRecordHandler($record)
{
// extract the required game variables from $record
// this isn't strictly necessary, but keeps the code neater
$url = "http://api.sports.yahoo.com/ncaab/V1/getGamesByDate?date=2007-03-23";
$id = $record["SPORTS-EVENT-ID"];
$home_score = $record["TEAM/TEAM-STATS@1-SCORE"];
$away_score = $record["TEAM/TEAM-STATS-SCORE"];
// see if the record already exists
$sql = "SELECT id FROM games WHERE id='".$id."'";
$result = mysql_query($sql);
if (mysql_num_rows($result))
{
// record already exists, so update
$sql = "UPDATE games SET home_score='".$home_score."',away_score='".$away_score."' WHERE id='".$id."'";
}
else
{
// create a new record
$sql = "INSERT INTO games SET id='".$id."',home_score='".$home_score."',away_score='".$away_score."'";
}
mysql_query($sql);
}
mysql_connect("localhost","username","password");
mysql_select_db("sports");
MagicParser_parse($url,"myRecordHandler","xml|SPORTS-CONTENT/SCHEDULE/SPORTS-EVENT/");
?>
This example assumes that you have created a db called "sports", and created a table called games with fields suitable for the values you will be inserting. I would recommend keeping things simple with using VARCHAR fields of length 255 for each value...
Hope this helps!
Cheers,
David.
Thanks once again for your help, I feel like a pain but I really appreciate everything! I have uploaded this to my test server, changed the mysql_connect values to match and created all the databases & tables but when I try to run this, it just responds with a blank file that it wants me to download..
Hi Chris,
Have you remove the content-type header at the top? This shouldn't normally be required. What you need to do is to add some debug code to print out messages as to where your script has got to. Another test you can do is print out your SQL statements, for example:
print $sql;...and then enter the SQL into phpMyAdmin (or whatever MySQL admin tool you are using) and check for any error messages.
Feel free to email me a copy of your test script and i'll take a quick look - reply to your reg code or forum registration email is the easiest way to get me...
Cheers,
David.
Hi Chris,
Autodetection does not work for every XML document, as sometimes it cannot determine the repeating element. The file does work with Magic Parser, it just requires the correct format string to be provided in the call to MagicParser_parse(). Here's a demo running on this server:
http://www.magicparser.com/examples/sports.php
Here's the source:
<?phpheader("Content-Type: text/plain");
require("MagicParser.php");
function myRecordHandler($record)
{
print_r($record);
}
$url = "http://api.sports.yahoo.com/ncaab/V1/getGamesByDate?date=2007-03-23";
MagicParser_parse($url,"myRecordHandler","xml|SPORTS-CONTENT/SCHEDULE/SPORTS-EVENT/");
?>
You would then add code to load each event into your database in the myRecordHandler() function. To access the date-coverage-type value for example, you would use the variable:
$record["EVENT-METADATA-DATE-COVERAGE-TYPE"];(remember that Magic Parser provides all key names in UPPER CASE)
Hope this helps!
Cheers,
David.