Show Related items

6 posts by 2 authors in: Forums > CMS Builder
Last Post: September 20, 2012   (RSS)

By Toledoh - September 19, 2012

Hi Guys,

What am I doing wrong here.

I have a table called "curriculum"
Within this, I have a field called "additional_items" which is a multi-value pulldown looking at the "additions" table with values: num and labels: title

I'm trying to list all "additional items" that have been selected via the pull-down.

// load detail record from 'curriculum'
list($curriculumRecords, $curriculumMetaData) = getRecords(array(
'tableName' => 'curriculum',
'where' => whereRecordNumberInUrl(1), // If no record # is specified then latest record is shown
'loadUploads' => true,
'allowSearch' => true,
'limit' => '1',
));
$detailRecord = @$curriculumRecords[0]; // get first record
if (!$detailRecord) { dieWith404("Record not found!"); } // show error message if no record found


// load records from 'additions'
list($additionsRecords, $additionsMetaData) = getRecords(array(
'tableName' => 'additions',
'where' => mysql_escapef('num = ?', $detailRecord[' additional_items']),
'loadUploads' => true,
'allowSearch' => true,
));

With the standard display:

<?php foreach ($additionsRecords as $record): ?>
Record Number: <?php echo htmlencode($record['num']) ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
Content: <?php echo $record['content']; ?><br/>
_link : <a href="<?php echo $record['_link'] ?>"><?php echo $record['_link'] ?></a><br/>
<hr/>
<?php endforeach ?>

<?php if (!$additionsRecords): ?>
No records were found!<br/><br/>
<?php endif ?>

OR... should I be using somethign like

list($additionsRecords, $additionsMetaData) = getRecords(array(
'tableName' => 'additions',
'where' => "num LIKE '%\t".intval($curriculumRecords['additional_items'])."\t%'",
'loadUploads' => true,
'allowSearch' => true,
));
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] Show Related items

By gregThomas - September 20, 2012

Hi Tim,

I would personally do something similar to your second option, maybe something similar to this method:

<?php

// load detail record from 'curriculum'
list($curriculumRecords, $curriculumMetaData) = getRecords(array(
'tableName' => 'curriculum',
'where' => whereRecordNumberInUrl(1), // If no record # is specified then latest record is shown
'loadUploads' => true,
'allowSearch' => true,
'limit' => '1',
));
$detailRecord = @$curriculumRecords[0]; // get first record
if (!$detailRecord) { dieWith404("Record not found!"); } // show error message if no record found

//create a string that can be used to search the additions table. Uses the value array for additional_items.
$additionString = implode(',',$detailRecord['additional_items:values']);

list($additionsRecords, $additionsMetaData) = getRecords(array(
'tableName' => 'additions',
'where' => "num IN ($additionString)",
'loadUploads' => true,
'allowSearch' => true,
));
?>
<?php foreach ($additionsRecords as $record): ?>
Record Number: <?php echo htmlencode($record['num']) ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
Content: <?php echo $record['content']; ?><br/>
_link : <a href="<?php echo $record['_link'] ?>"><?php echo $record['_link'] ?></a><br/>
<hr/>
<?php endforeach ?>

Let me know if you have any problems implementing this code.

Thanks!
Greg Thomas







PHP Programmer - interactivetools.com

Re: [greg] Show Related items

By Toledoh - September 20, 2012

Perfect! Thanks Greg.

BTW. Welcome!
Cheers,

Tim (toledoh.com.au)

Re: [greg] Show Related items

By Toledoh - September 20, 2012

Hey Greg,

If a record doesn't have any additional items associated with it, I get an error...

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 DESC' at line 3

Any way to say, "if no additional items... don't list them"?
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] Show Related items

By gregThomas - September 20, 2012

Hi Tim,

I would throw an if statement around the getRecords function that checks if there is any numbers in the first value for $detailRecord['additional_items:values']. This should work:

//check to see if there is anything in the first item in the array that is greater than 1
if($detailRecord['additional_items:values'][0] > 0){
//create a string that can be used to search the additions table. Uses the value array for additional_items.
$additionString = implode(',',$detailRecord['additional_items:values']);

list($additionsRecords, $additionsMetaData) = getRecords(array(
'tableName' => 'additions',
'where' => "num IN ($additionString)",
'loadUploads' => true,
'allowSearch' => true,
));
}

Let me know if it doesn't work.

Thanks!

Greg
Greg Thomas







PHP Programmer - interactivetools.com