0

I'm building a script which outputs an xml file but I can't generate all the data from a single query as the description can come from a variety of sources. So far I have:

// Select the products
$query = $this->db->select('id,sku,name,image')
        ->from('products')
        ->get();



// !!! Here's where I need help - I need to loop through the results and run
// another query based on the ID which will pull a description from
// a number of possible sources and then add the resulting description into
// the main $query object so it can still use xml_from_result()




// Load the Database Utilities
$this->load->dbutil();

// Configure the xml structure
$config = array (
    'root'    => 'root',
    'element' => 'element', 
    'newline' => "\n", 
    'tab'    => "\t"
  );

// Output the xml doc
header('Content-type: text/xml');
echo $this->dbutil->xml_from_result($query, $config);

I can get the data I need by first executing a $query->result() but then I can no longer use $query with xml_from_result(). Any help would be greatly appreciated.

RayZor
  • 665
  • 1
  • 13
  • 25
  • You are looking for a way to query two database tables at once. This is called a JOIN. And I highly suggest you to do it this way, otherwise you would need to actually create your own result object which is possible with `xml_from_result` but you need to understand some more internals for that. The concept of joining two database tables is more universal. – hakre Mar 20 '15 at 15:23
  • I know how to use joins but the join I'd need to use would vary from product to product as there are three possible sources for the description. – RayZor Mar 20 '15 at 15:25
  • that should be no blocker with SQL. Otherwise the question I closed against also shows a model class. You can as well place the logic therein and use the model class with `xml_from_result`. – hakre Mar 20 '15 at 15:26
  • You might be interested as well in [MySQL conditional table name in JOIN](http://stackoverflow.com/q/10331807/367456) – hakre Mar 20 '15 at 15:27
  • Thanks but I'm afraid none of that helps me - the conditions for selecting which piece of data to use are far too complex to be expressed (with my skill level) as an SQL query for example: are the left 2 characters of the sku alphanumeric (if so: does 3rd party data exist/ has 3rd party data been locked and manually modified) if not (does 3rd party data from the 2nd database exists, etc, etc) I'm going to have to look into creating the result object myself - will research before asking again. – RayZor Mar 20 '15 at 15:35
  • 1
    Sounds like you need to encapsulate all that into the model. Just take care it has the row fetch method and provides the list of fields. See the [source code of `xml_from_result` for details](http://cxzcxz.com/xref/codeigniter-1.7.1/system/database/DB_utility.php.source.html#l210). – hakre Mar 20 '15 at 15:54
  • I have modified the source code for xml_from_result to create a new helper function xml_from_array - this lets me use the model and PHP logic as needed to modify the results and then dumps it out as XML - thanks =) – RayZor Mar 20 '15 at 16:12
  • 1
    well you don't need to. You only need to pass in an object into that function that has two methods. One of those returns the array you created a new function for (was in there already) and the other is for the field names. – hakre Mar 20 '15 at 16:20

0 Answers0