Israel_2 0 Newbie Poster

So I have this simple query that if I run it on sql server management studio it gives me results.

Select name, count(*) As thecount from Restaurants group by name

I am using codeigniter and a wrapper library for datatables called ignited datatable . I am using it because its easier to integrate sql results to a nice json format for tables. The problem I ran into is that the group by function used by this library gives me an error.. This is my query

 $this->datatables->select("name, count(*) as thecount");
 $this->datatables->from("Restaurants");
 $this->datatables->group_by('name');
 echo $this->datatables->generate();

I believe this is a straightforward query it shouldn't give me any errors but when it echoes it gives me a database error

Error Number: 42000
[Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'Restaurants.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT * FROM Restaurants GROUP BY name (<- Note: Why is trying to select all columns?)

There is something wrong with the group_by functionality in this library because if I do another query without the group by it works just fine.
This is the select and group_by functionality in the datatables library

public function select($columns, $backtick_protect = TRUE)
    {
      foreach($this->explode(',', $columns) as $val)
      {
        $column = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$2', $val));
        $column = preg_replace('/.*\.(.*)/i', '$1', $column); // get name after `.`
        $this->columns[] =  $column;
        $this->select[$column] =  trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$1', $val));
      }
      $this->ci->db->select($columns, $backtick_protect);
      return $this;
    }

 public function group_by($val)
    {
      $this->group_by[] = $val;
      $this->ci->db->group_by($val);
      return $this;
    }

I would also like to mention if i accidently mispell something like this $this->datatables->group_by('nam'); I would get
a different error

Error Number: 42S22
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'name'.
SELECT AccountName, count(*) AS totalcount FROM ftmAccounts GROUP BY [name]

The point is selecting the right columns when i mispell something

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.