XPDO, Passing column from a join query to getChunk call.

85 Posts

mflammia Reply #1, 13 years, 8 months ago

Hi,

I have the following snippet below, which I want to do something simple with and that is to include the field 'Name' in the DestinationNames table that is part of the join query.

Bascially the output of $fields varible will look something like the below:

Array
(
[CarrID] => 0
[Area] => 01785
[Number] => 07822
[TierID] => 2
[CtryID] => 0
[keyfield] => 1245422
)

But I need it to include 'Name' so that I can pass it the getChunk call.

<?php
$querySet = "SET SQL_BIG_SELECTS = 1;";

$criteriaSet = new xPDOCriteria($modx->myDB, $querySet);
$criteriaSet->prepare();
$criteriaSet->stmt->execute();


$query = "SELECT NationalArea.*, DestinationNames.Name
		  FROM NationalArea
		  JOIN DestinationNames
		  ON NationalArea.Area=DestinationNames.Number
		  WHERE NationalArea.Number =  '01922'";

$criteria = new xPDOCriteria($modx->myDB, $query);

if ($results = $modx->myDB->getCollection('NationalArea', $criteria)) {
        foreach ($results as $result) {
            $fields = $result->toArray();
			echo '<pre>';
			print_r ($fields);
			echo '</pre>';
            $rows[] = $modx->getChunk('tpl.ShowDataNationalAreasOverView', $fields);
        }
 
        $ph['rows'] = implode('', $rows);
        $output = $modx->getChunk('tpl.ShowDataOuterNationalAreasOverview', $ph);
 
    } else {
        $output = $noresults;
    }

return $output;

Many thanks.

85 Posts

mflammia Reply #2, 13 years, 8 months ago

Actually I think the issue is in the getCollection call just specifying NationalArea - think I might need to use prepare() and execute() but not sure how to integrate this into my code yet - I'll have a play unless someone can come up with something

Thanks

85 Posts

mflammia Reply #3, 13 years, 8 months ago

Come across this post https://forums.modx.com/thread/35321/querying-a-non-modx-table-in-revolution and tried to implement it so my code now looks like this:

$querySet = "SET SQL_BIG_SELECTS = 1;";

$criteriaSet = new xPDOCriteria($modx->myDB, $querySet);
$criteriaSet->prepare();
$criteriaSet->stmt->execute();

$query = "SELECT NationalArea.*, DestinationNames.Name 
		  FROM NationalArea
		  JOIN DestinationNames 
		  ON NationalArea.Area=DestinationNames.Number
		  WHERE NationalArea.Number =  '".$_SESSION['areaCode']."'";

$criteria = new xPDOCriteria($modx->myDB, $query);

if ($criteria->prepare()) {
    if ($criteria->stmt->execute()) {
       if ($results= $criteria->stmt->fetchAll(PDO_FETCH_ASSOC)) {
         foreach ($results as $result) {
            $fields = $result->toArray();
			echo '<pre>';
			print_r ($fields);
			echo '</pre>';
            $rows[] = $modx->getChunk('tpl.ShowDataNationalAreasOverView', $fields);
          }
 
        $ph['rows'] = implode('', $rows);
        $output = $modx->getChunk('tpl.ShowDataOuterNationalAreasOverview', $ph);
 
        } else {
        $output = $noresults;
          }
    }
}

return $output;

But get the following error:

Notice: Use of undefined constant PDO_FETCH_ASSOC - assumed 'PDO_FETCH_ASSOC' in /home/sites/domain.co.uk/public_html/nhs/core/cache/includes/elements/modsnippet/41.include.cache.php on line 39 Warning: PDOStatement::fetchAll() expects parameter 1 to be long, string given in /home/sites/domain.co.uk/public_html/nhs/core/cache/includes/elements/modsnippet/41.include.cache.php on line 39 No items found.

85 Posts

mflammia Reply #4, 13 years, 8 months ago

Sorted it!

By removing the PDO_FETCH_ASSOC it then gets past that error but then gets stops at

Fatal error: Call to a member function toArray() on a non-object in /home/sites/domain.co.uk/public_html/nhs/core/cache/includes/elements/modsnippet/41.include.cache.php on line 41

I then changed the code as per below as the $results where already being output as an array:

if ($criteria->prepare()) {
    if ($criteria->stmt->execute()) {
      if ($results= $criteria->stmt->fetchAll()) {
        foreach ($results as $result) {
		  $rows[] = $modx->getChunk('tpl.ShowDataNationalAreasOverView', $result);
       }
 
       $ph['rows'] = implode('', $rows);
       $output = $modx->getChunk('tpl.ShowDataOuterNationalAreasOverview', $ph);
 
    } else {
        $output = $noresults;
    }
  }
}

MODX Staff
10,725 Posts

opengeek Reply #5, 13 years, 8 months ago

It's PDO::FETCH_ASSOC, not PDO_FETCH_ASSOC.

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