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

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

That looks perfect,

I'll give it a go,

thanks Greg!!!