Welcome Guest, Not a member yet? Register   Sign In
database query combine possibility
#1

(This post was last modified: 07-16-2017, 12:20 PM by eagle00789. Edit Reason: typo fix )

i currently have to following 2 query's:

Code:
SELECT `PersonelID` FROM `vacations` WHERE ( `FromDate` <= '2017-07-10' OR ( `FromDate` BETWEEN '2017-07-10' AND '2017-07-16' ) ) AND ( `ToDate` >= '2017-07-16' OR ( `ToDate` BETWEEN '2017-07-10' AND '2017-07-16' ) )
from which i use the result in the following query (the NOT IN('3') part)

Code:
SELECT `PersonelID`, `FirstName`, `LastName` FROM `personel` WHERE `PersonelID` NOT IN('3') AND `Active` = 1 ORDER BY `FirstName` ASC
but i found that for speed, i can combine these 2 query's as 1 single query like below which works like a charm in phpmyadmin

Code:
SELECT `PersonelID`, `FirstName`, `LastName` FROM `personel` WHERE `PersonelID` NOT IN(SELECT `PersonelID` FROM `vacations` WHERE ( `FromDate` <= '2017-07-01' OR ( `FromDate` BETWEEN '2017-07-01' AND '2017-07-02' ) ) AND ( `ToDate` >= '2017-07-02' OR ( `ToDate` BETWEEN '2017-07-01' AND '2017-07-02' ) )) AND `Active` = 1 ORDER BY `FirstName` ASC
for these 2 seperate query's i have the following code to use the result from the first in the second query:

PHP Code:
     $this->db->select('PersonelID')->from('vacations');
 
       $this->db->group_start();
 
       $this->db->where('FromDate <='$startDate);
 
       $this->db->or_group_start();
 
       $this->db->where('FromDate BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'');
 
       $this->db->group_end();
 
       $this->db->group_end();
 
       $this->db->group_start();
 
       $this->db->where('ToDate >='$endDate);
 
       $this->db->or_group_start();
 
       $this->db->where('ToDate BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'');
 
       $this->db->group_end();
 
       $this->db->group_end();
 
       $vacations $this->db->get();
 
       $vacation $vacations->result_array();
 
       if (empty($vacation))
 
       {
 
           $vac '';
 
       }
 
       else
        
{
 
           foreach($vacation as $vacs)
 
           {
 
               $vac[] = $vacs['PersonelID'];
 
           }
 
       }
 
       $this->db->select('PersonelID, FirstName, LastName');
 
       $this->db->from('personel');
 
       $this->db->where_not_in('PersonelID'$vac);
 
       $this->db->where('Active'1);
 
       $this->db->order_by('FirstName''ASC');
 
       $personel $this->db->get(); 
but i can't seem to figure out how to combine these 2 query's in CI using the querybuilder. am i missing something, or is this really impossible using the querybuilder...
Reply
#2

(This post was last modified: 07-16-2017, 03:26 PM by PaulD.)

Hi,

Since you have already written the sql why not just use it like so:

PHP Code:
$this->db->query('YOUR QUERY HERE'); 

For complex queries it is often easier to write an sql query and use is as above.

In the docs: https://www.codeigniter.com/user_guide/d...ml#queries

Best wishes,

Paul.

PS You can also enable the profiler to view the query that was generated which often shows you where it is going wrong.
PHP Code:
$this->output->enable_profiler(TRUE); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB