Using modx database connection

$result = @mysqli_query($myconnection, $sql);

$xpdo->addPackage

$modx->addPackage

$sql = "SELECT P.product_id, P.title AS product, 
               V.description AS variation,
	       PV.product_code AS product_code,
	       V.variation_id AS variation_id
          FROM products as P
	  INNER JOIN product_codes AS PV 
               ON PV.product_id = P.product_id
	  INNER JOIN variation as V
	       ON V.variation_id = PV.variation_id
	  WHERE P.product_group_id = $productGroupId
	       ORDER BY P.product_id "; 

<?php
$stmt = $modx->query("SELECT P.product_id, P.title AS product, 
               V.description AS variation,
	       PV.product_code AS product_code,
	       V.variation_id AS variation_id
          FROM products as P
	  INNER JOIN product_codes AS PV 
               ON PV.product_id = P.product_id
	  INNER JOIN variation as V
	       ON V.variation_id = PV.variation_id
	  WHERE P.product_group_id = $productGroupId
	       ORDER BY P.product_id ";);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // do stuff with the $row here
}

$c = $modx->newQuery('Products');
$c->innerJoin('ProductCodes','PV','PV.product_id = Products.product_id');
$c->innerJoin('Variation','V','V.variation_id = PV.variation_id');
$c->where(array(
   'Products.product_group_id' => 1,
));
$c->sortby('Products.product_id','ASC');

$products = $modx->getCollection('Products',$c);
$output .= '<p>Total: '. count($products) . '</p>';

foreach($products as $product) {
   $output .= '<p>Size imp: ' . $product->get('size_imp');
   $output .= '<br />Code: ' . $product->get('product_code');
   $output .= '<br />Author: ' . $product->get('description') . '<br /></p>';
}
return $output;