How can i use an in index.php declared db-connection inside my extended classes? And how to set up and prefix for the database?

[JamesHemmingworth]

Hey there,

i have a little question, maybe you can help me with it.

Im trying to archieve an MVC-styled system and am stucking at a little problem: i declared a database connection in index.php with:

$db = new \DB\SQL('mysql:host=localhost;port=????;dbname=test','user','password');

After this, im autoloading my class with the name "Models" that extends  "DB\SQL" and all the classes inside the "models" folder which extend it so that i just wanna call smth like this:

$titleofPage = Models->getTitleOfPage();

{Im not using magic-methods, just calling the functions for better readability}

Inside the Models, i have this in my __construct:

public function __construct() {

$this->db = parent::__construct( \Base::instance()->get('db'), 'lm_config' );

}

What im expecting: It gets the database-variable "$db", which has the database-connection stored inside, and distributes them to my functions if i call it. The "lm_config" is the database-name, which specifies the table all the functions inside MainModel have access to and only that one to prevent problems (i would join tables with leftjoin if i need other informations). For each table, i wanna use a specified class-file to handle all incoming and outcoming calls to have a better structure and to make plugins and bugtracking way easier.

Inside my MainModel, which extends Models. i have the following __construct:

parent::__construct();

And a function named getTitle:

public function getTitle() {

        $result = $db->exec('SELECT title FROM lm_config');

        return $result;

    }

The return goes to another file which handles the coresponding dynamically generated URL.

Now here comes my problem:

If i just use $db inside my functions, it would not allow me to use any arguments 

(eg. "call to undefined method "exec()" on NULL") 

and if i use the approache above, it would print this:

invalid data source name [D:\xampp2\htdocs\fatfree\private\lib\db\sql.php:496]

[D:/xampp2/htdocs/fatfree/private/lib/db/sql.php:496] PDO->__construct('','config',NULL,[])
[D:/xampp2/htdocs/fatfree/private/classes/Models.php:6] DB\SQL->__construct(NULL,'config')
[D:/xampp2/htdocs/fatfree/private/model/MainModel.php:6] Models->__construct()
[D:/xampp2/htdocs/fatfree/public/index.php:46] MainModel->__construct()

I already know that [D:/xampp2/htdocs/fatfree/private/lib/db/sql.php:496] PDO->__construct('','config',NULL,[]) is the problem here, but i have declared the database-information already in the index.php, so what is wrong? Am i missing some OOP-basics here that the scope is wrong or does F3 sanitize my connection so everything is NULL when it arrives at my functions?

And another question:

How can i add a prefix like "lm_" in my example above to the database-connection so that if i call a table "user",it would put the prefix in fornt of it ("lm_user")?

Help is much appreciated.

With Greetings,

JamesHemmingworth.

[tom chris]

; Set Installed
installed = true

; Database Settings
db_dns = mysql:host=localhost;dbname=
db_host = localhost
db_name = tested
db_user = root
db_pass =
db_prefix =

BaseController

function __construct() {

$f3 = Base::instance();
$this->f3 = $f3;

$this->home_url = $f3->get('SCHEME').'://'.$f3->get('HOST').$f3->get('BASE');


/* Database Connect */
try {

$db = new DB\SQL(

$f3->get('db_dns') . $f3->get('db_name'),
$f3->get('db_user'),
$f3->get('db_pass')
);
} catch (PDOException $e) {
$f3->status(503);
echo "<h1>Error establishing a database connection</h1>";
exit;
}


// Database
$this->db = $db;

}

Other controller will extends it so you can use that $this->db in other classes

like

class LoginController extends BaseController

To add Prefix add it to Models Like

<?php

class User extends DB\SQL\Mapper {
   
protected $f3;    
   
public function __construct(DB\SQL $db) {
   
$this->f3 = Base::instance();

$db_prefix = $this->f3->get('db_prefix');

parent::__construct($db,$db_prefix.'series');
}

you need to use $this->db->exec($query);

[JamesHemmingworth]

thx tom chris,

after some line-changing and optimization it finally runs. For some reasons i cant use the prefix, because inside my database.ini with the database-informations you provided me with the db_prefix has the value of db_name, but cant seem to figure out why it would do that.

But that doesnt bother me anymore. I define a protected variable inside my construct with $this->tablename und call them if i need them inside my prepared sql-statement.

Thx for you help.

With greetings,

JamesHemmingworth.

[Abhas Dudeja]

Hi, this is in continuation with the discussion at hand, can you please guide me as to how i can put my DB details in a different file rather than putting it with each individual route that is present and using DB  connection in Index.php..
The issue i face is, I am trying to create a GeoData Manager online, F3 is being used to fetch the data present on the server, and thus i wish to make my DB details private from the main index.php file

Thanks in advance.

[richgoldmd]

There are several ways to accomplish this.

This "best" way, in my opinion, is to store the connection details in environment variables and then load them using getenv. phpdotenv (https://github.com/vlucas/phpdotenv) is a useful package in development to mimic this without actually adding environment variables to your development environment.

But in short, say you define the db settings as environment variables like

DB_USER=myDbUser

DB_PASS=myDbPass

.. etc.. 

Then, in index.php or wherever you create the DB object (and save in $f3->set('DB', $myDb) for example, so its available everywhere), you could do:

$db = new DB\SQL(   getenv('DB_DNS'),
   getenv('DB_NAME'),
   getenv('DB_USER'),
   getenv('DB_PASS')
);

In this way, only those who have access to the server and can see environment variables can see the db credentials. This is one tenet if the "Twelve-factor app" (https://12factor.net/config)

In my apps, I use some config to indicate production vs dev and then use phpdotenv to load config from a .env file in development:

// Load environment vars for development
if (getenv('PROD_ENVIRONMENT') === FALSE) {
 $dotenv = Dotenv\Dotenv::create(__DIR__ . '/conf');
 $dotenv->load();
}