Chit-Chat

Filtering by User Attributes

Permalink July 20, 2015 at 8:33 PM

I could use some help with this LIKE statement where I'm filtering user attributes by their region. Typically a region is a state name and the problem is that I get in correct results for some states because the LIKE statement is picking up regions that have the same characters in them. For example, a search for the Virgina region also picks up West Virginia. I tried not using a LIKE statement but when I do so, regions with more than one word (i.e. New York) don't show in the results. Seems like if I fix one issue it breaks another. Any suggestions would be greatly appreciated. I've gone through a number of similar forum posts but didn't find one that addresses both of my issues.

$region =  $_GET['region'];
            $region = trim($region);
                                Loader::model('user_list');
                                $userList = new UserList();
                                $userList->filterByGroup('Reps');
                                $userList->filterByAttribute('region', "%{$region}%", "LIKE");
                                $users = $userList->get();
                                foreach ($users as $userInfo) {
                                    $company = $userInfo->getAttribute('company');
                                    echo "<strong>".$company."</strong><br />";
                                    $address = $userInfo->getAttribute('billing_address');
                                    echo $address."<br />";
                                    $phone = $userInfo->getAttribute('billing_phone');
                                    echo "Phone: ".$phone."<br />";
                                    $fax = $userInfo->getAttribute('fax');

Viewing 15 lines of 18 lines. View entire code block.

 
                                $region =  $_GET['region'];
            $region = trim($region);
 
                                Loader::model('user_list');
                                $userList = new UserList();
                                $userList->filterByGroup('Reps');
 
                                $userList->filterByAttribute('region', "%{$region}%", "LIKE");
 
                                $users = $userList->get();
                                foreach ($users as $userInfo) {
                                    $company = $userInfo->getAttribute('company');
                                    echo "<strong>".$company."</strong><br />";
                                    $address = $userInfo->getAttribute('billing_address');
                                    echo $address."<br />";
                                    $phone = $userInfo->getAttribute('billing_phone');
                                    echo "Phone: ".$phone."<br />";
                                    $fax = $userInfo->getAttribute('fax');
                                    echo "Fax: ".$fax;
                                    echo "<hr>";
                                }

pixo replied on Jul 21, 2015 at 10:56 am Permalink Reply

Let me re-post this code so that it is easier to read:

$region =  $_GET['region'];
$region = trim($region);
                  Loader::model('user_list');
                  $userList = new UserList();
                  $userList->filterByGroup('Reps');
                  $userList->filterByAttribute('region', "%{$region}%", "LIKE");
                  $users = $userList->get();
                  foreach ($users as $userInfo) {
                       $company = $userInfo->getAttribute('company');
                       echo "<strong>".$company."</strong><br />";
                       $address = $userInfo->getAttribute('billing_address');
                       echo $address."<br />";
                       $phone = $userInfo->getAttribute('billing_phone');
                       echo "Phone: ".$phone."<br />";
                       $fax = $userInfo->getAttribute('fax');

Viewing 15 lines of 18 lines. View entire code block.

 
$region =  $_GET['region'];
$region = trim($region);
                  Loader::model('user_list');
                  $userList = new UserList();
                  $userList->filterByGroup('Reps');
 
                  $userList->filterByAttribute('region', "%{$region}%", "LIKE");
 
                  $users = $userList->get();
                  foreach ($users as $userInfo) {
                       $company = $userInfo->getAttribute('company');
                       echo "<strong>".$company."</strong><br />";
                       $address = $userInfo->getAttribute('billing_address');
                       echo $address."<br />";
                       $phone = $userInfo->getAttribute('billing_phone');
                       echo "Phone: ".$phone."<br />";
                       $fax = $userInfo->getAttribute('fax');
                       echo "Fax: ".$fax;
                       echo "<hr>";
                  }

hutman replied on Jul 21, 2015 at 12:06 pm Permalink Reply

What type of attribute is "region"? If it is a multi-select this is going to be very difficult to get around, but if it is a regular select you should be able to do it without the LIKE.

pixo replied on Jul 21, 2015 at 12:08 pm Permalink Reply

Thanks for your reply. This is a multi-select currently. Would I be better off changing to something else?

hutman replied on Jul 21, 2015 at 12:15 pm Permalink Reply

Have you tried this:

$userList->filterByAttribute('region', "%\n{$region}\n%", "LIKE");

I seem to remember that working with a PageList

pixo replied on Jul 21, 2015 at 12:19 pm Permalink Reply

I did try that previously and it did not work. Just tried it again to make sure. It works for some states but not for others.

hutman replied on Jul 21, 2015 at 12:22 pm Permalink Reply

You did it with the "\n" in there? Which states don't work this way?

pixo replied on Jul 21, 2015 at 12:33 pm Permalink Reply 1 Attachment

I haven't figured out the pattern yet. New York works. New Jersey, California, Colorado doesn't.

You can try the states at:
http://pinnacle-ltg.com/contact/find-your-rep/...

Right now I'm using:
$userList->filterByAttribute('region', "%\n{$region}\n%", "LIKE");

Attached is a screen shot of the attribute setup.

hutman replied on Jul 21, 2015 at 12:37 pm Permalink Reply

Does the rep for New York have multiple regions? It's possible this code only works when there are multiple regions for the user.

pixo replied on Jul 21, 2015 at 12:46 pm Permalink Reply

That one in New York is just in one region.

Would it be easier to fix this if I just turned off the multi-select? Would that make it possible for me to just use the = rather than the LIKE?

hutman replied on Jul 21, 2015 at 12:48 pm Permalink Reply

If you're not using the multi select then yes, you would be able to use = instead of like if it wasn't turned on.

pixo replied on Jul 21, 2015 at 12:54 pm Permalink Reply

I turned off the multi-select for the attributes, but strangely it didn't fix it. Single word regions are coming up fine, but two word regions (i.e. New York) don't show any results now. This is what I'm using for the filter:

$userList->filterByAttribute('region', $region, '=');

hutman replied on Jul 21, 2015 at 12:56 pm Permalink Reply

Can you add

$userList->debug();

Before the ->get() and see what it is searching for? It might be searching 'New%20York' instead of 'New York'

pixo replied on Jul 21, 2015 at 1:02 pm Permalink Reply

When doing a search for West Virginia this is the response:

(mysqlt): SELECT DISTINCT u.uID, u.uName FROM Users u left join UserGroups ug_5 on ug_5.uID = u.uID left join UserSearchIndexAttributes on (UserSearchIndexAttributes.uID = u.uID) where 1=1 and ug_5.gID=5 and u.uIsActive = '1' and u.uIsValidated != '0' and ak_region = 'West Virginia' limit 0,100

hutman replied on Jul 21, 2015 at 1:04 pm Permalink Reply

Unfortunately I don't know what you have in your database as far as matching those criteria, does all of that look correct to you? If you test it in your phpMyAdmin does it give you what you think you should get?

Forums

Chit-Chat

Filtering by User Attributes

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?