I have a PHP program turned Wordpress plugin that generates XML from a database table. I am very close to completion but have stumbled upon a problem. Previously on this site I have asked stupid questions about SQL and retuning multiple rows as a single row. I realize this was a poor approach.
I'm generating the XML by doing the following:
- Writing an SQL statement to get my data
- Use a foreach loop to iterate over the data
- Use the column name and value like field => value to create nodes and data to fill those nodes
- Use the PHP DOMDocument functions along with the above to create nodes
$dom = new DOMDocument(); // Declare a new DOMDocument
$root = $dom->createElement('root'); // Create parent or root node
$dom->appendChild($root); // Append the root tag to the DOM
foreach($result as $r){ // foreach loop to iterate over the data in the $result array
foreach($r as $column_name => $val) { // Loop through key value pairs
// Create node elements by using the column name // Create text nodes using the values
$node = $dom->createElement($column_name); // Create an element tag using the column name in the column array
$textContent = $dom->createTextNode($val); // Create a text node using the value in the column array
$node->appendChild($textContent); // Add the text node data into the $node variable
// add the data gathered to the property node
$property->appendChild($node);
}
// add the property tag and its elements to the root node
$root->appendChild($property);
}
// Here we purposely exclude a header so that the browser does not try to output the XML at this stage
$xmlOutput = $dom->saveXML(); // Save the XML to a variable
Now for the final part each node has an ID which relates to a property. A property can have multiple images and this is in the database as a 1 : many relation.
In order to build the image URLs I do this:
foreach($imgr as $row){
$var = $row->item_name;
$extension = ""; // Extension will be used to add the file type extension
$imgSize = '_1600x420'; // The size of the image in the URL, can be: 1600x420, 285x200, 100x80, 80x60,
if(strpos($var, 'png') !== false){ // Use strpos() to see if a particular extension is present (by looking for particular letters)
$extension = ".png";
}
if(strpos($var, 'jpg') !== false){
$extension = ".jpg";
}
if(strpos($var, 'jpeg') !== false){
$extension = ".jpeg";
}
if(strpos($var, 'gif') !== false){
$extension = ".gif";
}
// Additional check as the extension jpeg is longer so we have to slightly modify the string build
if($extension == '.jpeg'){
$ammendment = substr_replace($var, $imgSize . $extension, -5); // get $val and replace the end of the string to include the default size
} else{
$ammendment = substr_replace($var, $imgSize . $extension, -4); // get $val and replace the end of the string to include the default size
}
$var = 'http://localhost/wp-content/uploads/WPL/' . $propertyid . '/' . 'th' . $ammendment; // Build the URL
echo $var . "<br /> <br /> <br />";
This code echoes a nice list of fully built URLs. My issue is that when I insert $var into my XML file, because I am already in a loop it only outputs one.
For instance:
<property>
<id>6</id>
<area>United States</area>
<country/><city/>
<town>Kyrat Property</town>
<towninner/><postcode/>
<lot_area>123456</lot_area><living_area>19000</living_area>
<price>210000</price>
<bedrooms>8</bedrooms>
<bathrooms>4</bathrooms>
<summary/>
<latitude>21.3239718</latitude>
<longlitude>-157.87649799999997</longlitude>
<street/>
<streetno/>
<type>Apartment</type>
<property_title/>
<build_year>1928</build_year>
<add_date>2015-01-14 12:19:03</add_date>
<imageurl>http://localhost/wp-content/uploads/WPL/6/thimg_house2_1600x420.jpg</imageurl
<imageurls>img_cartoonhouse.jpg,img_index.jpg,img_house1.jpg,img_house3.jpg,img_house.png,img_house4.jpg,img_house5.jpg,img_house2.jpg</imageurls>
As you can see in imgurls, this is how the URLs start off. Anyway even though I can generate all 6 URLs in a foreach loop which look like so:
http://localhost/wp-content/uploads/WPL/6/thimg_cartoonhouse_1600x420.jpg
http://localhost/wp-content/uploads/WPL/6/thimg_index_1600x420.jpg
When i try to insert the URLs into my imageurl node, only the last URL in the loop is used even though more are being generated.
Any help would be greatly appreciated and I apologize for any previous poorly worded questions.
My XML generation section
$dom = new DOMDocument(); // Declare a new DOMDocument
$root = $dom->createElement('root'); // Create parent or root node
$dom->appendChild($root); // Append the root tag to the DOM
foreach($result as $r){ // foreach loop to iterate over the data in the $result array
$propertyid = $r->id; // As the results are returned as an object we have to use this approach
$imgs = "SELECT item_name
FROM ultrait_wpl_items, ultrait_wpl_properties
WHERE ultrait_wpl_items.parent_id = ultrait_wpl_properties.id
AND ultrait_wpl_properties.id = $propertyid";
$imgr = $wpdb->get_results($imgs);
// TESTING
$node = $textContent = null; // Create a variable to hold textContent for each node
$property = $dom->createElement('property'); // Create containing node called property
foreach($r as $column_name => $val) { // Loop through key value pairs
// The next few if statements are used to rename the columns with unclear names to something more readable
// If your database fields have human readable names then you can remove this part of the code
if($column_name == 'location1_name'){
$column_name = 'area';
}
// Rename
if($column_name == 'location3_name'){
$column_name = 'country';
}
// Rename
if($column_name == 'location4_name'){
$column_name = 'city';
}
// Rename
if($column_name == 'field_312'){
$column_name = 'town';
}
// Rename
if($column_name == 'field_42'){
$column_name = 'towninner';
}
// Rename
if($column_name == 'post_code'){
$column_name = 'postcode';
}
// Rename
if($column_name == 'field_308'){
$column_name = 'summary';
}
// Rename
if($column_name == 'name'){
$column_name = 'type';
}
// Rename
if($column_name == 'googlemap_lt'){
$column_name = 'latitude';
}
// Rename
if($column_name == 'googlemap_ln'){
$column_name = 'longlitude';
}
// Rename
if($column_name == 'item_name'){
$column_name = 'imageurl';
}
//Rename
if($column_name == 'street_no'){
$column_name = 'streetno';
}
// In this node we need to build the URL leading to the image file
if($column_name == 'imageurl'){
foreach($imgr as $row){ // Loop through the img result set
$img = $row->item_name; // Store the returned value in a variable
$extension = ""; // Extension will be used to add the file type extension
$imgSize = '_1600x420'; // The size of the image in the URL, can be: 1600x420, 285x200, 100x80, 80x60,
if(strpos($img, 'png') !== false){ // Use strpos() to see if a particular extension is present (by looking for particular letters)
$extension = ".png";
}
if(strpos($img, 'jpg') !== false){ // if jpg
$extension = ".jpg";
}
if(strpos($img, 'jpeg') !== false){ // if jpeg
$extension = ".jpeg";
}
if(strpos($img, 'gif') !== false){ // if gif
$extension = ".gif";
}
// Additional check as the extension jpeg is longer so we have to slightly modify the string build
if($extension == '.jpeg'){
$ammendment = substr_replace($img, $imgSize . $extension, -5); // get $val and replace the end of the string to include the default size
} else{
$ammendment = substr_replace($img, $imgSize . $extension, -4); // get $val and replace the end of the string to include the default size
}
$img = 'http://localhost/wp-content/uploads/WPL/' . $propertyid . '/' . 'th' . $ammendment; // Build the URL
$val = $img;
echo $val . "<br /> <br /> <br />";
$textContent = $dom->createTextNode($val); // Create a text node using the value in the column array
}
}
// Create node elements by using the column name
// Create text nodes using the values
$node = $dom->createElement($column_name); // Create an element tag using the column name in the column array
$textContent = $dom->createTextNode($val); // Create a text node using the value in the column array
$node->appendChild($textContent); // Add the text node data into the $node variable
// add the data gathered to the property node
$property->appendChild($node);
}
// add the property tag and its elements to the root node
$root->appendChild($property);
}
// Here we purposely exclude a header so that the browser does not try to output the XML at this stage
$xmlOutput = $dom->saveXML(); // Save the XML to a variable
$dom->save("/Applications/XAMPP/xamppfiles/htdocs/feed/zooplafeed.xml"); // Save the contents of the DOM to an XML file