Main
Index 		Search
Posts 		Who's
Online 		Log
In

Home: Products: CMS Builder:
Where in/like

 
 


rjbathgate
User

Jan 12, 2012, 11:53 AM

Post #1 of 3 (427 views)
Shortcut
Where in/like Can't Post
Hi,

I am stuck on something I'm sure I've done before with no troubles...

I have tbl_people, in which has field practice_areas.

The practice_areas field is a multi value checkbox using a mysql query to get values:

Code
SELECT num,title 
  FROM `<?php echo $TABLE_PREFIX ?>practice_areas` 
WHERE parent = ''

Now I want to display related people records on a practice area details page.

If I use this:
Code
'where' => "$thisPage IN (practice_areas)",
($thisPage is the current practice_area detail page)

it only works if the current practice area is first, eg:
$thisPage = 11
people practice_area = '11 23 32' -- will show
people practice_area = '9 11 23' - won't show.

I figure that's because this list needs to be 9,11,23 instead...?

If instead I use
Code
'where' => "practice_areas LIKE '%$parent%'",

it fails on practice_area detail page '1', because it finds 1 in 11, 12, 13 etc instead of just 1.

Am I completely missing something and having Fridayitus? I'm sure I've done this before using IN with no problems -- is there anyway to store the people practice_areas as csv?

Thanks in advance
Rob


Jason
Staff / Moderator


Jan 13, 2012, 8:20 AM

Post #2 of 3 (412 views)
Shortcut
Re: [rjbathgate] Where in/like [In reply to] Can't Post
Hi,

This can be done using either LIKE or IN, depending on exactly how you want things to work.

You would use IN if you are looking for records where a field has 1 of several values. The field you are comparing, however, needs to have only a single value (like num, for example). To get this to work, what you normally have to do is first break your multi-select value into an array, then use join() to turn it into a comma separated list.

For example:

Code
 
  $practiceAreaList = join(",", explode("\t", trim($record['practice_areas'], "\t"))); 
 
 if ($practiceAreaList) { 
   $where = "num IN ($practiceAreaList)"; 
 }

The one catch with IN is that if practice_areas has no value, you will get a MySQL error, which is why we use the if statement.

LIKE works in the exact opposite way. We use LIKE when we have a single value and are looking for it inside of a larger string (like a multi-value field). You were actually very close in your LIKE statement. What you need to do is add tab characters ("\t") to the front and end of your string. Each value in a mult-select list has a tab character in front and in back of it.

Example:

Code
  'where' => "practice_areas LIKE '%\t$parent\t%'",

Hope this helps
---------------------------------------------------
Jason Sauchuk - Programmer 
interactivetools.com

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


rjbathgate
User

Jan 15, 2012, 12:37 PM

Post #3 of 3 (388 views)
Shortcut
Re: [Jason] Where in/like [In reply to] Can't Post
Hey,

Thanks, the

Code
  'where' => "practice_areas LIKE '%\t$parent\t%'",

works perfectly...

Cheers
Rob