list records from a concatenated search query

5 posts by 3 authors in: Forums > CMS Builder
Last Post: November 30, 2011   (RSS)

Re: [Deborah] list records from a concatenated search query

By zip222 - November 29, 2011

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.

Re: [Deborah] list records from a concatenated search query

By Jason - November 30, 2011

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
Jason Sauchuk - Project Manager

Hire me! Save time by getting our experts to help with your project.

Re: [zip222] list records from a concatenated search query

By Deborah - November 30, 2011


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

Re: [Jason] list records from a concatenated search query

By Deborah - November 30, 2011 - edited: November 30, 2011


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