Code Igniter: Displaying raw SQL query to table. Error 1064

Question

This is my first real bash at Code Igniter. I'm attempting to return the results of a mySql query to a table.

My model:

 public function generate_ecomm_data_report(){
    $sql = "SELECT 
                COUNT(*) AS no_skus, 
                pd_vendor AS brand, 
                (SELECTCOUNT(DISTINCT(pd_model_code)) 
                FROM iris_product_data
                WHERE pd_vendor = PD.pd_vendor ) AS unique_models
                FROM iris_product_data PD
                GROUP BY pd_vendor
                ORDER BY 
                COUNT(*) DESC";

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

    }

My controller:

public function ecomma(){
            $this->load->model('report_model');
            $data ['query'] = $this->report_model->generate_ecomm_data_report();
            $this->load->view('report_view', $data);    
    }

When loading the controller I get a 1064 error however. Can anyone see an issue here? (I've excluded the View, I don't believe there's an issue here).

Aniruddha Chakraborty · Accepted Answer · 2015-12-08 14:47:56Z

4

i guss Your problem is in the SQL query Try editing from first block of code from view . Changed the 5th line ny adding "AS pd_model_code

public function generate_ecomm_data_report(){
    $sql = "SELECT 
                COUNT(*) AS no_skus, 
                pd_vendor AS brand, 
                (SELECT(COUNT(DISTINCT(pd_model_code)) AS pd_model_code
                FROM iris_product_data
                WHERE pd_vendor = PD.pd_vendor ) AS unique_models
                FROM iris_product_data PD
                GROUP BY pd_vendor
                ORDER BY 
                COUNT(*) DESC";

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

    }

One Advice: You are using MVC pattern in a wrong way :) .. Try not to use any kind of query in view ..

Happy Coding :)

answered Dec 8, 2015 at 14:47

Aniruddha Chakraborty

1,8621 gold badge22 silver badges33 bronze badges

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

1 Comment

Josh Rumbut Over a year ago

Very good point about the MVC! Keeping database access in the model, away from presentation is the first step in making your app reliable and maintainable.

Josh Rumbut · Accepted Answer · 2015-12-08 14:45:12Z

2

There needs to be a space between SELECT and COUNT in the 4th line of your query.

So it should be like this:

 $sql = "SELECT 
                COUNT(*) AS no_skus, 
                pd_vendor AS brand, 
                (SELECT COUNT(DISTINCT(pd_model_code)) 
                FROM iris_product_data
                WHERE pd_vendor = PD.pd_vendor ) AS unique_models
                FROM iris_product_data PD
                GROUP BY pd_vendor
                ORDER BY 
                COUNT(*) DESC";

answered Dec 8, 2015 at 14:45

Josh Rumbut

2,7102 gold badges34 silver badges43 bronze badges

2 Comments

Liam Fell Over a year ago

Thanks, I suppose this is why active records are nice. Interestingly the query worked as it was in my DB application.

Aniruddha Chakraborty Over a year ago

nice one @JoshRumbut .. One +1 From me! :)

Collectives™ on Stack Overflow

Code Igniter: Displaying raw SQL query to table. Error 1064

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related