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:
'artists'
'merchandise'

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 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
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

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

By Deborah - November 30, 2011

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

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

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

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