list records from a concatenated search query

amazingly I just learned how to do this earlier today on a site I am working on, and I got it to work the first time. here is what my query looks like...

SELECT num, CONCAT(city,', ',state)
FROM `<?php echo $TABLE_PREFIX ?>facilities`

The only difference I see is the use of single quotes in mine versus doubles in yours.

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

zip222,

Thanks for posting the alternate code. Actually, both versions work for me. What I need to do is one step further, which Jason has addressed in the next reply. I appreciate your help.

~ Deborah

Jason,

I had just set everything up successfully using CONTACT for value and label. I understand now from your post that this is not the desired method, due to updates to the names. I'll install the upgrade and give it a go (your #2 suggestion).

Thanks for the helpful info and wish me luck!

~ Deborah