Category

Hi Jason,
Yes, get all programs associates with the listings that are associated with Day Trips is what I want to do. However, I don't have the category set up within program types section to pull from like I am doing in the other code on page which is pulling the listings based on category. I guess that is where I am getting confused.

For the search area, I only want to pull in the program types that are associated with the day trips category. I currently have the program types being chosen under the category section when users are selecting their category, then they can choose the program types that they want to show up with that category. Let me know if you need anything more specific.

Thanks!
April

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

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,