Empty database output in CI

Question

Hi. I'm building a simple app and trying to test DB result output. But unfortunately all I'm getting is an array of size 0. Here's the controller code excerpt:

$data['query'] = $this->db->query('SELECT role_id, role_privilege FROM role');
$this->load->view('welcome_message', $data);

And a view code excerpt:

<?php
        echo count($query->result_array())."<br/>";
        foreach ($query->result() as $row){
            echo $row->role_id . '<br/>';
            echo $row->role_privilege . '<br/>';
        }
        echo 'Total result '.$query->num_rows();
    ?>

And what I get is next:

0
Total result

Running query from a command line gives a 2 rowed output. Can someone point out what i'm missing?

EDITED:
This issue is also discussed here .

EDITED:
Maybe some platform specific stuff (I really doubt that)? I'm running LAMP (php 5.3.2, mysql 5.1.37, apache 2.2.15).

EDITED:
This prints out a "Array ( )" string. My DB is 100% filled. I can say that for sure, because I did

INSERT INTO role(role_privilege) VALUES ('ROLE_MODERATOR');
INSERT INTO role(role_privilege) VALUES ('ROLE_USER');

and then checked it through a command line.

EDITED:
After I put this into my controller:

echo $this->db->last_query(); exit;

I get next output:

SELECT role_id, role_privilege FROM role

And that's exact sql query that I needed. Unfortunately results are o sized array.

Answer 1

Well, it sounds like your db access is set up properly because you're not getting an error. Which I think also means that the table exists. I know you said you ran that query from the command line, but are you certain you're accessing the same data set/server/table? No weird versioning or anything? It sounds like there aren't records in the role table. Sorry, just grasping at straws as your code looks right.

How about doing

SELECT * FROM role

and then doing a

print_r($query->result_array()); 

in your view? That should reveal column names at least so you know you're accessing the right data.

Try that and report back with any detail and hopefully it will supply some hints.

Answer 2

Well, this here problem is basically not a CI related one, but strange thing is that CI couldn't track this error. Here's what was going on:

While installing php I haven't specified --with-mysql-socket parameter and it looks like php tried to use a /tmp/mysql.sock (default one) which obviously was not specified in my.cnf. So CI tried to bind to a nonexistent socket. Changing mysql params in a php.ini solved the problem.