Codeigniter Can't Connect to External SQL Server

Question

i'm trying to connecting my codeigniter framework to external database. But it shows error

A Database Error Occurred Unable to connect to your database server using the provided settings. Filename: core/Loader.php Line Number: 346

then, i insert this one to end of config/database.php

  echo '<pre>';
  print_r($db['default']);
  echo '</pre>';

  echo 'Connecting to database: ' .$db['default']['database'];
  $dbh=mysql_connect
  (
    $db['default']['hostname'],
    $db['default']['username'],
    $db['default']['password'])
    or die('Cannot connect to the database because: ' . mysql_error());
    mysql_select_db ($db['default']['database']);

    echo '<br />   Connected OK:'  ;
    die( 'file: ' .__FILE__ . ' Line: ' .__LINE__);

But it shows

A PHP Error was encountered Severity: 8192 Message: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead Filename: config/database.php Line Number: 79

A PHP Error was encountered Severity: Warning Message: mysql_connect(): Can't connect to MySQL server on '167.114.xxx.xxx' (111) Filename: config/database.php Line Number: 79

Cannot connect to the database because: Can't connect to MySQL server on '167.114.xxx.xxx' (111)

then i trying to create this one outside the codeigniter dir (in public_html)

<?php
$servername = "167.114.xxx.xxx";
$username = "myusername";
$password = "dbpass";
$database = "dbname";

// Create connection
$conn = mysql_connect($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>

and it shows connected successfully. So, what should i do? while the db details in config/database.php is same with above

The connection that you tried outside codeigniter was it in the same server? Are you sure MySQL server is accepting external connections and the users have the right permissions? — BeoWulf, Apr 12 '16 at 14:39
**Warning**: You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) which has been **removed** entirely from the latest version of PHP. You should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). — Quentin, Apr 12 '16 at 15:29
@BeoWulf yep, the connection i tried is in the same server with the codeigniter. If it has no permission for accepting external connections, how would be the second one give the output "Connected Successfully" — Yudhistira Bayu, Apr 12 '16 at 16:35
@YudhistiraBayu it gives "Connected Successfully" because you're using the wrong approach. The property `->connection_error` only works for MySQLi, not for the (deprecated) MySQL lib. Therefore, there will never be a `$conn->connect_error` so the condition will never be true. The right to do in your case is `if(!$conn)`. And the very right is switching to MySQLi or PDO. See my answer below for the correct approach. — Jefrey Sobreira Santos, Apr 12 '16 at 19:35

score 0 · Answer 1 · answered Apr 12 '16 at 15:00

By default, MySQL will deny connections from outside.

Where is your MySQL server located in? Usually if you're using some web hosting service, they allow you to unlock external connections from they CPanel. Note that most of them require some time to made it really active.

Also, please note mysql_connect does not return an object, but just a resource (connection ID) instead. So if you really want to still using the deprecated (old) MySQL API, please do:

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password'); // note that if the port is different from 3306, you must do: 'server:port'
if (!$link) {
    die('Could not connect ' . mysql_error());
}
echo 'Connection successfull';
mysql_close($link);

However, it's recommended to use something like MySQLi or PDO, as MySQL extension is deprecated due to a long time not being update, lack of OOP support, lack of new MySQL features support etc etc.

Alternative using MySQLI, from PHP manual:

<?php
$link = mysqli_connect("127.0.0.1", "my_user", "my_password", "my_db");

if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL;
echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;

mysqli_close($link);

num8er · Answer 2 · 2016-04-12T15:23:44.733

1) Look at /etc/mysql/my.cnf file and make it have these lines:

bind-address = 0.0.0.0
# skip-networking

2) run /etc/init.d/mysql restart or something like this to restart mysql to open connection from outside

3) enter mysql console and execute:

GRANT ALL ON database_name.* to 'remote_user'@'app_servers_global_ip' IDENTIFIED BY 'very_hard_password';
FLUSH PRIVILEGES;

4) have exact this code in Your config/database.php

$active_group = 'remote';
$query_builder = TRUE;

$db['remote'] = array(
    'dsn'   => '',
    'hostname' => 'database_remote_ip_here',
    'username' => 'remote_user',
    'password' => 'very_hard_password',
    'database' => 'database_name',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => FALSE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => FALSE
);

Codeigniter Can't Connect to External SQL Server

2 Answers2