Load specific Category menu type records into a Multi menu type using category that calls to the database LIKE

5 posts by 2 authors in: Forums > CMS Builder
Last Post: July 5, 2013   (RSS)

By Mikey - July 3, 2013

I've got a multi (Menu Type) called "the_biltmore"

Here's the code for this record:

  // load record from 'the_biltmore'
  list($the_biltmoreRecords, $the_biltmoreMetaData) = getRecords(array(
    'tableName'   => 'the_biltmore',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $the_biltmoreRecord = @$the_biltmoreRecords[0]; // get first record
  if (!$the_biltmoreRecord) { dieWith404("Record not found!"); } // show error message if no record found

I also have category (Menu Type) called "wood_types". Within the "wood_types" I can create category records, give the record a name (for the type of wood) and upload an image the represents the wood's appearance.

Within the multi (Menu Type) called "the_biltmore" I have a Field Type called "wood_options" that gets List Options "Get options form database" as checkboxes (multi value). It has the following settings:

Section Tablename: wood_types
Use this field for option values: num
Use this field for option labels: name

So I can select various types of Wood options that are valuable for various products created under the multi (Menu Type) called "the_biltmore". Not every product under the_biltmore will have the same wood_types available.

So what I've trying to achieve is to load into the_biltmore products - the various associated "wood_types" details created in the category (menu type) which include the wood type's; name, description and a wood type image upload.

list($wood_typesRecords, $wood_typesMetaData) = getRecords(array(
    'tableName'   => 'wood_types',
    //'where'       => $where,
    //'where'       => "name LIKE '%\t".$the_biltmoreRecord['wood_options:labels']."\t%'",
    'where'       => "name LIKE '%\t".$the_biltmoreRecord['wood_options:values']."\t%'",
    //'where'       => "name LIKE '%".$the_biltmoreRecord['wood_options:labels']."%'",
    //'where'       => "name LIKE '%".$the_biltmoreRecord['wood_options:values']."%'",
        'loadUploads' => true,
        'allowSearch' => false,
  ));
  //$wood_typesRecord = @$wood_typesRecords[0]; // get first record
  // if (!$wood_typesRecord) { dieWith404("Record not found!"); } // show error message if no record found

Here's is the code where I'm trying to load this information into, but I've had no luck what so ever. If I remove the 'where' clause all the images uploaded to the "wood_types" category menu type appear regardless of their association with the_bilmore product and the "wood_options" category multi-checkboxes I created within "the_bilmore" to select the associated wood_types available for that product. I need to filter out and associate only the wood_options that are available for various the_biltmore products that I need to create records for within the multi-list.

<?php foreach ($wood_typesRecords as $record): ?>
    <h2><?php echo $record['name']; ?></h2>
        <?php echo $record['description']; ?>
<?php foreach ($record['wood_finish_image'] as $upload): ?>
          <img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" alt="<?php echo htmlencode($upload['info1']) ?> <?php echo htmlencode($upload['info2']) ?>" /><br/>
<?php endforeach ?>
<?php endforeach ?>

I suspect it has something to do with the fact that when I generate the code for the_biltmore I get this for the "wood_options" multi-checkboxes and the fact that I may need to explode these or something.

Wood Options (values): <?php echo join(', ', $the_biltmoreRecord['wood_options:values']); ?>

I've also tried wood_options:labels with not success either.

Anyone have any suggestions on how to get this working?

By gregThomas - July 4, 2013

Hi Zick,

This is a great post! I think I know what is causing the problem. 

As your wood_options field in the 'the_biltmore' section is storing the num as the value, you need to create a where statement that will only retrieve records with that num value from the wood_types section. 

I think you're close with your imploding method, I think your where statement should look like this:

$inString = implode(', ', $the_biltmoreRecord['wood_options:values']);

list($wood_typesRecords, $wood_typesMetaData) = getRecords(array(
  'tableName'   => 'wood_types',
  'where'       => "num IN($inString)",
  'loadUploads' => true,
  'allowSearch' => false,
));

So the inString variable will contain a comma separated list of num values selected from the wood_options field from the biltmore record. This string is then used in the where statement in an IN function. This will only retrieve records with a num value that is in the IN function. 

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By Mikey - July 4, 2013

Hey Greg... thanks again for the help.

So, I plugged in your solution:

$inString = implode(', ', $the_biltmoreRecord['wood_options:values']);

list($wood_typesRecords, $wood_typesMetaData) = getRecords(array(
  'tableName'   => 'wood_types',
  'where'       => "num IN($inString)",
  'loadUploads' => true,
  'allowSearch' => false,
));

...and this works great - if there is an option selected for within "the_biltmore" product record for the category "wood_options".. However, if a "wood_options" is not selected within the multi (Menu Type) called "the_biltmore" it produces the following MySQL Error message. So essentially at least one "wood_options" in the Field Type "list" must be selected from within the "the_biltmore" product record, or the error message is produced. Is there some sort of if statement I can use to check to see if a "wood_options" is selected, otherwise do nothing? Below are a few things I also tried within the category (Menu Type) called "wood_types", with no luck.

 MySQL Error: You have an error in your SQL syntax; check the manual that
 corresponds to your MySQL server version for the right syntax to use 
near ')) 
 ORDER BY name ASC' at line 3

I tried changing the ORDER BY name ASC to ORDER BY globalOrder and got the following MySQL Error message.

MySQL Error: You have an error in your SQL syntax; check the manual that
 corresponds to your MySQL server version for the right syntax to use 
near ')) 
 ORDER BY globalOrder' at line 3

I also tried ORDER BY dragSortOrder and got the following MySQL Error message.

MySQL Error: You have an error in your SQL syntax; check the manual that
 corresponds to your MySQL server version for the right syntax to use 
near ')) 
 ORDER BY dragSortOrder' at line 3

Again thanks for you help!

Zick

By gregThomas - July 5, 2013

Hi Zick,

You could get around this by wrapping the wood_types getRecord function in an if statement that checks if @$the_biltmoreRecord['wood_options:values'] exists. If it doesn't then $wood_typesRecords could be set to an empty array:


  if(@$the_biltmoreRecord['wood_options:values']){
    $inString = implode(', ', $the_biltmoreRecord['wood_options:values']);
    list($wood_typesRecords, $wood_typesMetaData) = getRecords(array(
      'tableName'   => 'blog',
      'where'       => "num IN($inString)",
      'loadUploads' => true,
      'allowSearch' => false,
    ));
  }else{
    $wood_typesRecords = array();
  }

So if no options are selected on the wood_options field a values array isn't created for it by getRecords. So if $the_biltmoreRecord['wood_options:values'] doesn't exist, we set $wood_typesRecords to an empty array. Otherwise the previous getRecords function creates the $wood_typesRecords array as before.

Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com