Ambiguous column name error in CodeIgniter query with dynamic columns and tables

Question

I have up to three arbitrary fields (say "location, crimes and food_rating") and two arbitrary table names (say "food and crimes"), which may contain these given fields.

If an attribute is in both tables I receive an "ambiguous" error, which is understandable. However, I cannot quite think how to go about producing a query that will checks each possible case, and join if possible.

I am doing this in codeigniter, and so far have written the following, which causes errors. I should also note that the arbitrary fields can also be empty.

$this->db
    ->select($data->att_one . ',' . $data->att_two . ',' . $data->att_three)
    ->from($data->db_one . ',' . $data->db_two)
    ->get()
    ->result_array();

The error I receive is as follows:

Column 'location' in field list is ambiguous (Since location is in both tables)

Answer 1

The problem is the ambiguity, it's not clear what you want to select from which tables and how you want your results to be populated.

If you want "location" from both the "food" and "crimes" tables in one result, you'll have to alias them:

$this->db
    ->select('food.location as foodlocation, crimes.location as crimeslocation')
    ->from('food, crimes')
    ->get()
    ->result_array();

Depending on what you're doing with these results, it might be easier to use a new query for each table:

$results = array();
$select = array($data->att_one, $data->att_two, $data->att_three);
$tables = array($data->db_one, $data->db_two);

foreach ($tables as $tableName)
{
    $results[$tableName] = $this->db->select($select)
        ->get($tableName)
        ->result_array();
}

It really depends on what you're doing, but hopefully you see why the database can't comprehend your query.