Why Codeigniter doesn't accept group by in this case and generate error?

Hi Experts!

I'm facing na page error when trying do run a query in Codeigniter, when groupyng  to the column "idpublicacoes", accordingly with te code below:

         public function dados_relatorio_publicacoes_datas($qtd = 0,$inicio = 0)
        {

            $data_inicial =  $this->input->post('data_inicial');
        	$data_final   =  $this->input->post('data_final');
                
            $this->db->where('data >=', $data_inicial);
            $this->db->where('data <=', $data_final);
            
            $this->db->group_by("idpublicacoes"); 
            
            return $this->db->get('tb_registra_publicacoes');
    	}

Open in new window


If I don't put the line:
$this->db->group_by("idpublicacoes");

Everything goes ok.

Here is the table structure:

CREATE TABLE tb_registra_publicacoes
(
  idregpublicacoes integer NOT NULL DEFAULT nextval('id_seq_reg_publicacoes'::regclass),
  idpublicacoes integer NOT NULL,
  nome character(300) NOT NULL,
  data date NOT NULL,
  visitas integer,
  CONSTRAINT pk_tb_reg_publicacoes PRIMARY KEY (idregpublicacoes)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb_registra_publicacoes
  OWNER TO postgres;

Open in new window


What could be wrong in the code and must be done?

Isn't necessary to code the query to count()  the lines too?

Thanks in advacnce!
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marco GasiFreelancerCommented:
First, but this doesn't make mutch sense to me, you can try to do a little change to your code:
         public function dados_relatorio_publicacoes_datas($qtd = 0,$inicio = 0)
        {

            $data_inicial =  $this->input->post('data_inicial');
            $data_final   =  $this->input->post('data_final');
            $this->db->select('*');
            $this->db->from('tb_registra_publicacoes');    
            $this->db->where('data >=', $data_inicial);
            $this->db->where('data <=', $data_final);
            
            $this->db->group_by("idpublicacoes"); 
            
            return $this->db->get();
    	}

Open in new window


if this doesn't work, it could be interesting to know what it happens when you run the query in more traditional way:

public function dados_relatorio_publicacoes_datas($qtd = 0,$inicio = 0)
{
     $sql = "SELECT * FROM tb_registra_publicacoes WHERE data >=$data_inicial AND data >=$data_final GROUP BY idpublicacoes";
    $result = $this->db->query($sql);
    return $result->result_array();
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FishMongerCommented:
You said you received an error but didn't tell us the the error message.  What was it?
0
Marco GasiFreelancerCommented:
I think 'na page error' stay for 404 Page not found error, which CodeIgniter usually give for routing errors. But this doesn't seem the routing be involved since the function gives expected result if the group_by line is commented out...
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

FishMongerCommented:
If it really is a "404 Page not found" error, then something else is wrong either in addition to or in-place of the possible sql error.

I don't use Postgres, so I can't do any testing, but assuming the problem is due to the group by clause, then this may be the reason.
PostgreSQL 8.3.23 Documentation
The GROUP BY Clause is used to group together those rows in a table that share the same values in all the columns listed. The order in which the columns are listed does not matter. The effect is to combine each set of rows sharing common values into one group row that is representative of all rows in the group. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups. For instance:
=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

Open in new window

In the second query, we could not have written SELECT * FROM test1 GROUP BY x, because there is no single value for the column y that could be associated with each group. The grouped-by columns can be referenced in the select list since they have a single value in each group.
0
FishMongerCommented:
If you haven't already done so, you should make sure you're running in "development" mode so that full error reporting is enabled.

Since you're getting a 404 error, I suspect that error reporting is turned off (i.e., you're in production mode) and codeigniter is trying to send a "500 Internal Server" error, but that error page is missing so it then sends the 404 error page.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Hi Experts

Thank you for the assistance until now.

Yes the page is exactly  the "404 Page not Found".  The query runs OK in Postgres environment.

I'm out of office now but anxious to try the solutions you've pointed tomorrow
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
The way Codeigniter treats the query is still too much misterious for me:
After wasting all the morning in tentatives, probably this is the better I get:

   // EF 2015 Acesso aos artigos por intervalo de datas
    	public function dados_relatorio_acesso_datas($qtd = 0,$inicio = 0){

            pg_query("INSERT INTO teste VALUES ('------Inicio 06------')");

            $data_inicial =  $this->input->post('data_inicial');
        	$data_final   =  $this->input->post('data_final') ;

            pg_query("INSERT INTO teste VALUES ('".$data_inicial."')"); 
            pg_query("INSERT INTO teste VALUES ('".$data_final."')");
            
            $sql = "SELECT nome, count(nome) FROM tb_registra_artigos WHERE dia >= '".$data_inicial. "' AND dia <= '". $data_final."' GROUP BY nome";

            $sql = "SELECT nome, count(nome) FROM tb_registra_artigos WHERE dia >= '01/10/2015' AND dia <= '31/10/2015' GROUP BY nome";

            //pg_query("INSERT INTO teste VALUES ('".$sql."')");
            
            $result = $this->db->query($sql);
            return $result->result_array();

// No results
//            $data_inicial =  $this->input->post('data_inicial');
//            $data_final   =  $this->input->post('data_final');
//            
//            $this->db->select('nome, count(nome)');
//            
//            
//            $this->db->from('tb_registra_artigos');    
//            $this->db->where('data >=', $data_inicial);
//            $this->db->where('data <=', $data_final);
//            
//            $this->db->group_by("nome"); 
//            
//            pg_query("INSERT INTO teste VALUES ('------Fim 06------')");
//
//            return $this->db->get();
//
    	}

Open in new window


Accordingly wit this evidences, runs OK in Postgres:

img001
But Codeigniter give this error:

img 002

The code pointed by the error:

      public function efetuar_pesquisa_acessos_data(){
        	if (logado(FALSE)){
	            $valor = mb_strtolower($this->input->post('nome',TRUE));
                $CI =& get_instance();
                $user_idusuario = $CI->session->userdata('user_id');
				$user_idsetor = $CI->session->userdata('user_fk_idsetor');
	            

// Line 2065:
                $dados_acesso['dados_acesso']= $this->acesso_usuarios_model->dados_relatorio_acesso_datas()->result();
//                                
	            $this->load->vars($dados_acesso);
	            $data['title'] = "Acessos de Artigos no Período";
	            $this->load->view('template_head',$data);
	            
                $CI =& get_instance();
                $user_idsetor = $CI->session->userdata('user_fk_idsetor');
                    
                $this->load->view('menu/menu');
                $this->load->view('usuarios/acessos_data_dados');
                $this->load->view('template_footer');
	        }
	}

Open in new window


Any other opinion ?

(I really don't know where to go from here)
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Ops...

It's a matter of a little change to

  //return $result->result_array();
    return $result;

Open in new window


Thanks heaven!
0
FishMongerCommented:
Good to hear that you found and fixed the problem, but I see another potential area for a problem.
pg_query("INSERT INTO teste VALUES ('------Inicio 06------')");

Open in new window

You're not including the connection object, which is allowed to leave out but according to the manual, leaving it out could cause bugs.

   Note: Although connection can be omitted, it is not recommended, since it can be the cause of hard to find bugs in scripts.
In addition to that, it also states:
pg_query_params() should be preferred in most cases.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Thanks for the assistance!
0
Marco GasiFreelancerCommented:
You're welcome. Thanks for points.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Hello FishMonger

But this insert runs ok...
0
FishMongerCommented:
My comment regarding the pg_query() syntax was not in relation to the issue in your question.  I was  just pointing out that based on the php manual, the syntax you're using is not the best and could lead to future bugs which could be difficult to trace.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.