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 ?>

Hey Zaba,

It's great you found a solution, here is another method you could get the linked record nums:

 <?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

  //Convert the 'you might also like' num values into an array.
  $youMightLikeNums = explode("\t", trim($productsRecord['you_might_also_like'],"\t"));

  //If the product has linked you might also like products..
  if ($youMightLikeNums) {
    //Escape the values into a comma seperated string.
    $youMightLikeNums = mysql_escapeCSV($youMightLikeNums);
    
    //Pull the records with linked nums.
    list($relatedProductsRecords, $relatedProductsMetaData) = getRecords(array(
      'tableName'   => 'products',
      'loadUploads' => true,
      'where'       => "`num` IN($youMightLikeNums)",
      'allowSearch' => false,
      'debugSql'    => true,      // optional, display SQL query, defaults to no
    ));

}

This is sample code, so you might have to make a few changes to get it working.

The advantage of this method is that you're only loading the records from the database that are being used for related products.

Cheers,

Greg

Greg Thomas
PHP Programmer - interactivetools.com