Logo F2FInterview

Codelgnitor Interview Questions

Q   |   QA

If you JOIN two tables that share an identically named column, your result set will include only one column with that name.

The simple answer is to use the AS feature, as described for the previous COUNT(‘foo’) AS foo_count question. This works best when you are SELECTING specific columns. For example:

$this->db->select ("foo.id AS foo_id, bar.id AS bar_id, ... ");

// Results will include: foo_id, bar_id

In general terms you should strive to specify all your SELECT fields - it reduces load on the database and network, memory requirements, and (arguably) also contentions and bugs. SELECT(*) constructs are, by definition, heavier. However there are times when, for various reasons, you want to use a SELECT(*).

You can work around the problem by redefining your AS casts after the SELECT(*) - these have been shown in separate function calls to highlight what we’re doing here.

// A select("*") is the assumed default, but ONLY ABSENT any other ->select() calls

$this->db->select ("*");

// We now append the two renamed-using-AS 'id' fields
$this->db->select ("foo.id AS foo_id");
$this->db->select ("bar.id AS bar_id");

// We do the JOIN here, using whatever criteria you want
$this->db->join ("bar", "bar.thing = foo.otherthing", "LEFT");

// Initiate the actual database query
$query = $this->db->get ("foo");

// The result_array() output from this would look like:
[0] => Array
[id] => 27 // This SHOULD BE IGNORED (it's *probably* = bar.id)
[name] => Brian
[email] => brian@life.of
. . .
[foo_id] => 42 // We use and trust this field
[bar_id] => 27 // We use and trust this field

[1] => Array
. . .

To clarify:
1. The [id] field should be ignored - the contents is predictable, as it will reflect the most recent JOINed table with an id column - but using it will inspire ambiguity. We basically just discard it for the sake of coding consistency and clarity.
2. The [foo_id] and [bar_id] fields are what we rely on here.
3. You only need to do this trick - forcing the first SELECT(”*”), with subsequent AS constructs - when you do not know what specific fields you should SELECT - but this should be an exceptional, rather than your normal use case. 

With MySQL you can do a query like this:

SELECT ‘whatever’ FROM ‘tablename’ WHERE ‘field_name’ BETWEEN ‘lower_value’ AND ‘higher_value’;

There’s two obvious ways you can effect this with AR:

// First
$this->db->where('field_name >=', $lower_value);
$this->db->where('field_name <=', $higher_value); 

// Second
$this->db->where('field_name BETWEEN ' . $lower_value. ' AND ' . $higher_value);  

This is covered in the Query Helper Functions section of the CI User Guide - the function you’re looking for is:

$foo = $this->db->insert_id();  

The AR Helpers page suggests this construct will work for you:

$x = $this->db->affected_rows();

Consulting the PHP.net documentation for mysql_affected_rows() provides some cautionary words for MySQL users. Specifically: 

The AR Helpers page suggests this construct will work for you:

$x = $this->db->affected_rows();

This page also provides a caveat for MySQL users. The affected_rows() function lives in (system/database/drivers/mysql/mysql_driver.php) and is simply this:

function affected_rows()
return @mysql_affected_rows($this->conn_id);

Consulting the PHP.net documentation for mysql_affected_rows() reveals the underlying function has its own caveats. Btw, the delete hack mentioned is located at line 47 of the mysql_driver.php file (in 1.7.2), and the hack is implemented within _prep_query() in the same file. 

In order to link this F2FInterview's page as Reference on your website or Blog, click on below text area and pres (CTRL-C) to copy the code in clipboard or right click then copy the following lines after that paste into your website or Blog.

Get Reference Link To This Page: (copy below code by (CTRL-C) and paste into your website or Blog)
HTML Rendering of above code: