Multiple Databases in a Single Table?

shawngibson

Hi all, happy holidays!

Before I try going off on my own here, I should ask: Is it possible to access two or more databases within the same Editor table?

My case:

--I have a database called Inventory which is full of tables.
--I have a database called Tutorials which is full of tables.
--In both dbs I have duplicated a few tables based on categories (both dbs have MetaCats, Categories, and Subcats tables).

Not only is this duplication inefficient, it also leads to a number of issues which I am sure you can imagine.

I'd like to create a new database called Categories, which has Metacats, Categories, and Subcats tables, and thereafter access the Categories database from both the Inventory database and the Tutorials database.

I hope I am making sense here lol:)

I've previously been able to link different tables within the same db, but within different databases seems a different animal to me. Is there a standard way of doing this, if it is even possible?

Thanks as always,
Shawn

allan

Is it possible to access two or more databases within the same Editor table?

As in two different database connections? Or do you have a user which has access to both databases? What database are you using here - MySQL, SQL Server, Postgres?

Allan

shawngibson

I'm running XAMPP on a local computer, full access to Mariadb.

allan

Okay, with Mariadb (and MySQL by extension) you can use notation such as:

database.table.column

In other databases such as Postgres that sort of syntax would be schema first, then table and finally column, but MySQL / Mairadb is a little different.

So you can do things like:

Editor::inst( $db, 'database1.table1' )
    ->fields(
        Field::inst( 'datatable1.table1.column1' ),
        Field::inst( 'datatable1.table1.column2' )
    );

And add joins etc as needed. Just add the database name as a qualifier.

Allan

shawngibson

An elegant and very much appreciated solution. Thank you, Allan:)

shawngibson

Hi Allan (and all),

Now that I have created the unique categories database (with metacats,categories,subcats tables), I'm not sure how to modify the php file. For example, in my products table, I'm currently linking to internal metacat,category, and subcat tables like this:

Editor::inst($db, 'products', 'id')
    ->fields(
        Field::inst('products.product')
            ->validator(Validate::notEmpty()),

etc...
etc...

        Field::inst('products.mfrlnk'),
        Field::inst('products.prodid'),

        Field::inst('products.universe')
            ->options(
                Options::inst()
                    ->table('universes')
                    ->value('id')
                    ->label('universe')
            )
            ->validator(Validate::dbValues()),
        Field::inst('universes.universe'),


        Field::inst('products.metacat')
            ->options(
                Options::inst()
                    ->table('metacats')
                    ->value('id')
                    ->label('metacat')
            )
            ->validator(Validate::dbValues()),
        Field::inst('metacats.metacat'),


        Field::inst('products.category')
            ->options(
                Options::inst()
                    ->table('categories')
                    ->value('id')
                    ->label('category')
            )
            ->validator(Validate::dbValues()),
        Field::inst('categories.category'),


        Field::inst('products.subcat')
            ->options(
                Options::inst()
                    ->table('subcats')
                    ->value('id')
                    ->label('subcat')
            )
            ->validator(Validate::dbValues()),
        Field::inst('subcats.subcat')


    )
    ->leftJoin('universes', 'universes.id', '=', 'products.universe')
    ->leftJoin('metacats', 'metacats.id', '=', 'products.metacat')
    ->leftJoin('categories', 'categories.id', '=', 'products.category')
    ->leftJoin('subcats', 'subcats.id', '=', 'products.subcat')
    ->process($_POST)
    ->json();

This works when using 'select' type in the JavaScript:

$(document).ready(function () {
        var editor = new $.fn.dataTable.Editor({
            ajax: 'php/table.products.php',
            table: '#products',
            fields: [

etc...
etc...
{
    "label": "<a  class='' href='#new_metacat' uk-toggle><span class='icon icon-bookmark-o'></span></a>Metacat:",
    "name": "products.metacat",
    "type": "select",
    "placeholder": "Select MetaCategory"
},
{
    "label": "<a  class='' href='#new_cat' uk-toggle><span class='icon icon-bookmark-o'></span></a>Cat:",
    "name": "products.category",
    "type": "select",
    "placeholder": "Select Category"
},
{
    "label": "<a  class='' href='#new_subcat' uk-toggle><span class='icon icon-bookmark-o'></span></a>Subcat:",
    "name": "products.subcat",
    "type": "select",
    "placeholder": "Select SubCategory"
},
{
    "label": "Universe:",
    "name": "products.universe",
    "type": "select",
    "placeholder": "Select Universe"
}

Then connect the 3 tables:

{
    "data": "products.comment"
},
{
    "data": "products. Keywords"
},
{
    "data": "metacats.metacat"
},
{
    "data": "categories. Category"
},
{
    "data": "subcats.subcat"
},
{
    "data": "universes.universe"
}

To disconnect the internal metacats, categories, and subcats tables and connect to the new Categories db with the same 3 tables, would I simply add the new database name as dot-notation to the last part of the JS above, i.e.:

{
    "data": "products.comment"
},
{
    "data": "products.keywords"
},
{
    "data": "categories.metacats.metacat"
},
{
    "data": "categories.categories.category"
},
{
    "data": "categories.subcats.subcat"
},
{
    "data": "categories.universes.universe"
}

and do similarly with the last part of the PHP:

    ->leftJoin('categories.universes', 'categories.universes.id', '=', 'products.universe')
    ->leftJoin('categories.metacats', 'categories.metacats.id', '=', 'products.metacat')
    ->leftJoin('categories.categories', 'categories.categories.id', '=', 'products.category')
    ->leftJoin('categories.subcats', 'categories.subcats.id', '=', 'products.subcat')

Am I understanding the concept here, Allan?

Thanks as always,
Shawn

allan

Can you add ->debug(true) immediately before ->process($_POST) and then send me a copy of the JSON that is returned from the server-side when the data is loaded?

Thanks,
Allan

shawngibson

I've added the debug line, but I'm not sure where to find the JSON data. I'm guessing it's somewhere in the Chrome Console, but I'm not sure where to look...sorry:(

Right now, I get the following error:

DataTables warning: table id=products - Requested unknown parameter 'categories.metacats.metacat' for row 0, column 9. For more information about this error, please see http://datatables.net/tn/4

And then the same error two more times before loading my table, which works, not including the 3 external tables (metacats, categories, subcats) which are merely blank both in DT and DTE.

I would love a recommendation for debugging, if you are willing to offer one:) I'm using VSCode and NotePad++. I've not had the advantage of a debugger since my Flash/ActionScript days lol...

shawngibson

Quick update here. Strangely (to me), given that all of my naming conventions remained identical, all I had to do to move from the two, disparate, internal category tables from within my Products and Tutorials databases, respectively, all I had to do was point the config.php files to the categories database, and now everything pulls from there, in both cases.

In other words, all of my Products config files point to "products", all of my Tutorials config files point to "tutorials" and in both cases, where I join then to category tables, these joined tables come from the categories database, with the config file pointing to "categories", as noted.

One tiny change, very nice:)

allan

Awesome - super to hear you've got it working now!

Allan