list records from a concatenated search query

Hi Deborah,

So the issue here is that the database is only storing the num, not the concatenated value.

If you need to perform a search, there are a couple of options.

1) you can use the concatenated value as both your value and label. Since you're using first, middle, and last name, you have a reasonable chance of this being unique, but it is NOT guaranteed. Also, if you ever need to change a spelling mistake or a name changes, all of your associations will break. If you do make this change, you'll need to re-associate all of the records that you already have in merchandise.

2) You can perform your search against the artists table. You can then use the resulting record numbers to search against the merchandise table.

For example, I'll assume that someone enters a value in a text box called "search". This will search against the first, middle and last names in artist, then do a merchandise search. NOTE: this also assumes that you are using version 2.08 or higher:

$artistsWhere = "`first_name` LIKE '%".mysql_escape(@$_REQUEST['search'])."%' OR
middle_name LIKE '%".mysql_escape(@$_REQUEST['search'])."%' OR
last_name LIKE '%".mysql_escape(@$_REQUEST['search'])."%'";

$artistRecordNums = join(",", array_pluck(mysql_select('artists', $artistsWhere), 'num'));

if (!$artistRecordNums) { $artistRecordNums = 0; }

// get resulting merchandise records
list($merchandiseRecords, $merchandiseMetaData) = getRecords(array(
'tableName' => 'merchandise',
'allowSearch' => false,
'where' => "`artist_name` IN ($artistRecordNums)",
));

Hope this helps get you started