Welcome Guest, Not a member yet? Register   Sign In
Active Record WHERE_IN(subquery)
#1

[eluser]Ty Bex[/eluser]
I am note getting any results from the query that CI generates but if I run the query manually without the single quotes on the subquery I get a proper result

CONTROLLER
Code:
$filter['promoFlag']         = 1;
$filter['promoType']         = 1;

$levelWhereIn = "SELECT DISTINCT(levelID) FROM  ecom_product WHERE active = 1 AND promoFlag = ".$filter['promoFlag']." AND promoType = ".$filter['promoType'];

$data['level_list'] = $this->M_Ecom->buildDistinctList("Level of Play", "ecom_level", "id", "level", $levelWhereIn);

MODEL
Code:
function buildDistinctList($title = '', $table, $id, $name, $wherein )
    {

        $this->db->select($name, $id);
        $this->db->where_in($id, $wherein, false);
        $Q = $this->db->get($table);
        if ($Q->num_rows() > 0)
        {
            if($title)
            {
                $options[0] = $title;
            }
            foreach ($Q->result() as $row)
            {
                $options[$row->$id] = $row->$name;
            }
            $list = form_dropdown($name, $options);
            return $list;
        }
        echo $this->db->last_query();
        echo "<br />";
        echo "RESULTS = ".$Q->num_rows;
        die();
    }

SCREEN DISPLAY
SELECT `level` FROM (`ecom_level`) WHERE `id` IN ('SELECT DISTINCT(levelID) FROM ecom_product WHERE active = 1 AND promoFlag = 1 AND promoType = 1')
RESULTS = 0

COPY QUERY INTO MYSQL
Quote:MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0003 sec )

MODIFY THE QUERY BY REMOVING SINGLE QUOTES IN RED ABOVE
SELECT `level` FROM (`ecom_level`) WHERE `id` IN (SELECT DISTINCT(levelID) FROM ecom_product WHERE active = 1 AND promoFlag = 1 AND promoType = 1)

Quote:Showing rows 0 - 2 (3 total, Query took 0.0001 sec)

level
---------

Senior
Intermediate
Junior




Theme © iAndrew 2016 - Forum software by © MyBB