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
PHP Programmer - interactivetools.com
By Mikey - July 4, 2013
Greg - thank you!
Your solution did the trick and the records are loading right now.
You're the man!
Zick
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
PHP Programmer - interactivetools.com