Filter Listings by category

7 posts by 2 authors in: Forums > CMS Builder
Last Post: February 6, 2013   (RSS)

By gversion - February 4, 2013

Hello,

I have two database tables, one for listings (cms_listings) and one for categories (cms_categories).

I have a detail page for the category and on this page I would like to list all the listings that have been assigned to that category.

Here's the code for the top of my category detail page:

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  /* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
  
  // load viewer library
  $libraryPath = 'cmsAdmin/lib/viewer_functions.php';
  $dirsToCheck = array('/home/demo/public_html/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

  // load record from 'categories'
  list($categoriesRecords, $categoriesMetaData) = getRecords(array(
    'tableName'   => 'categories',
    'where'       => whereRecordNumberInUrl(0),
    'joinTable'   => 'listings',
    'loadUploads' => false,
    'allowSearch' => false,
    'limit'       => '1',
  ));

  $categoriesRecord = @$categoriesRecords[0]; // get first record
  if (!$categoriesRecord) { dieWith404("Record not found!"); } // show error message if no record found

?>

Am I right in thinking I need to joinTable => 'listings' and then I can run a foreach loop to display each of the listings from the current category?

                                <?php foreach ($categoriesRecords as $record): ?>
                                <tr>
                                      <td class="name"><?php echo htmlencode($record['product']) ?></td>
                                      <td class="manufacturer"><?php echo $record['manufacturer'] ?></td>
                                      <td class="price"><?php echo htmlencode($record['sale_price']) ?></td>
                                      <td class="details"><a href="<?php echo $record['_link'] ?>" class='more'></a></td>
                                </tr>
                                <?php endforeach ?>

The above loop doesn't work as it just returns an "undefined index" error so I guess I am not calling the variables from the 'listings' table correctly.

Could someone please give me a hand understanding how this should work?

Thank you,

Greg

By gregThomas - February 4, 2013

Hi Greg,

Are you using variables from both the categories record and the listings records? If you are I would do something like this:

  /* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
  
  // load viewer library
  $libraryPath = 'cmsAdmin/lib/viewer_functions.php';
  $dirsToCheck = array('/home/demo/public_html/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

  // load record from 'categories'
  list($categoriesRecords, $categoriesMetaData) = getRecords(array(
    'tableName'   => 'categories',
    'where'       => whereRecordNumberInUrl(0),
    'joinTable'   => 'listings',
    'loadUploads' => false,
    'allowSearch' => false,
    'limit'       => '1',
  ));

  $categoriesRecord = @$categoriesRecords[0]; // get first record
  if (!$categoriesRecord) { dieWith404("Record not found!"); } // show error message if no record found

  $catNum = $categoriesRecord['num'];

   // load record from 'categories'
  list($listingsRecords, $categoriesMetaData) = getRecords(array(
    'tableName'   => 'listings',
    'where'       => "nameOfAssociatedListField = '$catNum'",
    'allowSearch' => false,
  ));

So the second getRecords function will only return items that have the category number stored in the nameOfAssociatedListField field. 

You will need to change 'nameOfAssociatedListField' to the name of the field in the listings section that stores the category for each listing. 

Then you could display the contents of the listings section like this:

<?php foreach ($listingsRecordsas $record): ?>
<tr>
    <td class="name"><?php echo htmlencode($record['product']) ?></td>
    <td class="manufacturer"><?php echo $record['manufacturer'] ?></td>
    <td class="price"><?php echo htmlencode($record['sale_price']) ?></td>
    <td class="details"><a href="<?php echo $record['_link'] ?>" class='more'></a></td>
  </tr>
<?php endforeach ?>

Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By gversion - February 4, 2013

Hi Greg,

That's worked perfectly, thank you so much for clear instructions!

Regards,

Greg

By gversion - February 4, 2013 - edited: February 4, 2013

Hi Greg,

One more question regarding this topic, can you tell me how to get the following fields to work please?

<strong><span>RESULTS</span> <?php echo $listingsMetaData['pageResultsStart'] ?>-<?php echo $listingsMetaData['pageResultsEnd'] ?> <span>OF</span> <?php echo $listingsMetaData['totalRecords'] ?></strong>

I'd like this to display these three variables so I can show the number of listings displayed in the specific category.

Currently I am getting "Undefined variable" error messages.

Thanks again,

Greg

By gregThomas - February 5, 2013

Hi Greg,

I think you need to modify your code so that it looks like this:

$catNum = $categoriesRecord['num'];   

// load record from 'categories'  
list($listingsRecords, $listingsMetaData) = getRecords(array(
    'tableName'   => 'listings',
    'where'       => "nameOfAssociatedListField = '$catNum'",
    'allowSearch' => false,
));


Currently the listings meta data is being stored in the variable $categoriesMetaData, if you change the name to $listingsMetaData that should fix the issue.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By gregThomas - February 6, 2013

Hi Greg,

I'm not 100% sure what the correct variable name is that you need to use. Could you post the output of the following code back into a forum post?

  // load record from 'listings'
  list($listingsRecords, $listingsMetaData) = getRecords(array(
    'tableName'   => 'listings',
    'joinTable'   => 'categories',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => true,
    'useSeoUrls'  => true,
    'limit'       => '1',
  ));
  $listingsRecord = @$listingsRecords[0]; // get first record

  showme($listingsRecord);
  exit;

  if (!$listingsRecord) { dieWith404("Record not found!"); } // show error message if no record found

  $catName = $listingsRecord['categories.name'];

   // load record from 'categories'
  list($categoriesRecords, $listingsMetaData) = getRecords(array(
    'tableName'   => 'categories',
    'where'       => "name = '$catName'",
    'allowSearch' => false,
  ));

You just need to add the lines in green to your current code.    

This should output all of the variables in the $listingsRecords array, and give me an idea of what the correct variable name is.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com