By potential on

Hey guys,

With the default search, you can either search content or users. When you search users, it only searches the username. How can I extend this to also search the first and last name fields I have in the profile?

I would also like to know how to make a third search tab, for location. It would search the city and country fields in the profile to find users by location.

Huge thanks, in advance, for helping me on this!

- BigP

⋅ Categories: Drupal 4.6.x

Comments

potential’s picture

potential commented

This thread http://drupal.org/node/32382 seems to be related, but I don't know how to integrate it for my needs...

potential’s picture

potential commented

Here is the code I pulled from the other thread. Works well. All you have to do is copy it into profile.module.

<?php
function profile_search($op = 'search', $keys = null) {
  switch ($op) {
   case 'name':
      if (user_access('access user profiles')) {
        return t('user profiles');
      }
    case 'search':
      if (user_access('access user profiles')) {
        $find = array();
        // Replace wildcards with MySQL/PostgreSQL wildcards.
        $keys = preg_replace('!\*+!', '%', $keys);
        $result = pager_query("SELECT {profile_values}.*,{users}.name,{users}.uid FROM {profile_values},{users} WHERE LOWER({profile_values}.value) LIKE LOWER('%%%s%%') AND {users}.uid = {profile_values}.uid", 15, 0, NULL, $keys);
        while ($account = db_fetch_object($result)) {
          $find[] = array('title' => $account->name, 'link' => url('user/'. $account->uid));
        }
        return $find;
      }
  }
}
?>

I'd like to modify it to seach JUST the name field of the profile. I believe I have to modify the SQL statement for this. If possible, I'd like to also output the user picture/avitar with the results.

THANKS!

bradlis7’s picture

bradlis7 commented

It's going to be a little complicated. My personal profile users fullname field is called "profile_fullname", which you'll probably have to change. Here goes nothing...

$result = pager_query("SELECT {profile_values}.*,{users}.name,{users}.uid FROM {profile_values},{users},{profile_fields} WHERE LOWER({profile_values}.value ) LIKE LOWER( '%%%s%%' ) AND {profile_fields}.name = 'profile_fullname' AND {users}.uid = {profile_values}.uid AND {profile_fields}.fid = {profile_values}.fid LIMIT 0,30", 15, 0, NULL, $keys);

There may be a few errors. Let me know if so

potential’s picture

potential commented

My profile has a field called profile_fname and profile_lname for first and last name.

Heres the entire code I am using now after adding the above:

<?php
function profile_search($op = 'search', $keys = null) {
switch ($op) {
case 'name':
if (user_access('access user profiles')) {
return t('real names');
}
case 'search':
if (user_access('access user profiles')) {
$find = array();
// Replace wildcards with MySQL/PostgreSQL wildcards.
$keys = preg_replace('!\*+!', '%', $keys);
$result = pager_query("SELECT {profile_values}.*,{users}.name,{users}.uid FROM {profile_values},{users},{profile_fields} WHERE LOWER({profile_values}.value ) LIKE LOWER( '%%%s%%' ) AND {profile_fields}.name = 'profile_fname' AND {users}.uid = {profile_values}.uid AND {profile_fields}.fid = {profile_values}.fid LIMIT 0,30", 15, 0, NULL, $keys);
while ($account = db_fetch_object($result)) {
$find[] = array('title' => $account->name, 'link' => url('user/'. $account->uid));
}
return $find;
}
}
}
?>

But I receive this error:

user error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 15' at line 1
query: SELECT profile_values.*,users.name,users.uid FROM profile_values,users,profile_fields WHERE LOWER(profile_values.value ) LIKE LOWER( '%dan%' ) AND profile_fields.name = 'profile_fname' AND users.uid = profile_values.uid AND profile_fields.fid = profile_values.fid LIMIT 0,30 LIMIT 0, 15 in F:\www\htdocs\includes\database.mysql.inc on line 66.

bradlis7’s picture

bradlis7 commented

Because you have a first name and last name, searching is going to be quite difficult, and the only thing I could come up with may not be compatible with your version of MySQL if that's what you have. I'd suggest using only 1 field for the name. I might try to work a little more on it later, but someone else may be able to help you more.

potential’s picture

potential commented

When I removed "LIMIT 0,30" from the SQL statement, it successfully searched the first name field in my profiles.

Now I have to figure out how to search both first and last name as I want to keep the fields separate. When I add my last name field into the SQL statement using AND, it doesn't work, and when I use OR I get many duplicate results.

Any advice?? I'll keep working on it.

bradlis7’s picture

bradlis7 commented

The DISTINCT keyword will cause the sql to return only one of each user:

SELECT DISTINCT {profile_values}.*,{users}.name,{users}.uid FROM {profile_values},{users},{profile_fields} WHERE LOWER({profile_values}.value ) LIKE LOWER( '%%%s%%' ) AND {profile_fields}.name = 'profile_fname' OR {profile_fields}.name = 'profile_lname') AND {users}.uid = {profile_values}.uid AND {profile_fields}.fid = {profile_values}.fid LIMIT 0,30

Note that if a user types in "John Doe", only one of the fields will be searched. The user can type in "John" or "Doe," but not both.