Save simplexmlelement object to mysql

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!!!

Well, the biggest problem with your final bit of code:

foreach ($results->HomeGoalDetails as $team) {
    $homeGoalDetails = $team->HomeGoalDetails;
}
$sql2 = "INSERT INTO $file_name (HomeGoalDetails)
VALUES ('$homeGoalDetails') 
 on duplicate key update HomeGoalDetails='$homeGoalDetails'";

is that you build (and presumably run) the query outside of the loop, so it would only ever work on the final item in the object. I don’t know whether that’s what is happening - “It doesn’t work” doesn’t give us a lot to go on. Precisely how does it “not work”?

If you add echo $homeGoalDetails; into the loop, does it display the values that you would expect? If not, then that is more of a problem than the query being in the wrong place.

It seems to me that HomeGoalDetails will have a varying number of entries, depending on how many home goals are scored, so can your database layout deal with that properly? Presumably you have a separate table for that information, linked to the main table by team-id? The same would apply to other fields such as HomeSubDetails and AwaySubDetails, there isn’t going to be a good way to keep all those in the same table.

Those fields seem to also be XML objects within the main one, so you’ll need to extract the data correctly as you do for the main one. So, first crack that, then you can figure out where they should be stored.

Thank you for reply i tried with this. bdw Echo gives to me the exact data.

<?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,
        HomeGoalDetails varchar(800) 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;
    $homeGoalDetails = $team->HomeGoalDetails;

///INSERTING DATA INTO THE TABLE
    $sql = "INSERT INTO $file_name (HomeGoalDetails)
VALUES ('$homeGoalDetails')
on duplicate key update HomeGoalDetails='$homeGoalDetails'";
}
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
$conn->close();
?>

I get this in response

Error: INSERT INTO testing2 (HomeGoalDetails)
VALUES (‘35’: Stefan Johansen;4’: penalty Leigh Griffiths;‘)
on duplicate key update HomeGoalDetails=‘35’: Stefan Johansen;4’: penalty Leigh Griffiths;‘
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘: Stefan Johansen;4’: penalty Leigh Griffiths;’)
on duplicate key update HomeGo’ at line 2
Process finished with exit code 0

I would say the quotes are causing the trouble there - you have a string value that contains single quotes, but don’t seem to be doing anything to escape them so as to not confuse MySQL.

I tried to replace quotes but still i get an error

<?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,
        HGoaldetails varchar(800) 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;
    $homeGoalDetails = $team->HomeGoalDetails;
    $remove[] = "'";
    $remove[] = '+';
    $remove[] = ";"; // just as another example

    $replace[] = "";
    $replace[] = "";
    $replace[] = '|'; // just as another example

    $string = str_replace($remove, $replace, $homeGoalDetails);
///INSERTING DATA INTO THE TABLE
    $sql = "INSERT INTO $file_name (HGoaldetails)
VALUES ('$string')
on duplicate key update HGoaldetails='$string'";
}
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
$conn->close();
?>

result

Error: INSERT INTO testing2 (HGoaldetails)
VALUES (‘35: Stefan Johansen|4: penalty Leigh Griffiths|’)
on duplicate key update HGoaldetails=‘35: Stefan Johansen|4: penalty Leigh Griffiths|’
Table ‘database.testing2’ doesn’t exist
Process finished with exit code 0

Did you read the error message where it says your table does not exist? Does it exist or not?

Thank you very much for the help you give to me, I add mysqli_real_escape_string and it works, all the best it was a pleasure.

Excellent, glad it’s now working. I still think you’d be better redesigning the database and splitting these multiple-part fields into their constituents, as it will make it much easier to use them in searches later on. But if it works for now that’s great.

For your inserts it will be more efficient if you use a single prepare statement and then just bind/execute each row to be inserted. That will also do away with the need to escape the data as the code will then be completely separate.

I TRIED THIS CODE BUT I GET AN ERROR

<?php
include("conf.php");
$query = mysqli_query($conn,"SELECT HomeTeam, COUNT(HomeGoals) FROM GetHistoricMatchesByLeagueAndSeason GROUP BY HomeTeam ORDER BY HomeTeam ASC");
//Create the `mysqli_connection` and assign to `$conn` variable.
$result = mysqli_query($conn, $query);
if($result->num_rows>0)
{
    while($row = mysqli_fetch_assoc($result))
    {
        echo "<br>Team=" . $row['HomeTeam'];
        echo "<br>SUM=" . $row['HomeGoals'];
    }
}
?>

PHP Warning: mysqli_query() expects parameter 2 to be string, object given in C:\TEST.php on line 5

PHP Notice: Trying to get property of non-object in C:\TEST.php on line 6
Warning: mysqli_query() expects parameter 2 to be string, object given in C:\TEST.php on line 5

Notice: Trying to get property of non-object in C:\TEST.php on line 6

Process finished with exit code 0

You have run the query and passed the value returned into a second query in the spot where the string containing the query goes. Running a query doesn’t return the string that was being run by that query.

Exactly, you need to do something like

$query = "SELECT HomeTeam, COUNT(HomeGoals) FROM GetHistoricMatchesByLeagueAndSeason GROUP BY HomeTeam ORDER BY HomeTeam ASC";
//Create the `mysqli_connection` and assign to `$conn` variable.
$result = mysqli_query($conn, $query);
+------------+------------+
|   Team     |    Goal    |
+------------+------------+
| Team1      |      2     |
+------------+------------+
| Team3      |      5     |
+------------+------------+
| Team1      |      2     |
+------------+------------+
| Team2      |      3     |
+------------+------------+
| Team2      |      2     |
+------------+------------+
| Team3      |      2     |
+------------+------------+
| Team1      |      2     |
+------------+------------+

I need the following results

Number of times the team is on column
Team1 = 3 times
Team2 = 2 times
Team3 = 2 times

And the total number of goals the time has

Team1 = 6 goals
Team2 = 5 goals
Team3 = 7 goals

And inserting it in new table

You’re looking for aggregate functions: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Hi i made this code but when i run there is no data in the table

<?php
include("conf.php");
$file_name = basename(__FILE__,'.php');
$table = $file_name;
$query = "SELECT HomeTeam FROM " . $table;
$result = 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 (
        HomeTeam int NOT NULL PRIMARY KEY,
        HomeGoals varchar(255) NOT NULL
    )CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
    echo "<p>" . $table . "table exists</p>";
} // else

$sql = "INSERT INTO $file_name (HomeTeam, HomeGoals) VALUES (
SELECT HomeTeam COUNT(HomeGoals) As HomeGoals FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC
)";
?>

Missing comma in the SELECT portion of the final query after HomeTeam?

DOESENT WORK

SELECT HomeTeam, COUNT(HomeGoals) As HomeGoals FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC

By “doesn’t work”, do you mean that it runs but doesn’t give the result you want, or it doesn’t run? Does it give you an error message? What happens if you run it from phpMyAdmin or whatever equivalent you use? Can you have a column alias with the same name as an actual column?

im using php storm it runs and dot give any error but in the database table there are not columns created

No, I mean if you get that final query (the one you put in post #27 that doesn’t have the INSERT portion) and run it directly in MySQL, does it display the results you want? If it does not, then that needs to be fixed before you start worrying about why it doesn’t insert it into the table correctly. Of course the query in post #27 won’t create rows in the database because you’ve removed the INSERT part to test it.

Also, there’s a typo here:

$result = mysqli_query($conn, $query);
if(empty($resultat)) {

$resultat isn’t defined anywhere I can see, so presumably it always tries to create the table.

Also, further up in this thread, you define HomeTeam like this:

HomeTeam varchar(255) NOT NULL,

to contain data that looks like this, assuming you’re being logical with column names:

<HomeTeam>Hearts</HomeTeam>

so it’s not going to be very happy when you try to stick data like that in your new table where it is defined as

HomeTeam int NOT NULL PRIMARY KEY,