Codeigniter remote connection does not return queries

[eluser]sunblast[/eluser]
I have a local and a remote connection with my mysql database. The local connection works just fine. But the remote connection, while it makes a connection, it does not return anything. I usually get the following:

Fatal error: Call to a member function result() on a non-object

I use for the remote connection the following configuration:

Code:

$db['mydb']['hostname'] = "ip_address_of_database";
    $db['mydb']['username'] = "username";
    $db['mydb']['password'] = "password";
    $db['mydb']['database'] = "database";
    $db['mydb']['dbdriver'] = "mysql";
    $db['mydb']['dbprefix'] = "";
    $db['mydb']['pconnect'] = FALSE;
    $db['mydb']['db_debug'] = FALSE;
    $db['mydb']['cache_on'] = FALSE;
    $db['mydb']['cachedir'] = "";
    $db['mydb']['char_set'] = "utf8";
    $db['mydb']['dbcollat'] = "utf8_general_ci";

In my function that accesses the database I check if there is a connection with the remote server and then I try to retrieve data.

Code:

$mydb = $this->load->database('mydb', TRUE);
    if (!isset($mydb->conn_id) && !is_resource($mydb->conn_id)) {
        $error = 'database is not connected';
        return $error;
    }else{
        $query = $mydb->query("SELECT * FROM table_name LIMIT 1;");
        return $query->result();            
         }

This works fine in the localhost database but not in the remote database. I allways get the error Fatal error: Call to a member function result() on a non-object

Can you please help? What am I doing wrong? I stuck on this.

[eluser]theprodigy[/eluser]
just for troubleshooting purposes, when setting the ip address of the remote host, are you also setting the correct port number?

Have you tried setting your if conditional to use an OR instead of an AND, and see if that helps?

[eluser]sunblast[/eluser]
Thanks for the reply.
I am trying that online, from webhost to webhost, therefore there are no ports that I can configure. The only change I made is to allow from the cpanel the remote connection to the database from the ip of the other webhost. Both webhost plans have static ip therefore I can do that without any problem.
My main concern is that while the check of the database connection passes without any problem, the queries do not return anything.

[eluser]theprodigy[/eluser]
The only thing I can think of is that your query isn't returning records, probably even throwing an error and for some reason, your 'remote' web server isn't catching it.

Have you tried running your query directly on the remote db server and see what it returns?

I would run a couple of tests myself.

1. Connect directly to your remote db server (ssh, cpanel and phpmyadmin, whatever), and running the query locally.

2. Connect remotely from another machine using a program like mysql workbench, and running the query that way.

3. If both of these work, then run a print_r on your $mydb variable before running any queries and make sure you are connecting correctly and fully.

That's all I can think of right now.

Try those things and write back with your results.

[eluser]sunblast[/eluser]
Again thanks for the reply.
I did what you proposed as follows.
I tried to run the query directly on the remote db server and it works just fine.
I also run the query through phpmyadmin of the remote server and it works also
I tried to connect with mysql workbench and run the query and it worked also. This step gave me even more frustration.
Finally, I run prin_r $mydb before running any queries and it returned me a series of connection instructions for the remote server with the settings I had defined in my dadabase.php file. In my code I check if the connection is made with the code:

Code:

if (!isset($mydb->conn_id) && !is_resource($mydb->conn_id))

I do not know what else I can do. Maybe is a bug of Codeigniter.
I will try to use standard php to make the connection and execute the query and see if it works that way.

[eluser]TWP Marketing[/eluser]

Code:

$mydb = $this->load->database('mydb', TRUE);
    if (!isset($mydb->conn_id) && !is_resource($mydb->conn_id)) {
        $error = 'database is not connected';
        return $error;
    }else{
        $query = $mydb->query("SELECT * FROM table_name LIMIT 1;");
        return $query->result();            
         }

The error condition returns a sting. I'm guessing that your controller doesn't check for the type of returned value and tries to process it as an object, expecting to receive $query->result().

[eluser]sunblast[/eluser]
Thanks for the reply.
I don't think that this is the case because I should have the same issue when I am running the code directly on the server where the database is located and not remotely. But I do not have such problem. The code runs fine on the server with localhost database configuration.

[eluser]sunblast[/eluser]
Finally, I found the solution after contacting my web hosting provider. The issue had to do with the Remote database access and their servers. The IP address exception and the domain name that I had added didn't do the job. I had to add an internal domain name that my host was using in order the Remote database access to be allowed. I spent 2-3 hours chatting with them in order to find a solution.
Anyway now is solved. Thank you all for the help.

[eluser]Unknown[/eluser]
I encounter the same problem. I can't solve it yet. Could you plz give us the detailed information?