4 posts by 2 authors in: Forums > CMS Builder
Last Post: July 19   (RSS)

Help with the query please?

 <?php
 // load record from 'products'
  list($productsRecords, $productsMetaData) = getRecords(array(
    'tableName'   => 'products',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $productsRecord = @$productsRecords[0]; // get first record
  if (!$productsRecord) { dieWith404("Record not found!"); } // show error message if no record found

  $listed=$productsRecord['you_might_also_like'];
//This is a tab separated array of related products using the num as value.

//I want to search the product table to list only those with the num that matches the num from $listed.

//related products
	 list($relatedProductsRecords, $relatedProductsMetaData) = getRecords(array(
    'tableName'   => 'products',
	'loadUploads' => true,
	'where' => ?,
    'allowSearch' => false,
    'debugSql'           => true,      // optional, display SQL query, defaults to no

  ));


//Then on the page I want to display each one...

?>
			<?php foreach ($relatedProductsRecords as $record): ?>
			<h3><?php echo htmlencode($record['title']) ?></h3>
			<?php endforeach ?>

OK,

I think I sussed out a solution....

rather than do the filter at sql level, I have done it on the results, here is how I have achieved this, not sure how efficient it is though.

 <?php
 // load record from 'products'
  list($productsRecords, $productsMetaData) = getRecords(array(
    'tableName'   => 'products',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $productsRecord = @$productsRecords[0]; // get first record
  if (!$productsRecord) { dieWith404("Record not found!"); } // show error message if no record found


//related products
	 list($relatedProductsRecords, $relatedProductsMetaData) = getRecords(array(
    'tableName'   => 'products',
	'loadUploads' => true,
    'allowSearch' => false,
  ));

?>
<?php $listed=$productsRecord['you_might_also_like']; //tab delimited array of num ?>
<?php $theRecords= explode("\t",$listed); //explode the array on tab ?>

Then pull out only the matching ones from the query of the records.


			<?php foreach ($relatedProductsRecords as $record): ?>
			<?php foreach ($theRecords as $item): ?>
			<?php $itemTrim=trim($item); ?>
				<?php if ($itemTrim==$record['num']):?>
			
				<a class="item" href="<?php echo $record['_link'] ?>">

				<?php foreach ($record['image_gallery'] as $index => $upload): ?>
		<?php if ($index >= 1) { continue; } // limit uploads shown ?>
        <img alt="<?php echo htmlencode($record['title']) ?>" title="<?php echo htmlencode($record['title']) ?>" src="<?php echo $upload['thumbUrlPath2']; ?>"/>
                 <?php endforeach ?>

        <h3><?php echo htmlencode($record['title']) ?></h3>
         </a>



			<?php endif ?>
			<?php endforeach ?>
			<?php endforeach ?>

That looks perfect,

I'll give it a go,

thanks Greg!!!