CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
Shafat Ahmad
Shafat Ahmad

Reputation: 77

use string variable as query in codeigniter

i am using codeigniter and i need to use aggregate function in query. so i have this query. 

"SELECT Dated, CASE WHEN `Account_ID` = 2 then SUM(Total_Bricks) ELSE 0 end as 'Nadeem', 
CASE WHEN Account_ID = 2 then SUM(Kaat_Bricks) ELSE 0 end as 'NadeemKaat', 
CASE WHEN `Account_ID` = 7 then SUM(Total_Bricks) ELSE 0 end as 'Abid', 
CASE WHEN Account_ID = 7 then SUM(Kaat_Bricks) ELSE 0 end as 'AbidKaat', 
CASE WHEN `Account_ID` = 8 then SUM(Total_Bricks) ELSE 0 end as 'Sajid', 
CASE WHEN Account_ID = 8 then SUM(Kaat_Bricks) ELSE 0 end as 'SajidKaat' 
FROM `tblstockdetail` GROUP BY `Dated`"

i have generated this query through a simple foreach loop 

$stock = $this->Kharkaar_Model->get_stockdetail();

        $sql = '"SELECT Dated, ';

        $numItems = count($stock);
        $i = 0;
        foreach ($stock as $key => $value) 
        {

            if(++$i === $numItems)
            {
                $sql.= "CASE WHEN `Account_ID` = ".$value['Account_ID']." then SUM(Total_Bricks) ELSE 0 end as '".$value['AccountName']."', <br /> 
            CASE WHEN Account_ID = ".$value['Account_ID']." then SUM(Kaat_Bricks) ELSE 0 end as '".$value['AccountName']."Kaat' <br /> FROM `tblstockdetail` GROUP BY `Dated`";
            }
            else
            {
                $sql.= "CASE WHEN `Account_ID` = ".$value['Account_ID']." then SUM(Total_Bricks) ELSE 0 end as '".$value['AccountName']."', <br /> 
            CASE WHEN Account_ID = ".$value['Account_ID']." then SUM(Kaat_Bricks) ELSE 0 end as '".$value['AccountName']."Kaat', <br /> ";

            }

        }

        $sql.= '"';

now when i try to get result of this query 

$result = $this->db->query($sql);

it is giving me a syntax error, otherwise when i put this query direct into 

$result = $this->db->query(// string query here );

it is running fine.

Upvotes: 1

Views: 1769

Answers (1)

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

Your foreach should be like this

$stock = $this->Kharkaar_Model->get_stockdetail();

$sql = "SELECT Dated, ";

$numItems = count($stock);
$i = 0;
foreach ($stock as $key => $value) 
{

    if(++$i === $numItems)
    {
        $Account_ID = $value['Account_ID'];
        $AccountName = $value['AccountName'];

        $sql.= "CASE WHEN `Account_ID` =  $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName, 
        CASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName 
        FROM `tblstockdetail` GROUP BY `Dated`";
    }
    else
    {
        $sql.= "CASE WHEN `Account_ID` = $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName,
        CASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName";

    }

}

if TRUE

"SELECT Dated, 
CASE WHEN `Account_ID` =  $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName, 
CASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName 
FROM `tblstockdetail` GROUP BY `Dated`"

if FALSE

"SELECT Dated, 
CASE WHEN `Account_ID` = $Account_ID then SUM(Total_Bricks) ELSE 0 end as $AccountName,
ASE WHEN Account_ID = $Account_ID then SUM(Kaat_Bricks) ELSE 0 end as $AccountName"

Wrong in your code

  1. Wrapping too much with ' and "
  2. <br /> don't use irrelevant tags

Suggestion

  1. Use Variable ($Account_ID) instead of actual array pointer ($value['Account_ID'];) - It's EASY to UNDERSTAND and easy to DEBUG

Upvotes: 1

Related Questions