$this->db->where_in

[eluser]mrliam69[/eluser]
I am trying to get my head round this problem.

Basically I want to populate the array(5,6) with sql results.

Code:

$this->db->where_in('VehServicingDealer',array(5,6));

Code:

$ValidDealersForUser =     "SELECT DealerLogNumber
                                                           FROM `tblDealerInfo`
                                                           WHERE `DealerCode` = (
                                                           SELECT `UserDealerCode`
                                                           FROM `fa_user_profile`
                                                          WHERE id = ? )";
                        
$VDFUquery = $this->db->query($ValidDealersForUser,$this->db_session->userdata('id'));

        
    foreach ($VDFUquery->result_array() as $row)
{
   echo $row['DealerLogNumber'];

}
        
}

which only returns 56

not 5,6

How can I populate the array field ?

Code:

$this->db->where_in('VehServicingDealer',array(5,6));

Hope this makes sense :red:

[eluser]bastones[/eluser]
Where are you placing this where_in active record and have you tried putting the numbers in single-quotations?

[eluser]mrliam69[/eluser]
Sorry I should have been a bit clearer.

Its in the controller but on the way to the train station I had a thought of using a foreach loop to build up the where and statements instaead of using where in.

unless someone can think of a better way.

[eluser]barbazul[/eluser]
I don't see how are you connecting the where_in call with the rest of the query.

When calling query() with two parameters, you are executing that query regardless of any previously set conditions in the active record.
That means the call to where_in is completely ignored.

You either build your own queries or you let AR build them for you... bt you can't have a bit of both.

Try this:

Code:

$this->db->select('DealerLogNumber');
$this->db->from('tblDealerInfo');
$this->db->where('`DealerCode` = (SELECT `UserDealerCode` FROM `fa_user_profile` WHERE id = '. $this->db_session->userdata('id').')');
$this->db->where_in('VehServicingDealer',array(5,6));
$VDFUquery = $this->db->get();

        
foreach ($VDFUquery->result_array() as $row)
{
   echo $row['DealerLogNumber'];
}

[eluser]mrliam69[/eluser]
I have created a for each loop to create my string.

Code:

$ValidDealersForUser =     "SELECT DealerLogNumber
                        FROM `tblDealerInfo`
                        WHERE `DealerCode` = (
                        SELECT `UserDealerCode`
                        FROM `fa_user_profile`
                        WHERE id = ? )";
                        
        $VDFUquery = $this->db->query($ValidDealersForUser,$this->db_session->userdata('id'));
    
        $MAX = $VDFUquery->num_rows();    
        $ValidDealers = "";
        $i=1;
        foreach ($VDFUquery->result_array() as $row)
{
           $ValidDealers = $ValidDealers.$row['DealerLogNumber'];
        if ($i==$MAX)
              $ValidDealers=$ValidDealers;
        else
              $ValidDealers=$ValidDealers.",";
          $i++;

}

this creates a string called $ValidDealers which in my test echo returns 5,6

so db part now becomes

Code:

$this->db->where_in('VehServicingDealer',array(($ValidDealers)));

using the $ValidDealers in the array

but the sql generated is

SELECT * FROM (`tblVehicles`) WHERE `VehLog` = '3390' AND `VehServicingDealer` IN ('5,6')

which is incorrect as the end bit should be IN (5,6) without the apostrophes?

How can I strip the apostrophes ?