Database connection reuse

73 Posts

neo2.0 Reply #1, 14 years, 3 months ago

Hey everybody,

I have a number of snippets that do the writing of some values to tables in a different db from modx's, but on the same server. Each of them starts with some code to make a db connection, then goes on:

$host = 'localhost';
$port = 3306; 
$database = 'inventar';
$username = 'user';
$password = 'pass';

$dsn = "mysql:host=$host;port=$port;dbname=$database";
$db = new xPDO($dsn, $username, $password);
$db->addPackage('Inventardatenbank','/var/www/core/components/Inventardatenbank/model/','');

------ here the useful code starts ------

if (! $cd = $db->getObject('CDs', $_GET['cdnr']) ) {
    return 'CD ' . $cdnr . ' not found';
}
...

I know that's ugly, redundant and unmaintainable. But what I don't know is how to best streamline the code! Maybe it would be coolest to make an object that represents the db connection, an object immediately accessible from my snippets, so that I can start my snippets with useful code from line 1.

Is that doable? -- How?
Or can I reduce the non-useful code to a line of "include(...);"? -- What would the includes have to look like?
Or maybe a snippet call that return said object? -- What would such a snippet have to look like?

Is there a canonical way of doing these things?

Thanks!

PHP illiterate MODx enthusiast

MODX Staff
10,725 Posts

opengeek Reply #2, 14 years, 3 months ago

Create one snippet the you will use on each Resource where you need the additional connection, or call a snippet to create the connection if it doesn't exist. This snippet can store the connection in a placeholder (if it doesn't already exist) which you can then retrieve from any Snippet that follows (or calls) your connection Snippet.

/* a connection snippet */
$db = $modx->getPlaceholder('inventar.db');
if (!($db instanceof xPDO)) {
    $host = 'localhost';
    $port = 3306; 
    $database = 'inventar';
    $username = 'user';
    $password = 'pass';

    $dsn = "mysql:host=$host;port=$port;dbname=$database";
    $db = new xPDO($dsn, $username, $password);
    $db->addPackage('Inventardatenbank','/var/www/core/components/Inventardatenbank/model/','');
    $modx->setPlaceholder('inventar.db', $db);
}
return '';

/* a snippet that uses the connection snippet */
$modx->runSnippet('getInventarConnection');
$db = $modx->getPlaceholder('inventar.db');
if (!empty($db)) {
    if (!($cd = $db->getObject('CDs', $_GET['cdnr']))) {
        return 'CD ' . $cdnr . ' not found';
    }
}

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

73 Posts

neo2.0 Reply #3, 14 years, 3 months ago

Thanks, Jason, that was just what I was looking for! Coding feels much easier with the xPDO interface.

And your snippet proposals work almost flawlessly! laugh

Just one hiccup:

The following code is the beginning of a snippet that uses the connection-in-a-placeholder. The content of $rechnungen is expected to be a collection of objects that I can process further via foreach ( $rechnungen as rechnung ), right?

$modx->runSnippet('getInventarConnection');
$db = $modx->getPlaceholder('inventar.db');
if (!empty($db)) {
    $rechnungen = $db->getCollection('Rechnungen');
    print "<pre>"; print_r($rechnungen); print "</pre>";

But print_r reveals that something must have gone wrong, I guess. The following is just the beginning of an output of more than 600 lines, while at the time of query, there is one single line in the database table.

Array
(
    [1] => Rechnungen_mysql Object
        (
            [xpdo] => xPDO Object
                (
                    [pdo] => PDO Object
                        (
                        )

                    [config] => Array
                        (
                            [table_prefix] => 
                            [dbtype] => mysql
                            [host] => localhost
                            [port] => 3306
                            [dbname] => inventar
                            [dsn] => mysql:host=localhost;port=3306;dbname=inventar

And, of course, all my

foreach ($rechnungen as $rechnung ) {
  $rechnung->ColumnNameHere
}

'queries' turn up nothing. What is missing here? [ed. note: neo2.0 last edited this post 14 years, 3 months ago.]

PHP illiterate MODx enthusiast

73 Posts

neo2.0 Reply #4, 14 years, 3 months ago

Wait, what.

A completely functional $db-getObject(...); call returns a similarly big and complicated array when print_r-ed.

Hold on, I'll have a look at it. Jason's code seems to be working better than I thought.

EDIT: D'oh. Silly me. I must say that

$rechnung->get('ColumnNameHere')

works much better than

$rechnung->('ColumnNameHere')

or

$rechnung->ColumnNameHere

[ed. note: neo2.0 last edited this post 14 years, 3 months ago.]

PHP illiterate MODx enthusiast