filter listing with 2 combined tables
4 posts by 2 authors in: Forums > CMS Builder
Last Post: August 13, 2013 (RSS)
By s2smedia - August 12, 2013
I have a list of communities from one table here:
http://questbuilders.com/test/New-Homes/Homes/
if you click on first community:
http://questbuilders.com/test/New-Homes/Homes/community.php?Majestic-Estates-1
I need the list of models shown on this page to show only models with the community=Majestic Estates
right now my code is always listing ALL models:
<?php foreach ($modelsRecords as $record): ?>
<tr style="background-image:url(images/tealtrans.png); color:#148C8C">
<td style="font-weight:normal"><?php echo htmlencode($record['title']) ?></td>
<td><?php echo htmlencode($record['description']) ?></td>
<td><?php echo htmlencode($record['starting_at']) ?></td>
<td> <?php foreach ($record['elevation_floor_plan'] as $index => $upload): ?>
<a href="http://questbuilders.com/admin<?php echo $upload['urlPath'] ?>" target="_blank" style="color:#000">Download PDF</a>
<?php endforeach ?></td>
</tr>
<?php endforeach ?>
Hi,
How have you got the relationship between the community and models tables set up? If you have used this method:
http://www.interactivetools.com/kb/article.php?Populate-a-list-field-from-another-section-15
and have the num field for the value of the list field, then you should be able to filter based on the value in the URL like this:
$numValue = getLastNumberInUrl('1');
// load records from '_nlb_subscribers'
list($modelsRecords , $modelsMetaData) = getRecords(array(
'tableName' => 'models ',
'where' => "`community` = '$numValue'",
'loadUploads' => true,
'allowSearch' => false,
));
This is just example code, so you'll have to make a few changes to get it working with your site.
So get getNumValueFromUrl function will retrieve the last number from the URL. Then this cab used to filter on the community field in the getRecords function.
This should only return items with a community value that matches the num value passed in the URL.
Let me know if you have any questions.
Thanks
Greg
PHP Programmer - interactivetools.com
By s2smedia - August 12, 2013
hmm.. now no models show up..
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
$numValue = getLastNumberInUrl('0');
// load records from 'models'
list($modelsRecords, $modelsMetaData) = getRecords(array(
'tableName' => 'models',
'loadUploads' => true,
'where' => "'community' = '$numValue'",
'allowSearch' => false,
));
?>
// load record from 'homes'
list($homesRecords, $homesMetaData) = getRecords(array(
'tableName' => 'homes',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$homesRecord = @$homesRecords[0]; // get first record
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Homes | Quest Builders : New Homes : Custom Home Builders Marlton New Jersey</title>
<link href="../css/main.css" rel="stylesheet" type="text/css" />
<link rel="shortcut icon" type="image/x-icon" href="../favicon.ico">
<link href='http://fonts.googleapis.com/css?family=Oswald:400,300,700' rel='stylesheet' type='text/css'>
<style type="text/css">
html {
background: url(../images/bg1.jpg) no-repeat center center fixed;
}
</style>
</head>
<body>
<div class="portalbar">
<div class="wrapper">
<div class="homelogo"><a href="../../index.php"><img src="../images/logo.jpg" width="165" height="163" alt="Quest Builders" border="0" /></a></div>
<div class="portalnav">
<a href="../About" style="text-decoration:none; color:#FFF">
<div class="navwrap">
<div class="portalnavitem">ABOUT</div>
</div>
</a>
<div class="navwrap_active">
<div class="portalnavitem">HOMES</div>
</div>
<a href="../Portfolio" style="text-decoration:none; color:#FFF">
<div class="navwrap">
<div class="portalnavitem">PORTFOLIO</div>
</div>
</a>
<a href="../Testimonials" style="text-decoration:none; color:#FFF">
<div class="navwrap">
<div class="portalnavitem">TESTIMONIALS</div>
</div>
</a>
<a href="../Contact-Us" style="text-decoration:none; color:#FFF">
<div class="navwrap">
<div class="portalnavitem">CONTACT US</div>
</div>
</a>
<div style="clear: both;"></div></div>
</div>
</div>
<div class="subcontent"><div class="subwrapper">
<div class="subheader"><a href="../Homes" style="text-decoration:none; color:#000">Back to Homes</a></div>
<?php foreach ($homesRecord['image'] as $index => $upload): ?>
<div class="communityimage" style="background-image:url(<?php echo $upload['urlPath'] ?>)"></div>
<?php endforeach ?>
<div class="subheader22"><?php echo htmlencode($homesRecord['community']) ?><br />
<span style="font-size:14px; font-weight:lighter"><?php echo htmlencode($homesRecord['blurb']) ?></span>
</div>
<table width="100%" border="0" cellspacing="2" cellpadding="15">
<tr>
<td style="font-size:18px">Model</td>
<td style="font-size:18px">Description</td>
<td style="font-size:18px">Starting At</td>
<td style="font-size:18px">Elevations & Floor Plans</td>
</tr>
<?php foreach ($modelsRecords as $record): ?>
<tr style="background-image:url(images/tealtrans.png); color:#148C8C">
<td style="font-weight:normal"><?php echo htmlencode($record['title']) ?></td>
<td><?php echo htmlencode($record['description']) ?></td>
<td><?php echo htmlencode($record['starting_at']) ?></td>
<td> <?php foreach ($record['elevation_floor_plan'] as $index => $upload): ?>
<a href="http://questbuilders.com/admin<?php echo $upload['urlPath'] ?>" target="_blank" style="color:#000">Download PDF</a>
<?php endforeach ?></td>
</tr>
<?php endforeach ?>
<tr>
<td colspan="4"> </td>
</tr>
</table>
</div></div>
<div class="footer"><?php include '../../includes/footer.php'?></div>
</body>
</html>
Hi,
It looks as if you have the wrong type of brackets around your community field, you need to use this type:
'where' => "`community` = '$numValue'",
It's the apostrophe that is above the tab button on the left of your keyboard. Also are you storing the num value in your community field? As the example code i gave you assumed that this is what is being stored.
Thanks!
Greg
PHP Programmer - interactivetools.com