Strange phenomenon passing array through active record class

[eluser]Dandy_andy[/eluser]
I'm using the active record class to return results for a database query. I want to exclude some results from the query using $this->db->where_not_in(); but something strange is happening that I can't explain. I have a statement in my database query which reads:-

Code:

$this->db->where_not_in('members.mem_id', $excluded_members);

Now if I manually generate an array to pass through this statement like the one below, the query works fine.

Code:

$excluded_members = array(9012, 9021, 9031);

However, I want to dynamically create the array and I'm using the following function to do so:-

Code:

public function excluded_members($mem_id) {
  
  $limit_array = 100; //limit array to 100 results. Only the most recent 100 results will be returned as exclusions
  $data = array();
  $this->db->select('matched_mem_id');
  $this->db->where('mem_id', $mem_id);
  $this->db->order_by('datetime', 'DESC');
  $this->db->limit($limit_array);
  $query = $this->db->get('search_exclusions');
  $row = $query->result_array();
  foreach($row as $value)
   {
   array_push($data, (int)$value['matched_mem_id']); //(int) to convert values to integer
   }
  return $data;

}//excluded_members

The value returned and outputted using var_dump or var_export is identical to my original hand written array yet the dynamically created array $excluded_members is causing my database query to crash. Does anyone know why? What am I missing here?

[eluser]Tim Brownlaw[/eluser]
The Best way to see what's going on is to check the SQL being generated
If there's nothing obvious there, then try running the generated SQL statement through your phpmyadmin or mysql workbench or even the ole command line and see what helpful errors they give.

So after your "get", add in a "last_query" call like so..

Code:

...
$query = $this->db->get('search_exclusions');
echo $this->db->last_query();
...

And see what light that sheds on matters.

Cheers
Tim

[eluser]Dandy_andy[/eluser]
Hi Tim,
I did that on the last portion of my query that uses the results from 'search_exclusions' and the results are just as I expected them to be. I don't think there is a problem with the SQL query. It seems to be an issue with the array and that's what I can't understand. Just to show you the difference (and there isn't any at all), if I create an array manually as per the code below:-

Code:

$excluded_members = array(9012, 9021, 9031);

The result of the last query (and the one that works fine) is:-

Code:

SELECT `members`.`screenname`, `members`.`mem_id`, `members_photos`.`pho_id`, `members_photos`.`s3_region` FROM (`members`) LEFT JOIN `members_photos` ON `members_photos`.`mem_id` = `members`.`mem_id` LEFT JOIN `members_profile_complete` ON `members_profile_complete`.`mem_id` = `members`.`mem_id` LEFT JOIN `members_profile_options` ON `members_profile_options`.`mem_id` = `members`.`mem_id` WHERE `members_profile_complete`.`sex_dob` = '1' AND `members_profile_complete`.`profile` = '1' AND `members_photos`.`main` = '1' AND `members_photos`.`approved` = '1' AND `members`.`sex` = 'male' AND `members`.`region` = 'london' AND `members_profile_options`.`height` > '4' AND `members_profile_options`.`body` = '2' AND `members_profile_options`.`smoking` = '1' AND `members_profile_options`.`income` = '4' AND `members`.`mem_id` != '9001' AND `members`.`mem_id` NOT IN (9012, 9021, 9031)

and if I use the result produced by the dynamically created array, the result is also:-

Code:

SELECT `members`.`screenname`, `members`.`mem_id`, `members_photos`.`pho_id`, `members_photos`.`s3_region` FROM (`members`) LEFT JOIN `members_photos` ON `members_photos`.`mem_id` = `members`.`mem_id` LEFT JOIN `members_profile_complete` ON `members_profile_complete`.`mem_id` = `members`.`mem_id` LEFT JOIN `members_profile_options` ON `members_profile_options`.`mem_id` = `members`.`mem_id` WHERE `members_profile_complete`.`sex_dob` = '1' AND `members_profile_complete`.`profile` = '1' AND `members_photos`.`main` = '1' AND `members_photos`.`approved` = '1' AND `members`.`sex` = 'male' AND `members`.`region` = 'london' AND `members_profile_options`.`height` > '4' AND `members_profile_options`.`body` = '2' AND `members_profile_options`.`smoking` = '1' AND `members_profile_options`.`income` = '4' AND `members`.`mem_id` != '9001' AND `members`.`mem_id` NOT IN (9012, 9021, 9031)

Both completely identical yet the last one will fail if I remove the last query function. I am totally puzzled here. If I remove the last query function which is showing the array in the NOT IN part at the end, the error I get is as per the screenshot attachment which appears to be missing the array at the end. What the hell is going on?

[eluser]Tim Brownlaw[/eluser]
So what is the actual code you are using with the "last query function" and without the "last query function".

From the error message, it appears that it's still seeing the where_not_in and there will be a reason!

Also try simplifying things by just doing a simple Select with a "where_not_in" and without a "where_not_in" and build your way back up.

It's simple to just create a test method in one of your existing controllers or make a test controller to setup and display the progress...
So create something simple - keep it... and then create another with added bits... That way you get to document what works and what doesn't.