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 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 gversion - February 5, 2013

Hi Greg,

That seems to have done the job, thank you! I actually found another workaround which was to change the variable to "$categoriesMetaData1", but I think your way is cleaner so I am really grateful for that.

One more thing if I may:

On my product detail page, I would like to have a breadcrumb navigation so user can see which category the product is in and then click on a category name to view the listings in that specific category.

My code is currently as follows:

  // 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

  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,
  ));

  // create parent categories for breadcrumb navigation
  $parentlinks = '';
  $currentcat = $listingsRecord['category'] ;

  if ($currentcat>0)
    {
      list($categoryRecords) = getRecords(array('tableName'   => 'categories',    'where'    => " Num=$currentcat",'orderBy'    => 'name',  ));
      $parentcat = @$categoryRecords[0]['parentNum'];

        while ($parentcat>0)
        {
           list($categoryRecords) = getRecords(array('tableName'   => 'categories',    'where'    => " Num=$parentcat",'orderBy'=> 'name',));
           $parentlinks =  '<li><a href="/category/'.$categoryRecords[0]['name'].'/'.$categoryRecords[0]['num'].'">' . $categoryRecords[0]['name'] .'</a></li>' . $parentlinks ;
           $parentcat = $categoryRecords[0]['parentNum'];
        }
    }

I then display the breadcrumb with the following code:

                                <ul class="breadcrumbs">
                                    <li><a href="#">HOME</a></li>
                                    <?php echo $parentlinks;?>
                                    <li><a href="/category/<?php echo htmlencode($listingsRecord['categories.name']) ?>/<?php echo htmlencode($listingsRecord['category']) ?>"><?php echo $listingsRecord['categories.name'] ?></a></li>
                                    <li>Product Details</li>
                                </ul>

The above works fine, but I am having difficulty displaying the category name that the product is in (i.e. line 4 in the code above doesn't work).

Would you be able to help me get this working please?

Thank you,

Greg

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