Category

Hi April,

Here is a code example. I had to make a couple of assumptions in this:
1) program_types in the dir_listings section is a multi-value field.
2) this option value of this field is the num of the dir_programs field.

With those assumptions, what we do is combine the regular and premium listings into one array. We then take the program_types:values pseudo field of each record and combine those into a single array. We use that list to get the program records that match that list.

EXAMPLE:

$allListings = array_merge($dir_listingsRecords, $premium_listRecords);
$allProgramTypes = array();
foreach ($allListings as $listing) {
$allProgramTypes = array_merge($allProgramTypes, $listing['program_type:values']);
}

$whereProgram = "";
if ($allProgramTypes) {
$whereProgram = "num IN (".join(",", $allProgramTypes).")";
}

list($dir_programsRecords, $dir_programsMetaData) = getRecords(array(
'tableName' => 'dir_programs',
'allowSearch' => false,
'where' => $whereProgram
));

Hope this helps get you started

Jason,

I tested this but get an error on output.

Here is the error I am getting:
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 'Party Entertainment,Party Supplies,Party Venues)) ORDER BY globalOrder' at line 3

I have attached my page again in case I did something wrong for you to look at.

Thank you,
april

Hi April,

So it looks like your program_type field was set up using title instead of num, so each element in the list needs to have single quotes around it.

Try this:

$whereProgram = "";
if ($allProgramTypes) {

$programTypeString = "";

foreach ($allProgramTypes as $type) {
$programTypeString .= "'$type',";
}

$programTypeString = rtrim($programTypeString, ",");

$whereProgram = "title IN ($programTypeString)";
}

list($dir_programsRecords, $dir_programsMetaData) = getRecords(array(
'tableName' => 'dir_programs',
'allowSearch' => false,
'where' => $whereProgram
));

Hope this helps

Hi Jason,

Sorry to keep bothering you with this but it is still not filtering the program types in search based on category. I know I haven't explained my sections and fields well so I am attaching screenshots of those sections so you can see field names, etc.

My three sections:

Directory Listings - Multi record section

Directory Categories - Category section

Directory Program Types - Category section

When I placed the new code, I got an MySQL Error: Unknown column 'title' in 'where clause' because I use name instead of title. However, once I changed title to name in code, I don't get an error but the program types are not filtering based on the category.

Also, In the listings section, program types is a multi value field.

Thanks!

April

Hi April,

If you use debugSql on your query to get programs, what does it output? Also, what is "main_category" in dir_programs have a value of? Does it connect dir_categories to dir_programs? Can we use this to filter?

Let me know.

When I debugSql I get the following output:

SELECT SQL_CALC_FOUND_ROWS `dir_programs`.*
FROM `cms_dir_programs` as `dir_programs`
WHERE (name IN ('Multi-Activity','','Party Entertainment','Party Supplies','Party Venues'))
ORDER BY globalOrder
SELECT SQL_CALC_FOUND_ROWS `accounts`.*
FROM `cms_accounts` as `accounts`
WHERE (`num` IN (1))
ORDER BY fullname, username

This is showing my program types I selected for my party planning category (I only have two categories in the system that have associated listings as a test right now: Party Planning and Day Trips).

Also, the main_category in dir_programs is just the category area where the users put in a list of all their program types. Then when the user goes to the dir_categories section and input theit categories, I have a list field their pulling up all the program types from the dir_programs category section where they can choose what program types they want to show with that category. This is where I am retrieving the selected program types from. The name field in this section is the category name and then under select_program that is where they are using a multi value to select whatever program types they want associated with this category.

Thanks!
April

Hi April,

If you could fill out a [url http://www.interactivetools.com/support]2nd Level Support Request[/url] I can take a look and see if I can tell what's happening.

Thanks,