[RESOLVED] Access another database with xPDO

833 Posts

treigh Reply #1, 12 years, 1 month ago

Hi guys, I read a lot of posts dealing with the topic, but most are very old and though they provided a guideline, I feel like I still need some help.

Essentially I want to randomly retrieve 1 row of data from a database hosted on the same server as MODX and display the data in the front end. I've written a small snippet to do that. I hope my baby steps won't bother you much. Here's where I'm standing, what am I doing wrong?

<?php
$dsn = 'mysql:host=HOSTIP;dbname=my_db;port=3306;charset=utf-8';
$xpdo = new xPDO($dsn,'my_db_usr','my_db_pwd');

//RANDOMLY SELECT 1 ROW OF DATA FROM THE versets TABLE
$query = $xpdo->query("SELECT * FROM `versets` ORDER BY `RAND()`");
$query->limit(1);

  while ($row = $query->fetch(PDO::FETCH_ASSOC))
  {
	// ONLY OUTPUT SPECIFIC COLUMNS
	echo $row['id'] . " " . $row['ch'] . ":" . $row['vst'] . " " . $row['darby'];
	echo "
";
  }
  
?>

[ed. note: treigh last edited this post 12 years, 1 month ago.]

A MODx Fanatic

88 Posts

lazylegs Reply #2, 12 years, 1 month ago

You are mixing xPDO and PDO,

The $xpdo->query() method is just PDO like you have used it, but then you try to mix xPDO limit() to it. So just add the limit to your SQL string instead of calling the method. Basicly you are using "fallback" option of using regular PDO and none of the xPDO options really.

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

833 Posts

treigh Reply #3, 12 years, 1 month ago

Thx for clarifying for me.Let me spend some more time on this

A MODx Fanatic

MODX Staff
10,725 Posts

opengeek Reply #4, 12 years, 1 month ago

<?php
$query = $xpdo->query("SELECT * FROM `versets` ORDER BY RAND() LIMIT 1");
$row = $query->fetch(PDO::FETCH_ASSOC);
$query->closeCursor();
// ONLY OUTPUT SPECIFIC COLUMNS
echo $row['id'] . " " . $row['ch'] . ":" . $row['vst'] . " " . $row['darby'];
echo "";

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

833 Posts

treigh Reply #5, 12 years, 1 month ago

@opengeek, Thanks alot! I guess the while statement wasn't necessary.

A MODx Fanatic