I'm iterating through a xml doc with SimpleXML. I have an array ($ids) with id's and I'm checking whether there's a match or not in the XML (Worksheet/Table/Row/Cell/Data). If it's a match I want to be able to get to the data from the following two siblings, but I can't figure out how.
from the php:
// $ids <---- array('8', '53', '38')
foreach ($thePositions->Worksheet->Table->Row as $row) {
if($row->Cell->Data == true) {
for ($i = 0; $i < count($ids); $i++) {
foreach($row->Cell->Data as $data) {
if ($data == $ids[$i]) {
echo 'match!';
/*
Tried $siblings = $data->xpath('preceding-sibling::* | following-sibling::*');
but doesn't seem to work in this case.
*/
}
}
}
}
}
the xml:
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<LastAuthor>Herpa Derp </LastAuthor>
<Created>2012-09-25T13:44:01Z</Created>
<LastSaved>2012-09-25T13:48:24Z</LastSaved>
<Version>14.0</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>14060</WindowHeight>
<WindowWidth>25040</WindowWidth>
<WindowTopX>25540</WindowTopX>
<WindowTopY>4100</WindowTopY>
<Date1904/>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Font ss:FontName="Courier" ss:Color="#000000"/>
</Style>
</Styles>
<Worksheet ss:Name="Workbook1.csv">
<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="79" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">
<Column ss:Index="2" ss:AutoFitWidth="0" ss:Width="43"/>
<Column ss:AutoFitWidth="0" ss:Width="113"/>
<Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="220"/>
<Row ss:Index="6">
<Cell ss:Index="3" ss:StyleID="s62"/>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s62"/>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s62"/>
</Row>
<Row>
<Cell ss:Index="2"><Data ss:Type="String">id</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">latitude</Data></Cell>
<Cell><Data ss:Type="String">longitude</Data></Cell>
</Row>
<Row>
<Cell ss:Index="2"><Data ss:Type="Number">8</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="Number">57.4999</Data></Cell> // to be saved to $latutude
<Cell><Data ss:Type="Number">15.8280</Data></Cell> // to be saved to $longitude
</Row>
<Row>
<Cell ss:Index="2"><Data ss:Type="Number">38</Data></Cell>
<Cell><Data ss:Type="Number">56.5659</Data></Cell>
<Cell><Data ss:Type="Number">16.1380</Data></Cell>
</Row>
The reason asking for siblings isn't working is that the <Data> elements aren't siblings; they're more like cousins - children of adjacent <Cell> elements.
For the same reason, you shouldn't be using foreach($row->Cell->Data as $data), as this is equivalent to foreach($row->Cell[0]->Data as $data), i.e. look at all <Data> children of the first <Cell> node. Since there will only ever be one <Data> element in a <Cell>, you might as well just write $data = $row->Cell[0]->Data - which in this case is fine, because the values you're looking for are at the beginning of rows.
What you actually need to do is loop over the <Cell>s: foreach($row->Cell as $cell) { $data = $cell->Data; /* ... */ }
You then have a couple of options for finding the adjacent cells, including XPath. A more "PHP-ish" way would be to use array indexes (siblings are indexed numerically in SimpleXML loop/array access):
foreach($row->Cell as $cell_index => $cell)
{
$data = $cell->Data;
if ($data == $ids[$i])
{
// Tip: always cast SimpleXML objects to string when you're done with their magic XMLiness
$latitudes[$i] = (string)$row->Cell[ $cell_index + 1 ]->Data;
$longitudes[$i] = (string)$row->Cell[ $cell_index + 2 ]->Data;
}
}
Alternatively, you could rely on your IDs always being in the first column, and the lat and long in the next two (this is a spreadsheet, after all!) and avoid the inner loop altogether:
if ( $row->Cell[0]->Data == $ids[$i] )
{
$latitudes[$i] = (string)$row->Cell[1]->Data;
$longitudes[$i] = (string)$row->Cell[2]->Data;
}
It looks like in case of this XML, cells are always in same order so it can be done as follows:
$ids = array('8', '53', '38');
foreach ($xml->Worksheet->Table->Row as $row) {
$children = $row->children();
if (count($children) == 3 && in_array(((string) $children[0]->Data), $ids)) {
echo 'lat: ' . $children[1]->Data . ' lng: ' . $children[2]->Data . "
";
}
}
You can do it entirely in XPath, without any loops, like:
//Row[Cell/Data[. = '8' or . = '53' or . = '38']]/following-sibling::*[position() <= 2]
That search all rows with the id in any data element and then takes the next two siblings.
Or
//Row[Cell[1]/Data[. = '8' or . = '53' or . = '38']]/following-sibling::*[position() <= 2]
if it is sure that the id is always in the first cell. (which also prevents errors due to an id being the same as a longitude/langitude)
Or
//Row[Cell[@ss:Index = "2"]/Data[. = '8' or . = '53' or . = '38']]/following-sibling::*[position() <= 2]
if the id is in the cell with index 2.
But in all cases, you need to initialize the namespaces correctly
An alternative approach if you have lots of IDs to match is to create a "hash" of all the rows based on their ID, and then look into that hash rather than looping through searching for matches.
// Initialise an empty array to use as the hash
$rows_by_id = array();
// Loop over all the rows in the spreadsheet
foreach ($thePositions->Worksheet->Table->Row as $row) {
// Skip rows with less than 3 cells
if ( count($row->Cell) < 3 ) {
continue;
}
// Take the ID from the first cell on the row
$id = (string)$row->Cell[0]->Data;
// Add this row to the hash, identified by it's ID
$rows_by_id[$id] = array(
'latitude' => (string)$row->Cell[1]->Data,
'longitude' => (string)$row->Cell[2]->Data
);
// Or if your IDs are not unique, and you want all matches:
// $rows_by_id[$id][] = array( ... )
}
foreach ( $ids as $required_id ) {
// Retrieve the results from the hash
if ( isset($rows_by_id[$required_id]) ) {
$matched_row = $rows_by_id[$required_id];
echo "ID $required_id has latitude {$matched_row['latitude']} and longitude {$matched_row['longitude']}.
";
}
else {
echo "ID $required_id was not matched. :(
";
}
// If you have non-unique matches, you'll need something like this:
// $all_matched_rows = $rows_by_id[$required_id]; ... foreach ( $all_matched_rows as $matched_row )
}