Xpath to parse xml and input in mysql

Question

I'm trying to use xpath in conjunction with DOMDocument to try and parse my xml and insert into a table.

All my variables are inserting correctly other than $halftimescore - why is this?

Here is my code:

<?php

  define('INCLUDE_CHECK',true);
  require 'db.class.php';

  $dom = new DOMDocument();
  $dom ->load('main.xml');

  $xpath = new DOMXPath($dom);
  $queryResult = $xpath->query('//live/Match/Results/Result[@name="HT"]');
  foreach($queryResult as $resulty) {
    $halftimescore=$resulty->getAttribute("value");
  }      


  $Match = $dom->getElementsByTagName("Match"); 
  foreach ($Match as $match) {

    $matchid = $match->getAttribute("id");
    $home = $match->getElementsByTagName("Home");
    $hometeam = $home->item(0)->getAttribute("name");
    $homeid = $home->item(0)->getAttribute("id");
    $away = $match->getElementsByTagName("Away");
    $awayid = $away->item(0)->getAttribute("id");
    $awayteam = $away->item(0)->getAttribute("name");

    $leaguename = $match->getElementsByTagName("league");
    $league = $leaguename->item(0)->nodeValue;
    $leagueid = $leaguename->item(0)->getAttribute("id");


    foreach ($match->getElementsByTagName('Result') as $result) {
      $resulttype = $result->getAttribute("name");
      $score = $result->getAttribute("value");
      $scoreid = $result->getAttribute("value");
    }

    mysql_query("
      INSERT INTO blabla
        (home_team, match_id, ht_score, away_team)
      VALUES
        ('".$hometeam."', '".$matchid."', '".$halftimescore."', '".$awayteam."')
    ");

  }

Answer 1

Because you populated $halftimescore outside the main loop, in a loop of its own, it will only have one value (the last value) because each iteration overwrites the previous.

What you need to do instead is run the XPath query within the main loop, with a base node of the current node, like this:

  // ...

  $xpath = new DOMXPath($dom);
  /*
  Remove these lines from here...
  $queryResult = $xpath->query('//live/Match/Results/Result[@name="HT"]');
  foreach($queryResult as $resulty) {
    $halftimescore=$resulty->getAttribute("value");
  }
  */


  $Match = $dom->getElementsByTagName("Match"); 
  foreach ($Match as $match) {

    // and do the query here instead:
    $result = $xpath->query('./Results/Result[@name="HT"]', $match);
    if ($result->length < 1) {
      // handle this error - the node was not found
    }
    $halftimescore = $result->item(0)->getAttribute("value");

  // ...