accessing a second database
Permalink
Hi folks,
I'm hitting a problem with a site which I'm trying to develop using concrete5. I'm conscious that it isn't going to be a straightforward one to solve, and really, what I'm looking for are tips, hints and nudges in hopefully the right direction, so that I avoid going down blind alleys.
The site is for a local natural history group, and one of the key requirements is the ability to store and retrieve data about venues, events, and what was found at the events. I've designed a suitable database, and already have it defined in MySQL: I've also written a basic CRUD interface in PHP - I can use it from my local apache server, and it works fine.
I've started building the new site in concrete5, and initially it went very well: I was massively impressed by the ease of use and intuitive interface. Eventually, I arrived at the task of incorporating the CRUD interface to the external database - and here I'm hitting what appears to be a number of brick walls - the main one being difficulty (or impossibility?) to access a database other than the concrete5 core database.
I'm trying to work through chapter 5 of David Strack's "Concrete5 cookbook", headed "Working with databases and models". On p123, he says that a database other than the c5 one can be connected using (this is actually incorrect, as it needs the extra parameter ",true)" at the end.) This works for basic SQL queries. However, Strack then goes on to describe the creation of a custom model class - and here I'm hitting the current showstopper. Even though I'm using the modified Loader::db call to access my additional database in the methods in the new model class, whenever I test the model, I get a message starting "mysqlt error 1146" which effectively says that it's trying to run a "SHOW COLUMNS" statement against a table from my external database - but trying to find the table in the c5 core database.
From an initial hunt through the code, the problem appears to be that the constructor method in the core model class (in concrete/core/libraries/model.php) contains with no possibility of this being dynamically modified - so it will always access the concrete5 database. As an experiment, I tried defining a constructor method in my own model class, using a modified Loader::db() statement to point to the external database: this actually worked - but as soon as I added a statement, it broke with the same message as before (which I guess is understandable, as the default Loader:db() call is happening after my modified one.)
It seems that I've got three main options:
1) Forget the concrete5 built-in database API and build my own from scratch.
2) Work out what happens in all the constructors from my own model class up to the core one, and then write my own __construct() method to override the defaults.
3) Move my external database tables into the concrete5 database (probably the easiest option, but also the most undesirable for several reasons.)
Any thoughts? Sorry this is a long and messy problem description - I'm feeling my way with this ... Maybe what I should be looking at is trying to define a package?
I'm hitting a problem with a site which I'm trying to develop using concrete5. I'm conscious that it isn't going to be a straightforward one to solve, and really, what I'm looking for are tips, hints and nudges in hopefully the right direction, so that I avoid going down blind alleys.
The site is for a local natural history group, and one of the key requirements is the ability to store and retrieve data about venues, events, and what was found at the events. I've designed a suitable database, and already have it defined in MySQL: I've also written a basic CRUD interface in PHP - I can use it from my local apache server, and it works fine.
I've started building the new site in concrete5, and initially it went very well: I was massively impressed by the ease of use and intuitive interface. Eventually, I arrived at the task of incorporating the CRUD interface to the external database - and here I'm hitting what appears to be a number of brick walls - the main one being difficulty (or impossibility?) to access a database other than the concrete5 core database.
I'm trying to work through chapter 5 of David Strack's "Concrete5 cookbook", headed "Working with databases and models". On p123, he says that a database other than the c5 one can be connected using
$db=Loader::db('host','user','password','database')
From an initial hunt through the code, the problem appears to be that the constructor method in the core model class (in concrete/core/libraries/model.php) contains
$db=Loader::db()
parent::__constructIt seems that I've got three main options:
1) Forget the concrete5 built-in database API and build my own from scratch.
2) Work out what happens in all the constructors from my own model class up to the core one, and then write my own __construct() method to override the defaults.
3) Move my external database tables into the concrete5 database (probably the easiest option, but also the most undesirable for several reasons.)
Any thoughts? Sorry this is a long and messy problem description - I'm feeling my way with this ... Maybe what I should be looking at is trying to define a package?
Hi John - thanks for replying. At the moment, I'm really trying to avoid putting the tables into the concrete5 database: the main reasons are that I want to be able to back them up independently, and almost certainly use them in the context of other applications. Obviously, I could still do both these things if they were in the concrete5 database, but it would add complications. Also, I've read in several places that concrete5 can use other databases, and it's annoying that it's proving so difficult! (However, I do seem to be making some progress - I'll report back when I've done a bit more testing.) I did see some other threads about using second databases - but they all seem to point to the same document, which apparently no longer exists (I'm wondering if this has something to do with recent changest to the database drivers.)
So I've done this in 5.6 installs by doing this in my config/site.php file
and doing this where I needed to
and doing this where I needed to
$db = Loader::db(DB_SERVER2, DB_USERNAME2, DB_PASSWORD2, DB_DATABASE2, true); //db stuff $db = Loader::db(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE, true); //reset it for the rest of the page stuff.
If you search back through the forums, there are several threads on connecting a second database.
I think most/all would connect, query, then reset to the c5 database for each query or immediate sequence of queries. ie. not keep both connections open at the same time. (please double check that, as I noted above, its not something I have done)
Unless the data really needs to be in a second database, its easy enough to set up a schema for some tables in a c5 db.xml file then import the existing database tables from a sql dump using phpMyAdmin. Then you don't have to worry about all the second database complexities.