list records from a concatenated search query

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

By Deborah - November 29, 2011

Another table merging challenge. I don't know how (or if its possible) to query related records for a table based on a field created by a CONCAT query.

I have a master list page for 'artists'. Clicking on the selection for a specific artist, I'd like to see the 'merchandise' list page with listings that match the artist selected.

The tables are:

The 'merchandise' table contains an 'artist_name' select list field that uses this query:

SELECT num, CONCAT(first_name," ",middle_name," ",last_name) FROM `<?php echo $TABLE_PREFIX?>artists`
ORDER BY first_name

The merchandise table is storing the artist_name as the record num, not the concatenated name, so I can't use 'artist_name' for my search.

Can this even be done? If so, any pointers would be greatly appreciated. Thanks in advance!

~ Deborah

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