Using form post to an XPDO external DB query

85 Posts

mflammia Reply #1, 11 years, 4 months ago

Hi,

Just trying to get my head around using XPDO with a simple search query on a form. I know I am probably going about this all wrong but would appreciate some pointers

In my resource template I have the following snippet calls:

[[dbconnect]]
[[dbaccess]]

dbconnect has the folowing code

<?php
$host = 'IPAddress';
$username = 'username';
$password = 'password';
$dbname = 'dbname';
$port = 3306;
$charset = 'utf-8';
   
$dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
$xpdo = new xPDO($dsn, $username, $password);
   
echo $o = ($xpdo->connect()) ? 'Connected </br>' : 'Not Connected';

dbacess has the following code

$results = $xpdo->getCollection('LogData',array('callid' => 7382054));
 
$output = '';

if ($results) {
    foreach($results as $result) {
      $fields = $result->toArray();
      $output .= $modx->getChunk('ShowData', $fields);
  }
}
else {
    return 'No items found.';
}

return $output;

the chunk has the following:

<p>Call Id: [[+callid]]<br />
   Dialled Number: [[+diallednumber]]<br />
   Call Date: [[+calldate]]
</p>

This works fine................

Now I am trying to pass data from a formm entry to the DB query doing the following:

Resource template has the following:

[[$SearchFrom]]

The SearchFrom chunk has the following:

[[dbconnect]]
[[!dbformtest]]

<form method="post" action="[[~[[*id]]]]">
                <input type="text" name='search' id='search' value="[[+search]]" />
                <input type='submit' name='submit' id='submit' value='submit'/>
</form>

the dbformtest currently has:

<?php
if (isset($_POST['search'])) {
/*
if (!$xpdo->addPackage('nhs','/home/sites/paradoxal.co.uk/public_html/nhs/core/components/nhs/model/','')) {
    print 'There was a problem adding your package.';
};
*/

    echo "Search is set";

    $search = $_POST['search'];

    print_r ($search);

}

else {
 return 'Search is not set';

}

but whenever I comment the nested if statement I get a 500 server error. I've taken out the nested if and it also does not work. I'm not sure what I am doing wrong as the format seems to work for the previous example?

Many thanks.

This question has been answered by jgulledge19. See the first response.

discuss.answer

☆ A M B ☆
181 Posts

jgulledge19 Reply #2, 11 years, 4 months ago

First check your PHP error log and it will tell you lots of info. I think you also need to set your DB object to be global and not local. The easiest way to do this is to use the following for every snippet that would use your extra DB connection:

if (!isset($modx->myDB)) {
    // load your DB stuff:
    // could put in external file...
    $modx->myDB = new xPDO($dsn, $username, $password);
}
// then you can use it:

if (!$modx->myDB->addPackage('nhs','/home/sites/paradoxal.co.uk/public_html/nhs/core/components/nhs/model/','')) {
    print 'There was a problem adding your package.';
};

The question I have is do you have to have a second connection? Can you use the existing MODX DB connection?

MODX ebook | My Site

85 Posts

mflammia Reply #3, 11 years, 4 months ago

Thanks for posting.

Well I had it working, but broken it again and can't work out where I have gone wrong the error I get is as follows;

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in /home/sites/paradoxal.co.uk/public_html/nhs/core/xpdo/xpdo.class.php on line 1359

The snippet now looks like this:

if (!isset($modx->myDB)) {

$host = 'IPAdress';
$username = 'Username';
$password = 'Password';
$dbname = 'dbname';
$port = 3306;
$charset = 'utf-8';
   
$dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
$modx->myDB = new xPDO($dsn, $username, $password);
   
echo $o = ($xpdo->connect()) ? 'Connected </br>' : 'Not Connected';

}

if (isset($_POST['search'])) {
    $path = MODX_CORE_PATH . 'components/nhs/';
    if (!$modx->myDB->addPackage('nhs', $path . 'model/','')) {
      print 'There was a problem adding your package.';
    };

    $search = $_POST['search'];

    $results = $modx->myDB->getCollection('LogData',array('diallednumber' => $search));

    $output = '';

    if ($results) {
     foreach($results as $result) {
      $fields = $result->toArray();
       $output .= $modx->getChunk('ShowData', $fields);
     }
    }
    else {
     return 'No items found.';
    }

} else {
    $output = $modx->getChunk('SearchFrom');
}
return $output;

To answer your question, the database host is the same but the database name is different. Hadn't really thought about it, but guess I could use the MODX DB with a different prefix for the tables of my DB, I can just then use the addPackage without the connection information.

I was also wondering why I have to always specify the connection details in the same snippet. Was hoping to call it seperately so I dont have to keeping adding it to new snippets.

Tried added the addPackage to the dbconnect snippet but this did not work.

To add to this, am I right in thinking $modx-> extends $xpdo-> and why it can be used in its place?

Many thanks.

☆ A M B ☆
181 Posts

jgulledge19 Reply #4, 11 years, 4 months ago

The memory issue means that your query is getting to many results. Is it filled with a lot of data?

Maybe this will help: http://rtfm.modx.com/display/ADDON/CMPGenerator.Foreign+Databases.

*Edit - Fixed link [ed. note: jgulledge19 last edited this post 11 years, 4 months ago.]

MODX ebook | My Site

85 Posts

mflammia Reply #5, 11 years, 4 months ago

Hi,

Well it probably will have but I did get it to work once, plus I haven't yet submitted anything to the form so the query shouldn't run yet.

I'll have a bit more of a tinker to see what I did.

Just to let you know that link did not work because of the end full stop I think http://rtfm.modx.com/display/ADDON/CMPGenerator.Foreign+Databases

Thanks for you help.

☆ A M B ☆
2,475 Posts

Everett Reply #6, 11 years, 4 months ago

For what it's worth, I've usually found it easier to deal with the one database where MODX is installed because then you are only dealing with one instance of xPDO. You can, however, connect to other databases -- there's a bit about it here: http://rtfm.modx.com/display/xPDO20/Database+Connections+and+xPDO

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

85 Posts

mflammia Reply #7, 11 years, 4 months ago

Thanks Everett.

Sorted.

The

else {
    $output = $modx->getChunk('SearchFrom');
}

at the bottom of the snippet was creating a loop.

Answered Using form post to an XPDO external DB query