Trying to query external DB via ModX snippet - can't do it!!

34 Posts

Funk247 Reply #1, 10 years, 10 months ago

I have a small snippet that I am attempting to use to return information from a database on the same server as Modx but seperate to the installation, I know its possible to import tables to the Modx DB and use xPDO to query them but that is excessive for what I need.

$user = "myUser";
$pass = "myPass";

$dsn = 'mysql:host=myServer;dbname=myDBName';
$DBH = new PDO($dsn, $user, $pass);

// set initial POST variable
$accNo = '';

// Check if form has been submitted and that the accNo exists in the db
if($_SERVER['REQUEST_METHOD'] == "POST") {

    if ($_POST['submit'] = 'Submit') {
        $accNo = $_POST['accNo'];

    // Define Query
    $query = $DBH->query("SELECT AccountNumber FROM tbl_accounts WHERE AccountNumber LIKE '$accNo'");
    // print_r ($query);

// Check to see if anythings been returned
if ($query) {
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
         print_r ($row['AccountNumber']);
     }
} else { 
    var_dump($query);
        echo "Nothing returned!";
    }
} 
}

This output precisely nothing, the query always returns a false value, it doesnt even run the

else { 
var_dump($query);
    echo "Nothing returned!";
}

Yet when I run the query manually on the DB it works properly, have I missed something elementary somewhere?

MODX Staff
10,725 Posts

opengeek Reply #2, 10 years, 10 months ago

You problem is a simple syntax error:

if ($_POST['submit'] = 'Submit') {
        $accNo = $_POST['accNo'];

You are assigning the value 'Submit' to the $_POST['submit'] variable instead of comparing it's value to the string.

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

34 Posts

Funk247 Reply #3, 10 years, 10 months ago

Quote from: opengeek at Jul 02, 2013, 01:56 PM

You problem is a simple syntax error:
if ($_POST['submit'] = 'Submit') {
        $accNo = $_POST['accNo'];
You are assigning the value 'Submit' to the $_POST['submit'] variable instead of comparing it's value to the string.

Oo I missed that, well spotted... It wasn't the cause of my problem, I was working with an older version of the DB and searching for an account number that didnt exist.

I have a different problem now. The snippet works on my testbed installation of modx, but when I've added it to the live site I've found that the snippet isn't even being loaded! Even when I create a simple return ("Hello World!"); nothing appears! Why could this be?

☆ A M B ☆
2,475 Posts

Everett Reply #4, 10 years, 10 months ago

Check your PHP logs and see if there's something going on there. You may want to review this page: http://rtfm.modx.com/display/xPDO20/Database+Connections+and+xPDO

If you don't establish a connection there, you can log an error (see examples of error logging here: http://rtfm.modx.com/display/revolution20/How+to+Write+a+Good+Snippet)

Hope that helps.

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

11 Posts

itcassy Reply #5, 10 years, 3 months ago

I am a new user and I really need some help. I am also trying to upgrade my skills to use pdo. I am not sure if this is a good thread to put this in or not. I have been searching the forums for days now trying to find my exact example and i just can't understand why I cannot get this to work. I can get this exact example to work using my old mysql code, but not when using pdo. I am trying to connect to a separate database in a snippet to retrieve info. I do not want to have to type the connection with the username and password every time I want to connect. I would like to put the connection in a different snippet (or even external file ) and include that instead of the actual connection. My code works fine when it is all in one snippet, but when i take the connection part out and call it from somewhere else, it does not work. It seems that the variables go out of scope whenever they are not in the same snippet. I apologize if that is not correct, but that is what is seems to me.

Test Snippet - Works fine

$dsn = 'mysql:dbname=db_name;host=localhost';
$user = 'user';
$password = 'pass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    die();
}

$stmt = $dbh->query('SELECT * from table');
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $name=$row['name'];
      echo '<a href="#">'.$name.'</a><br />';
    }

Does NOT work when I put the connection info into a different snippet and then try to reference (via runsnippet or include.

dbConnect Snippet

$dsn = 'mysql:dbname=db_name;host=localhost';
$user = 'user';
$password = 'pass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    die();
}

Test Snippet

$modx->runSnippet('dbConnect');

$stmt = $dbh->query('SELECT * from table');
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $name=$row['name'];
      echo '<a href="#">'.$name.'</a><br />';
    }

This returns:
Fatal error: Call to a member function query() on a non-object in ...

I have tried everything. I know this should be super simple, and I honestly have been looking for posts for a long time now. Any help or topic redirection would be helpful and I would be beyond grateful.

24,544 Posts

BobRay Reply #6, 10 years, 3 months ago

Try putting the connection code in the test snippet. I don't think it has any way of accessing the $dbh variable (and runSnippet() will always return a string, so you can't use its return value).

I'm surprised that it doesn't work with 'include', though.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

MODX Staff
10,725 Posts

opengeek Reply #7, 10 years, 3 months ago

You could use a service class to wrap your PDO instance. That way you can simply call $modx->getService('db', 'MyDbClass', '/path/to/model/class/') and access it via $modx->db in any snippet as a singleton (i.e. will only need to be created once and can be used as many times per request as necessary without additional overhead).

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

☆ A M B ☆
70 Posts

christianseel Reply #8, 8 years, 10 months ago

Hi Jason, that sounds pretty cool! I tried that, but I'm not sure if I'm on the right way. This is how my class currently looks… Should that work? Could you point me to the right direction?

class MyDb extends PDO {
    private $config = array(
        'host' => 'localhost',
        'dbname' => 'form_db',
        'port' => '3306',
        'username' => 'user',
        'passowrd' => '???'
    );

    function __construct($options = array()) {
        $dsn = 'mysql:host='.$this->config['host'].';dbname='.$this->config['dbname'].';port='.$this->config['port'].';charset=utf-8';
        parent :: __construct(
            $dsn,
            $this->config['username'],
            $this->config['password'],
            $options,
            array ()
        );
    }
}

chsmedien - Digital Marketing Agency
MODX Professional & Ambassador

http://chsmedien.com | http://twitter.com/christianseel

65 Posts

Vasiliy Naumkin Reply #9, 8 years, 10 months ago

Here is an example of work with any database from any snippet:

<?php
$dsn = implode(';', array(
	$config['dbtype'] . ':host=' . $config['host'],
	'dbname=some_database',
	'charset=' . $config['charset'],
));
$pdo = new PDO($dsn, $config['username'], $config['password']);

$sql = "SELECT * FROM `some_table`";
$c = $pdo->prepare($sql);
if (!$c->execute()) {
	$modx->log(modX::LOG_LEVEL_ERROR, print_r($c->errorInfo(), true));
}
else {
	print_r($c->fetchAll(PDO::FETCH_ASSOC));
}