Need help listing "related products" on a Product page

Hey Dan,

I think something like this should work:

  //Load the first wine record from my section
  list($wineRecord, $wineMetaData) = getRecords(array(
    'tableName'   => 'wine',
    'where'       => "ams_status = 'active'",
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
    'orderBy'     => 'vintage DESC',
  ));
  $wineRecord = $wineRecord[0];

  //Get all of the category values from the category:values array, and implode them into a string
  $searchNums = implode(",",$wineRecord['field_that_has_multi_check_list:values']);

  //Return all matching wine categories with num values that are in the category:values array.
  $relatedWines = mysql_select('wine', "`ams_number` IN($searchNums)");

  showme($relatedWines);
  exit;


This is just example code, so you'll need to make a few changes to get it running (namely adding the field that stores multi check items).

So first use the getRecords function to pull the main wine record and add it to the $wineRecord variable.

getRecords will create an array of the related checkbox list items. It will be the field name with ":values" on the end, we can use this to create a comma seperated string of the AMS numer codes. 

Finally, you can use the mysql_select function to return the related wines. The first variable in the function is the table name, and the second is the where cause. 

The mysql IN function is used to search a single field for multiple values.

showme is a CMSB function that will show you the contents of a variable or array. 

Thanks!

Greg

Hi Greg,

Thanks for getting me pointed in the right direction. I'm still having a few issues, however.

The filename is product_related.php, and the record number gets determined by the AMS number at the end of the url, as in www.server.com/product_related.php?ams=CS10DH7

Here's the code I'm using to load the records at the top of the page (after calling in the main library).

<?php
  // load record from 'wines'
  list($winesRecords, $winesMetaData) = getRecords(array(
    'tableName'   => 'wines',
    'loadUploads' => true,
    'allowSearch' => true,
    'limit'      => '1',
  ));
  $winesRecord = @$winesRecords[0]; // get first record
  if (!$winesRecord) { dieWith404("Record not found!"); } // show error message if no record found
?>

It's easy for me to pull up a comma-separated list of the AMS number or database record number with the following:

Related Products (values): <?php echo join(', ', $winesRecord['related_products:values']); ?><br/>
Related Products (labels): <?php echo join(', ', $winesRecord['related_products:labels']); ?>

What I'd like to do instead is explode out the array so I can use the other fields and uploads for each of the (three) selected related products.  If I use the following on the page:

<?php 
  //Get all of the category values from the category:values array, and implode them into a string
  $searchNums = implode(', ', $winesRecord['related_products:values']);

  //Return all matching wine categories with num values that are in the category:values array.
  $relatedWines = mysql_select('wines', "'num' IN($searchNums)");

  showme($relatedWines);
  exit;
?>


Then I get the following error message:

MySQL Error: Unknown column 'CS10DE7' in 'where clause' - in mysql_functions.php on line 224 by mysql_select_query()

(CS10DE7 is the AMS# of one of the related wines)

One other thing to note - if possible, I'd like to filter the products (wines) by their ams_status (active or inactive) before they get displayed to the user making that selection, so they don't accidentally select an older product that is no longer for sale (and also to reduce the number of possible choices from hundreds to tens).

Any ideas? Am I going about this all wrong? Thanks again for your help!!!

My best,

-Dan

Hi Dan,

There are a couple of issues here, I've updated your code below:

  //Get all of the category values from the category:values array, and implode them into a string
  $searchNums = implode(', ', $winesRecord['related_products:values']);

  foreach($winesRecord['related_products:values'] as $key => $value){
    if($key == 0){
      $searchNums = "'$value'";
    }else{
      $searchNums .= ", '$value'";
    }
  }

  //Return all matching wine categories with num values that are in the category:values array.
  $relatedWines = mysql_select('wines', "`num` IN($searchNums)");

  showme($relatedWines);
  exit;


The first issue is that I'd assumed that the ams values were numeric, and didn't contain alpha characters. If you're using alpha characters in a value for MySQL, then they must have apostrophes around them. So I've had to use this foreach loop to add them around each value.

The second issue is that you didn't have the right type of apostrophe around the num field. If you're defining a field in a MySQL query, you should use the ones on the top left hand key on your keyboard which is below the ESC key.

I think there is something still wrong with your related wines mysql_select, because you're searching for the values in the num field, but this is used as the primary key for the CMSB database. So it can't contain the AMS key.

Hopefully this helps.

Cheers,

Greg