Hi thank you for the replay i was busy few days, i fixed the last issu, but now the data from the xml for HomeGoalDetails cant be saved in to table.
The XML data
<XMLSOCCER.COM>
<Match>
<Id>96099</Id>
<FixtureMatch_Id>362047</FixtureMatch_Id>
<Date>2016-05-15T11:30:00+00:00</Date>
<Round>38</Round>
<Spectators>16020</Spectators>
<League>Scottish Premier League</League>
<HomeTeam>Hearts</HomeTeam>
<HomeTeam_Id>50</HomeTeam_Id>
<HomeCorners>4</HomeCorners>
<HomeGoals>2</HomeGoals>
<HalfTimeHomeGoals>2</HalfTimeHomeGoals>
<HomeShots>6</HomeShots>
<HomeShotsOnTarget>5</HomeShotsOnTarget>
<HomeFouls>19</HomeFouls>
<HomeGoalDetails>
20':Own Joe Shaughnessy;17': Arnaud Sutchuin Djoum;
</HomeGoalDetails>
<HomeLineupGoalkeeper>Jack Hamilton</HomeLineupGoalkeeper>
<HomeLineupDefense>
Callum Paterson; John Souttar; Alim Oezturk; Juwon Oshaniwa;
</HomeLineupDefense>
<HomeLineupMidfield>
Arnaud Sutchuin Djoum; Perry Kitchen; Prince Buaben; Lewis Moore;
</HomeLineupMidfield>
<HomeLineupForward>Abiola Dauda; Juan Delgado;</HomeLineupForward>
<HomeLineupSubstitutes>
Dario Zanatta; Neil Alexander; Sam Nicholson; Jordan McGhee; Gavin Reilly; Liam Smith; Miguel Pallardo;
</HomeLineupSubstitutes>
<HomeYellowCards>4</HomeYellowCards>
<HomeRedCards>1</HomeRedCards>
<HomeSubDetails>
68': in Gavin Reilly;68': out Juan Delgado;62': out Lewis Moore;62': in Sam Nicholson;53': out Juwon Oshaniwa;53': in Liam Smith;
</HomeSubDetails>
<AwaySubDetails>
83': in Steven MacLean;83': out Liam Gordon;68': in Greg Hurst;68': out Christopher Kane;
</AwaySubDetails>
<HomeTeamFormation>4-4-2</HomeTeamFormation>
<AwayTeam>St Johnstone</AwayTeam>
<AwayTeam_Id>46</AwayTeam_Id>
<AwayCorners>8</AwayCorners>
<AwayGoals>2</AwayGoals>
<HalfTimeAwayGoals>2</HalfTimeAwayGoals>
<AwayShots>26</AwayShots>
<AwayShotsOnTarget>7</AwayShotsOnTarget>
<AwayFouls>8</AwayFouls>
<AwayGoalDetails>12': Graham Cummins;9': penalty Liam Craig;</AwayGoalDetails>
<AwayLineupGoalkeeper>Alan Mannus</AwayLineupGoalkeeper>
<AwayLineupDefense>
Liam Gordon; Darnell Fisher; Joe Shaughnessy; Brian Easton;
</AwayLineupDefense>
<AwayLineupMidfield>
Liam Craig; David Wotherspoon; Daniel Swanson; Thomas Scobbie;
</AwayLineupMidfield>
<AwayLineupForward>Christopher Kane; Graham Cummins;</AwayLineupForward>
<AwayLineupSubstitutes>
Zander Clark; Eoghan McCawl; Michael Doyle; Steven Anderson; Greg Hurst; Steven MacLean; Scott Brown;
</AwayLineupSubstitutes>
<AwayYellowCards>4</AwayYellowCards>
<AwayRedCards>0</AwayRedCards>
<AwayTeamFormation>4-4-2</AwayTeamFormation>
<HomeTeamYellowCardDetails>
60': Juan Delgado;58': Arnaud Sutchuin Djoum;28': Juwon Oshaniwa;12': Alim Oezturk;8': John Souttar;
</HomeTeamYellowCardDetails>
<AwayTeamYellowCardDetails>
74': Thomas Scobbie;66': Liam Craig;59': Darnell Fisher;56': Christopher Kane;
</AwayTeamYellowCardDetails>
<HomeTeamRedCardDetails>57': Abiola Dauda;</HomeTeamRedCardDetails>
<AwayTeamRedCardDetails/>
<HomeLineupCoach>Robbie Neilson;</HomeLineupCoach>
<AwayLineupCoach>Tommy Wright;</AwayLineupCoach>
<HasBeenRescheduled>false</HasBeenRescheduled>
</Match>
The code
<?php
$file_name = basename(__FILE__,'.php');
include("conf.php");
include("XMLSoccer.php");
$years = 1; ///<-------NUMBER OF YEARS TO GO BACK
$leagueretrive = 3; ///<--------THE LEAGUE ID TO RETRIEVE DATA FOR
$date1 = date('y', strtotime("-$years years"));
$date2 = date("y");
//CHECKING IF TABLE EXIST IF NOT CREATE NEW
$table = $file_name;
$query = "SELECT ID FROM " . $table;
$resultat = mysqli_query($conn,$query);
if(empty($resultat)) {
echo "<p>" . $table . " table does not exist</p>";
$query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS $file_name (
Id int NOT NULL PRIMARY KEY,
FixtureMatch_Id varchar(255) NOT NULL,
Date varchar(255) NOT NULL,
HasBeenRescheduled varchar(255) NOT NULL,
Round varchar(255) NOT NULL,
Spectators varchar(255) NOT NULL,
League varchar(255) NOT NULL,
HomeTeam varchar(255) NOT NULL,
HomeTeam_Id varchar(255) NOT NULL,
HomeCorners varchar(255) NOT NULL,
HomeGoals varchar(255) NOT NULL,
HalfTimeHomeGoals varchar(255) NOT NULL,
HomeShots varchar(255) NOT NULL,
HomeShotsOnTarget varchar(255) NOT NULL,
HomeFouls varchar(255) NOT NULL,
HomeGoalDetails varchar(500) NOT NULL,
HomeLineupGoalkeeper varchar(255) NOT NULL,
HomeLineupDefense varchar(255) NOT NULL,
HomeLineupMidfield varchar(255) NOT NULL,
HomeLineupForward varchar(255) NOT NULL,
HomeLineupSubstitutes varchar(255) NOT NULL,
HomeYellowCards varchar(255) NOT NULL,
HomeRedCards varchar(255) NOT NULL,
HomeSubDetails varchar(255) NOT NULL,
HomeTeamFormation varchar(255) NOT NULL,
HomeTeamYellowCardDetails varchar(255) NOT NULL,
HomeTeamRedCardDetails varchar(255) NOT NULL,
HomeLineupCoach varchar(255) NOT NULL,
AwayTeam varchar(255) NOT NULL,
AwayTeam_Id varchar(255) NOT NULL,
AwayCorners varchar(255) NOT NULL,
AwayGoals varchar(255) NOT NULL,
HalfTimeAwayGoals varchar(255) NOT NULL,
AwayShots varchar(255) NOT NULL,
AwayShotsOnTarget varchar(255) NOT NULL,
AwayFouls varchar(255) NOT NULL,
AwayGoalDetails varchar(255) NOT NULL,
AwayLineupGoalkeeper varchar(255) NOT NULL,
AwayLineupDefense varchar(255) NOT NULL,
AwayLineupMidfield varchar(255) NOT NULL,
AwayLineupForward varchar(255) NOT NULL,
AwayLineupSubstitutes varchar(255) NOT NULL,
AwayYellowCards varchar(255) NOT NULL,
AwayRedCards varchar(255) NOT NULL,
AwaySubDetails varchar(255) NOT NULL,
AwayTeamFormation varchar(255) NOT NULL,
AwayTeamYellowCardDetails varchar(255) NOT NULL,
AwayTeamRedCardDetails varchar(255) NOT NULL,
AwayLineupCoach varchar(255) NOT NULL
)CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
echo "<p>" . $table . "table exists</p>";
} // else
/////GETING THE DATA FROM SERVICE
try {
$soccer = new XMLSoccer($api_key);
$soccer->setServiceUrl("http://www.xmlsoccer.com/FootballDataDemo.asmx");
$results = $soccer->GetHistoricMatchesByLeagueAndSeason(array("league"=>$leagueretrive,"seasonDateString"=>"$date1$date2"));
print_r($results);
} catch (XMLSoccerException $e) {
echo "XMLSoccerException: " . $e->getMessage();
}
foreach ($results->Match as $team) {
$id = $team->Id;
$fixtureid = $team->FixtureMatch_Id;
$date = $team->Date;
$hasBeenRescheduled = $team->HasBeenRescheduled;
$round = $team->Round;
$spectators = $team->Spectators;
$league = $team->League;
$hometeam = $team->HomeTeam;
$hometeamid = $team->HomeTeam_Id;
$homecorners = $team->HomeCorners;
$homegoals = $team->HomeGoals;
$halftimehomegoals = $team->HalfTimeHomeGoals;
$homeshots = $team->HomeShots;
$homeshotsontarget = $team->HomeShotsOnTarget;
$homefouls = $team->HomeFouls;
$homeGoalDetails = $team->HomeGoalDetails;
$homeLineupGoalkeeper = $team->HomeLineupGoalkeeper;
$homeLineupDefense = $team->HomeLineupDefense;
$homeLineupMidfield = $team->HomeLineupMidfield;
$homeLineupForward = $team->HomeLineupForward;
$homeLineupSubstitutes = $team->HomeLineupSubstitutes;
$homeYellowCards = $team->HomeYellowCards;
$homeRedCards = $team->HomeRedCards;
$homeSubDetails = $team->HomeSubDetails;
$homeTeamFormation = $team->HomeTeamFormation;
$homeTeamYellowCardDetails = $team->HomeTeamYellowCardDetails;
$homeTeamRedCardDetails = $team->HomeTeamRedCardDetails;
$homeLineupCoach = $team->HomeLineupCoach;
$awayTeam = $team->AwayTeam;
$awayTeam_Id = $team->AwayTeam_Id;
$awayCorners = $team->AwayCorners;
$awayGoals = $team->AwayGoals;
$halfTimeAwayGoals = $team->HalfTimeAwayGoals;
$awayShots = $team->AwayShots;
$awayShotsOnTarget = $team->AwayShotsOnTarget;
$awayFouls = $team->AwayFouls;
$awayGoalDetails = $team->AwayGoalDetails;
$awayLineupGoalkeeper = $team->AwayLineupGoalkeeper;
$awayLineupDefense = $team->AwayLineupDefense;
$awayLineupMidfield = $team->AwayLineupMidfield;
$awayLineupForward = $team->AwayLineupForward;
$awayLineupSubstitutes = $team->AwayLineupSubstitutes;
$awayYellowCards = $team->AwayYellowCards;
$awayRedCards = $team->AwayRedCards;
$awaySubDetails = $team->AwaySubDetails;
$awayTeamFormation = $team->AwayTeamFormation;
$awayTeamYellowCardDetails = $team->AwayTeamYellowCardDetails;
$awayTeamRedCardDetails = $team->AwayTeamRedCardDetails;
$awayLineupCoach = $team->AwayLineupCoach;
///INSERTING DATA INTO THE TABLE
$sql = "INSERT INTO $file_name (Id, FixtureMatch_Id, Date, HasBeenRescheduled, Round, Spectators, League, HomeTeam, HomeTeam_Id, HomeCorners, HomeGoals, HalfTimeHomeGoals, HomeShots, HomeShotsOnTarget,
HomeFouls, HomeGoalDetails)
VALUES ('$id', '$fixtureid', '$date', '$hasBeenRescheduled', '$round', '$spectators', '$league', '$hometeam', '$hometeamid', '$homecorners', '$homegoals', '$halfTimeAwayGoals', '$homeshots', '$homeshotsontarget',
'$homefouls', '$homeGoalDetails')
on duplicate key update Id='$id', FixtureMatch_Id='$fixtureid', Date='$date', HasBeenRescheduled='$hasBeenRescheduled', Round='$round', Spectators='$spectators', League='$league', HomeTeam='$hometeam', HomeTeam_Id='$hometeamid',
HomeCorners='$homecorners', HomeGoals='$homegoals', HalfTimeHomeGoals='$halftimehomegoals', HomeShots='$homeshots', HomeShotsOnTarget='$homeshotsontarget', HomeFouls='$homefouls', HomeGoalDetails='$homeGoalDetails'";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
$conn->close();
?>
l try this to add
foreach ($results->HomeGoalDetails as $team) { $homeGoalDetails = $team->HomeGoalDetails; } $sql2 = "INSERT INTO $file_name (HomeGoalDetails) VALUES ('$homeGoalDetails') on duplicate key update HomeGoalDetails='$homeGoalDetails'";
It doesn’t work HELP!!!