codeigniter model query not sending json data correctly

Question

I have a codeigniter model function which queries a database and sends the result back to the controller, encoded as json.

the entire function is shown below:

  function get_skufamily_cube($q){

        $sql=("select min([Pieces]) as ProductCode from
(SELECT  
       [ProductCode]
      ,[Description]
      ,[Length]
    ,[Pieces]
      ,[Thickness]
      ,[Width]
    ,([width]*1000) w2
    ,([thickness]*1000) t2
    ,REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','') AS l2
    ,concat(([width]*1000),([thickness]*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')) AS pc
    ,REPLACE([ProductCode],concat(([width]*1000),([thickness]*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'') as grade
    ,CONCAT(([width]*1000),([thickness]*1000),REPLACE([ProductCode],concat(([width]*1000),([thickness]*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options

  FROM [hammerhead].[dbo].[ProductList]) as p
         where Options like '%$q%' ");
    $query=$this->db->query($sql);


    if($query->num_rows > 0){
      foreach ($query->result_array() as $row){
        $row_set[] = htmlentities(stripslashes($row['ProductCode']));
        echo  $row['ProductCode']; // to see database result and troubleshoot
      }
      $this->output->set_content_type('application/json')->set_output(json_encode($row_set));
    }

  }

$q is being passed successfully to the query and the output of the query in echo $row['ProductCode']; is consistent with the result I require. in this case it is 108. the database query returns a single result in a single field.

For some reason, this is no tbeing passed back tot he controller correctly.

Controller is:

    $this->load->model('Sales_model');
    if (isset($_POST['data'])){
        $q = strtolower($_POST['data']);
        $data = $this->Sales_model->get_skufamily_cube($q);
        $this->output->set_content_type('application/json')->set_output(json_encode($data));
    }
}

In my developer tools, I can see the server response 108NULL. 108 being my echo and NULL being the json response. if I remove the echo this is simply NULL. enter image description here

Laslty, I need to populate the input in my table row with the value. my view jquery syntax for this is:

$.post('get_skufamily_cubes', {data:selectedObj.value},function(result) 
 { 
 $(this).find('input[id^="cubesperbundle"]').val(result); 
 });

Currently nothing is populated. the html input name and id is:cubesperbundle but has the row number appended to it hence the 'input[id^="cubesperbundle"]'

Any assistance would be appreciated.

Thanks and Regards,

Muhammad Raheel · Accepted Answer · 2013-04-23 07:28:28Z

1

The mistake i see is that you are not returning anything to controller.

Model

function get_skufamily_cube($q)
{
    $Query="select
              min(Pieces) as ProductCode
            from (SELECT
                    ProductCode,
                    Description,
                    Length,
                    Pieces,
                    Thickness,
                    Width,
                    (width*1000)    w2,
                    (thickness*1000)    t2,
                    REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','') AS l2,
                    concat((width*1000),(thickness*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')) AS pc,
                    REPLACE(ProductCode,concat((width*1000),(thickness*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'') as grade,
                    CONCAT((width*1000),(thickness*1000),REPLACE(ProductCode,concat((width*1000),(thickness*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options
                  FROM hammerhead.dbo.ProductList) as p
            where Options like '%$q%'";
    return  $this->db->query($Query)->row();
}

Controller

function getJson(){
    $this->load->model('Sales_model');
    if (isset($_POST['data'])){
        $q = strtolower($_POST['data']);
        $viewData   =   $this->Sales_model->get_skufamily_cube($q);
        $data_json = json_encode($viewData);
        echo $data_json;
    }
}

EDITS:
Change the return instruction in model function.

$result = $this->db->query($Query)->row();
return $result->ProductCode;

edited Apr 23, 2013 at 7:28

answered Apr 23, 2013 at 7:03

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Smudger Over a year ago

Thanks Raheel, appreciate you always helping me out. the server response with this is:[{"ProductCode":108}] I need just the 108. Any ideas? Thanks again.

Smudger Over a year ago

Hi Raheel, I have added to my question, I cant seem to populate my input with this result. could you have a look at my views jquery. Thanks again.

Smudger Over a year ago

let us continue this discussion in chat

Smudger Over a year ago

yip, sorted. server response is perfect.

Collectives™ on Stack Overflow

codeigniter model query not sending json data correctly

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related