How to produce error msg on ambiguous column names?

Question

I'm using CodeIgniter a lot lately, with array methods for querying data: usually result_array() or row_array() DB methods. I noticed a mistake that sometimes happens without error notices (old code - not mine - I'm just the bug fixer). It's the typical ambiguous column name issue that has been posted many times here in StackOverflow.

For example: PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?

With CodeIgniter, there's no ambiguous field error message. The array is populated as usual with the field names; ambiguous or not. Is there anyway to prevent this from within CodeIgnitier by displaying or logging an error message?

Does anyone have any ideas on how to log an error message (using CI log_message() perhaps) with PHP when ambiguous fields issues arise?

Answer 1

This is possible. The reason that CI doesn't throw up SQL errors about ambiguous column names is because it appends the name of the table when selecting so for example

SELECT `table`.`name`, `table2`.`name` FROM `table`, `table2` ...

The reason you then don't get to see these columns is because the array key generated by mysqli_fetch_assoc() or whichever driver you're using is name for both and obviously gets overwritten. The following answer is suitable for those using CI2.x.

Extend the Database driver result class in a similar fashion used in my answer to CodeIgniter: SQL Audit of all $this->db->query() method calls?. Once you've extended the database driver result class you have two options. You can throw an error as you requested as follows:

/**
 * Result - associative array
 *
 * Returns the result set as an array
 *
 * @access  private
 * @return  array
 */
function _fetch_assoc()
{
    if (!($row = mysql_fetch_array($this->result_id)))
    {
        return null;
    }

    $return = array();
    $row_count = mysql_num_fields($this->result_id);

    for($i = 0; $i < $row_count; $i++)
    {
        $field = mysql_field_name($this->result_id, $i);

        if( array_key_exists( $field, $return ) ) {
            log_message('Error message about ambiguous columns here');
        } else {
            $return[$field] = $row[$i];
        }
    }

    return $return;

    //Standard CI implementation
    //return mysql_fetch_assoc($this->result_id);
}

Or alternatively you can further manipulate the output of the function to simulate the query behavior by prepending tablename_ to the column name resulting in an array like

SELECT * FROM `users` LEFT JOIN `companies` ON `users`.`id` = `companies`.`id`
Array (
    [users_id] => 1
    [users_name] => User1
    [companies_id] => 1
    [companies_name] => basdasd
    [companies_share_price] => 10.00
)

To achieve this you can simply modify the for loop in the above function to the following:

for($i = 0; $i < $row_count; $i++)
{
    $table = mysql_field_table($this->result_id, $i);
    $field = mysql_field_name($this->result_id, $i);
    $return["$table_$field"] = $row[$i];
}