1. jimbo
  2. Support
  3. Thursday, October 08 2015, 09:56 PM
Hi,

I created a company_name text field on registration and I'd like to make the company names searchable as a selectlist (to make it easier for users to find available companies).

I've created a company_name_search searchlist but, of course, it is not working as the company names are stored in a different db table.

I'm trying to edit the administrator/components/com_jsn/helpers/fields/selectlist.php file but it is not working. You previously helped me with some edits for the text.php file so, based on that, I've come up with the code below (my new edits are currently commented out);


public static function getSearchQuery($field, &$query)
{
$option=JRequest::getVar($field->alias,null);
$db=JFactory::getDbo();
if($field->params->get('select_multiple',0))
{
foreach($option as $opt)
{
/*
if( $field->alias== 'company_name_search' )
{
$query->where('b.'.$db->quoteName( "company_name" ).' LIKE '.$db->quote('%"'.$opt.'"%'));
}
else
{
$query->where('b.'.$db->quoteName($field->alias).' LIKE '.$db->quote('%"'.$opt.'"%'));
}
*/
$query->where('b.'.$db->quoteName($field->alias).' LIKE '.$db->quote('%"'.$opt.'"%'));
}
}
else
{
/*
if( $field->alias== 'company_name_search' )
{
$query->where('b.'.$db->quoteName( "company_name" ).' = '.$db->quote($option));
}
else
{
$query->where('b.'.$db->quoteName($field->alias).' = '.$db->quote($option));
}
*/
$query->where('b.'.$db->quoteName($field->alias).' = '.$db->quote($option));
}

}


Am I making a mistake somewhere or is it not possible to achieve this?

Many thanks.
admin Accepted Answer
Admin
Hi,
there is many way to do this, but we do not recommend changing the source code.

You can archive this by:
1. In your select search field set DB parameter to get all company names as options (screenshot 1)
With this you will have a search select field with all company names options, but this field search into their values not on values of company_name text field, so follow step 2

2. You can create a simple plugin to copy company_name value into company_name_search values, follow these steps:
- Download, install and enable our skeleton plugin, you will find this at the end of page https://www.easy-profile.com/docs/44-triggers.html
- Open file /plugins/jsn/skeleton/skeleton.php
- Delete samples functions like: triggerFieldAvatarUpdate and triggerProfileUpdate
- write your function to copy field value:
triggerProfileUpdate($user,&$data,$changed,$isNew){ 
if(in_array('company_name',$changed)){
$data['company_name_search']=$data['company_name'];
}
}

Now when user register or update his profile Easy Profile store in DB the company name in 2 columns (company_name,company_name_search). Now search select field can search in own value because filled by this plugin

3. this not work for pre-existing users, the copy of the field value comes only when user updated a profile. You need to copy manually values from column "company_name" to column "company_name_search", you can do this with a simple query launched from some SQL tools like PhpMyAdmin. Here is a simple query that you can use to do this: http://stackoverflow.com/questions/6308594/copy-data-from-column-to-another-column-on-the-same-table

NOTE: this code is not tested and not supported, this is only for example purpose.
Attachments (1)
  1. more than a month ago
  2. Support
  3. # 1
jimbo Accepted Answer
Basic
Awesome, thank you so much for your reply. That looks to be a much better way to do it, thank you.

Sorry to throw another (small) question at you.. but on a related matter, which you previously helped me with, is there a way to edit this user list sql query to prevent the current/logged in user appearing in the list?


company_name LIKE "{user company_name displayedOrMe}" AND company_name IS NOT NULL AND company_name<>''


Many thanks.
  1. more than a month ago
  2. Support
  3. # 2
jimbo Accepted Answer
Basic
Note - update on the skeleton plugin solution;


triggerProfileUpdate($user,&$data,$changed,$isNew){
if(in_array('company_name',$changed)){
$data['company_name_search']=$data['company_name'];
}
}


It's not searchable. I can see the table data is being copied over but I think the problem is the format.
The input is via a text field (e.g. My Company Name) but the select list requires an array style format (e.g. ["My Company Name"]).
  1. more than a month ago
  2. Support
  3. # 3
jimbo Accepted Answer
Basic
Changing this line seems to work ok.. $data['company_name_search']='["'.$data['company_name'].'"]';
  1. more than a month ago
  2. Support
  3. # 4
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.
Sorry, the discussion is currently locked. You will not be able to post a reply at the moment.