6 Replies - 610 Views - Last Post: 27 June 2018 - 04:52 PM

#1 martymcfly   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 55
  • Joined: 09-July 16

I want to get results in priority order

Posted 27 June 2018 - 04:05 PM

Hi!
I am making a kind of a social network website. And I am writing the database search part. According to keyword that the user entered, I want to get result like the users whose usernames start with the keyword have a high priority, then the users whose username don't start with the keyword but contain have a low priority. To be clearer, I am leaving a code part from my app.

    public function search_users($keyword){
      $this->db->query('SELECT * FROM users WHERE 
      username LIKE :key1');

      //Bind the value
      $this->db->bind(':key1', '%' . $keyword . '%');


      //Execute
      $rows = $this->db->resultSet();
      return $rows;
    }



Is This A Good Question/Topic? 0
  • +

Replies To: I want to get results in priority order

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: I want to get results in priority order

Posted 27 June 2018 - 04:20 PM

Please give an example of the data and then an example of the data how you want it ranked.
Was This Post Helpful? 0
  • +
  • -

#3 martymcfly   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 55
  • Joined: 09-July 16

Re: I want to get results in priority order

Posted 27 June 2018 - 04:23 PM

keyword: marty

Results:
1 - marty01
2- 51515marty1515

marty01 is the first row because it starts with the keyword, 51515marty1515 is in the second row because it doesn't start with the keyword but contains it.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: I want to get results in priority order

Posted 27 June 2018 - 04:35 PM

Then you would want to use a UNION over two queries.

The first would be using the '%' AFTER your keyword, and the second would be % on both ends of your keyword. You should have the second check for 'NOT LIKE' the first query.

https://dev.mysql.co...n-matching.html

Example:


cr eate table #foo(id int, sval varchar(20))

insert into #foo(id, sval) values (1, 'abc123')
insert into #foo(id, sval) values (2, 'wer123')
insert into #foo(id, sval) values (3, 'c123werw')
insert into #foo(id, sval) values (5, 'abc123abc')
insert into #foo(id, sval) values (7, '987asdf123')
insert into #foo(id, sval) values (9, 'aZ4abc54')
insert into #foo(id, sval) values (11, '789abc4')

-- you give the first group an artificial rank of '1'.. this can make it easier to sort in a temp table or part of a larger subquery.
select *, '1' as myRank
from #foo
WHERE sval like 'abc%'

union
-- you give the second group an artificial rank of '2'
select *, '2' as myRank
from #foo
WHERE sval like '%abc%'
and sval not like 'abc%'

drop table #foo


id          sval                 myRank
----------- -------------------- ------
1           abc123               1
5           abc123abc            1
9           aZ4abc54             2
11          789abc4              2


The wild card looks for 0 to many characters depending on how you place it.

ABC% -> Makes sure it starts with 'ABC' and then anything after.
%ABC -> Makes sure it end with 'ABC' and then anything before.
%ABC% -> Makes sure it is somewhere in the string.
Was This Post Helpful? 1
  • +
  • -

#5 martymcfly   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 55
  • Joined: 09-July 16

Re: I want to get results in priority order

Posted 27 June 2018 - 04:36 PM

I got it Thank you very much. At first sight, it seems to be done in one line but this is the correct way. Thank you again.
Was This Post Helpful? 0
  • +
  • -

#6 martymcfly   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 55
  • Joined: 09-July 16

Re: I want to get results in priority order

Posted 27 June 2018 - 04:51 PM

Actually this is a very original and good solution. Did you think it right now, or used before?
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: I want to get results in priority order

Posted 27 June 2018 - 04:52 PM

Thanks, I've used something similar when ranking information for reporting.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1