Showing number of listings next to link for predetermined search

Hi,

CMS Builder has a function built into it that will count the number of entires of something in a section, and you can add in a custom where statement as well. Something like this should probably work:

mysql_count('equipmentSection', "type = 'farm'");  

Thanks!

Greg

Hi Greg,

I've tried several methods, unsuccessfully, to display the result of the mysql_count function.  I want to display the count of the equipment_type from my inventory section, so according to your code I should have it like this somewhere:

mysql_count('inventory', "equipment_type = '6'"); 


*note: the inventory section records pulls in the 'equipment_type" from a separate section called 'equipment_type', so the number '6' above represents bulldozers, as an example.

If I have code, from my inventory section to display on an index.php page, like the following:

<?php if ($record['equipment_type']): ?>
   <?php echo $record['equipment_type'] ?> 
<?php endif ?>

and it displays something like:

Bulldozers

and I want to use CMSB's mysql_count function and display the results, say 16 bulldozers like:

Bulldozers (16)

How would I code that on the php page?

Thank you in advance!

Hi,

From how you've described you have your section set up, it sounds as if your mysql_count code is correct. Are you storing the num as the value for your equipment_type list field? As it looks like you might be storing the title.  Do you get any errors when the code runs, or does it always return the number zero?

I think your code should look something like this:

<?php if ($record['equipment_type']): ?>
  <?php echo $record['equipment_type'] ?>(<?php echo mysql_count('inventory', "equipment_type = ".$record['equipment_type']); ?>) 
<?php endif ?>

So if $record contains the contents of a inventory record, the mysql_count function can be used to count the number of records in the inventory section that have an equipment_type with the same value.

Thanks!

Greg

Hi Greg,

Actually my code is a little different, mostly because I'm not pulling ALL equipment types from the database...only a few. BUT, the code you provided, once modified for my scenario a little bit...works perfect!

One more question, though...if a multi-choice list is being used for equipment_type, will the code you provided count and display all inventory numbers of those equipment types? Such as graders, rollers and compactors put together.?

Hi,

If your using a multi select drop down then your code for counting the number of records would have to be slightly different. CMS Builder stores numbers for multi select drop downs in a tab separated string, your code would need to look like this:

<?php echo mysql_count('inventory', "equipment_type LIKE '%\t".$record['equipment_type']."\t%'"); ?>

So the MySQL where statement searches the equipment type string for the record number separated by two tabs.

This code would only return entries where bulldozer had been ticked. It wouldn't include an other items that had been ticked for a record. 

Thanks!

Greg

Hi Greg,

Your reply confused me slightly because the initial statement proffers code for a multi-select, but then it reads this would only return entries for when bulldozer (or one of the entries) was ticked. If someone selects on the multi-select, say bulldozers, earthmovers, and heavy construction as 3 different equipment types, then the code would count all 3 but only show the bulldozer records?

Hi,

Sorry, for the confusion, the initial code I gave you:

<?php echo mysql_count('inventory', "equipment_type = ".$record['equipment_type']); ?>

wouldn't have worked for a multi select, it would only have functioned for single select drop down or radio. 

The mysql_count code I gave you for mutli select items:

<?php echo mysql_count('inventory', "equipment_type LIKE '%\t".$record['equipment_type']."\t%'"); ?>

would only count any records where bulldozer is selected once, even if the user had selected several other items as well. So an inventory record where bulldozers, earthmovers, and heavy construction are all selected would only be counted once. 

Hopefully I've made things clearer this time.

Thanks!

Greg

Hi, Greg.

I'm using the following code, but it seems to be delivering the total number of records, rather than the number in each individual category.

<ul>
<?php foreach ($catalogue_categoriesRecords as $record): ?>
<li>
<?php $count = 0; ?><a href="<?php echo $record['_link'] ?>"><?php echo htmlencode($record['title']) ?>
&nbsp;&raquo;</a>&nbsp;(<?php echo $catalogueMetaData['totalRecords']; ?>&nbsp;items)</li>
<?php endforeach ?>
</ul>


Here's the live page: http://kathiejordandesign.com.previewdns.com/storeCategories.php

Any idea where I'm going wrong, please?

Hi Nigel,

The problem is your using the totalRecords variable for the catalogue_categories section. Which shows you the total number of records you've created in the catalogue section as opposed to the number of products that have a catalogue. You need to use the mysql_count function to find the number of records that use that  catalogue num value in your products. 

Let me know if you need a hand integrating the system, I'll need to know if the catalogue list field for the products is a single or multi check box.

Thanks!

Greg