CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
Sbudah
Sbudah

Reputation: 3

Codeigniter DB Query chaining wrong data from previous calls

This is what I have in my model. I inherited somebody else's code and trying to clean it up (can't change column names yet)

$ad_where = array('id'=>$id, 'client'=>$this->brand->client['id']);
$this->db->select('id, client, user, code, heading, subhead, `from`, `to`, openings, view_only, publish, closed')->limit(1);
$query = $this->db->get_where('adverts', $ad_where);

if( $query->num_rows() > 0 ){
    $row = $query->row_array();

    if( $row['heading'] == 'n/a' ){
        $row['heading'] = $this->job_title_heading($row['id'], $row['client']);
    }

    return $row;
    }

    return FALSE;

CodeIgniter returns an error with the following output:

Error Number: 1066

Not unique table/alias: 'adverts'

SELECT `id`, `client`, `user`, `code`, `heading`, `subhead`, `from`, `to`, `openings`, `view_only`, `publish`, `closed` FROM (`adverts`, `adverts`, `adverts`, `adverts`, `adverts`, `adverts`, `adverts`, `adverts`, `adverts`, `adverts`) INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` INNER JOIN `advert_location_links` ON `advert_location_links`.`advert`=`adverts`.`id` WHERE (truncated) LIMIT 1

The added JOIN is from a previous statement.

I have tried adding

$this->db->stop_cache();
$this->db->flush_cache();

But that doesn't help

EDIT: The previous call was not resetting the statement because it was poorly formed and although it seemed to execute, it was not done properly. It was a count_all/count_all_results() call which I changed to a SELECT statements and used num_rows() to get total results.

Upvotes: 0

Views: 397

Answers (2)

iivannov
iivannov

Reputation: 4411

Are you sure that you end the previous query with getting some results? Because if you left it hanging it will be chained with the next statements.

Try calling once $this->db->result(); before starting this query to test that there is nothing waiting.

And if i can give a rewrite your code it will be like so:

$row = $this->db
    ->select('id, client, user, code, heading, subhead, from, to, openings, view_only, publish, closed')
    ->get_where('adverts', array('id'=>$id, 'client'=>$this->brand->client['id']))
    ->limit(1)
    ->row();

if(!$row)
  return false;

if($row['heading'] == 'n/a' )
    $row['heading'] = $this->job_title_heading($row['id'], $row['client']);

return $row;

Upvotes: 1

Sam
Sam

Reputation: 2761

I'd recommend putting the query directly after you escape the strings. CodeIgniter's query builder has a lot of issues and can't work properly when there are several calls.

Upvotes: 0

Related Questions