I'm using the latest version of codeigniter MVC framework and I'm building a database program that generates reports from our medical database, we have a database that records all bookings made in each of the countries we provide services in, I have an array of all our current active countries populated from a table that looks like so:
Countries array = Array ( [0] => Botswana [1] => Ghana [2] => India [3] => Ireland [4] => Kenya [5] => Middle East [6] => South Africa [7] => Spain [8] => Tanzania [9] => Uganda [10] => UK )
From this array I want to pull all the data from each table and join it together to produce one table that I can output in my view, doing so for each individual table is easy and is done via a switch statement that selects the relevant table based on the users $_POST['country'] input on my search form, however I need an option to select all countries that will add each country table (book_<COUNTRY>) to the output array. using SQL it would be a simple union as each bookings table is identicle, ive tried using the JOIN method but im getting no luck, heres the code from my model:
$database = $this->load->database('bookings_dev', TRUE);
$database->select($new_country_cols);
foreach ($country_tbls_array as $country) {
$database->from($country);
}
Any pointers in the right direction? Cheers
