Setting up a db connection using variables from an

Question

I am attempting to set up a db connection for a mysqli_connect statement $dbc. I had it working in my script with the following:

DEFINE ('DB_USER','myName';
DEFINE ('DB_PASSWORD','somePass123';
DEFINE ('DB_HOST','localhost';
DEFINE ('DB_NAME','sitename';


// make the db connection
    $dbc = @mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME)
    OR die ('Could not connect to mysql: ' . mysqli_connect_error());

I then posted a question on SO about security and best practice and was advised that if the connection variables (password, host, dbname, user) exist elsewhere in a separate database configuration file, that I should call the variables for the connection from there.

Here is the relevant snippet from the file in config/database/php

$config['default'] = array(
    'benchmark' => TRUE,
    'persistent' => FALSE,
    'connection' => array(
        'type' => 'mysqli',
        'user' => 'myName',
        'pass' => 'somepass123',
        'host' => 'localhost',
        'port' => FALSE,
        'socket' => FALSE,
        'database' => 'sitename',
    ),
    'character_set' => 'utf8',
    'table_prefix' => '',
    'object' => TRUE,
    'cache' => FALSE,
    'escape' => TRUE
);

So I then tried this:

DEFINE ('DB_USER','$config['default']['connection']['user'])';
DEFINE ('DB_PASSWORD',$config['default']['connection']['pass']);
DEFINE ('DB_HOST',$config['default']['connection']['host']);
DEFINE ('DB_NAME',$config['default']['connection']['database']);


// make the db connection
    $dbc = @mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME)
    OR die ('Could not connect to mysql: ' . mysqli_connect_error());

Which produced the following error when I tried to load the page: "Undefined variable: config"

I then tried this:

 $dbc = @mysqli_connect(
    $config['default']['connection']['host'],
    $config['default']['connection']['user'],
    $config['default']['connection']['pass'],
    $config['default']['connection']['database'])

    OR die ('Could not connect to mysql: ' . mysqli_connect_error());

// Set the encoding
mysqli_set_charset($dbc, 'utf8');

// set the query variable
$query = "SELECT MAX(rating_date) 
          AS last_date
          FROM rating
          WHERE incident_id = $incident_id;";

//connect and run the query
$result = mysqli_query($dbc, $query);

 echo $result->fetch_object()->last_date;

?>

Which then produced the following error: "Fatal error: Call to a member function fetch_object() on a non-object in..."

I'm not 100% sure what this is telling me. Yesterday I learned that one cannot echo a SQL query in php directly because it's an object so I cut n pasted the part "echo $result->fetch_object()->last_date;" which worked.

It seems that now that I am trying to draw upon the variables from config, rather than just define them in the function, I am unable to connect perhaps due to a scope issue?

The file with the $dbc connection is in a folder themes/myname/views/reports/detail.php

The file with the database configuration details array is in application/config/database.php

It looks like an issue of variable scope?

What would be the best practice here in making my $dbc variable? How do I call the variables from database.php when it exists in a different directory than the file where I'm calling it? Must I include() the whole file?

Answer 1

How about something sane like:

$dbc = @mysqli_connect(
  $config['default']['connection']['host'],
  $config['default']['connection']['user'],
  $config['default']['connection']['pass'],
  $config['default']['connection']['database'])
OR die ('Could not connect to mysql: ' . mysqli_connect_error());

You don't gain anything by using define(), you're only trying to avoid putting the raw strings with your username and password into the mysqli_connect() call in case an error occurs and that line gets sent to the client in an error message/stack trace.

Answer 2

Constants are global and you don't need to set them as global to retrieve them from the memory but variables are not, if you are calling the variable $config from inside a function you have two ways to call it:

1. Set the variable to be global at the first beginning of your function that you are calling this variable from global $config;

OR

1. Call the variable using the $GLOBALS variable $config = $GLOBALS['config'];