How to create smart search query depends on column-name?
I have a table like this:
+----+----------+-------+-------+
| id | name | code1 | code2 |
+----+----------+-------+-------+
| 1 | jack | 1 | 1 |
| 2 | peter | 1 | 2 |
| 3 | abel | 1 | 3 |
| 4 | brent | 2 | 1 |
| 5 | arnold | 2 | 2 |
| 6 | chester | 3 | 1 |
| 7 | morgan | 3 | 2 |
| 8 | olin | 3 | 3 |
| 9 | marvin | 3 | 4 |
| 10 | gavin | 3 | 5 |
| 11 | harley | 4 | 1 |
+----+----------+-------+-------+
Well, I have 3 keywords: name, code1, code2
My question is about the order of results depends on those keywords. There is some examples:
Example1:
$q = 'name brent'; // it is what user writes for search in the input
// actually I meant of smart is detecting this automatically:
// keyword: 'name', value: 'brent'
I want this output:
+----+----------+-------+-------+
| 4 | brent | 2 | 1 |
| |
| /* the order of other colunms |
| does not matter */ |
| |
+----+----------+-------+-------+
Example2:
$q = 'code1 3';
I want this output:
+----+----------+-------+-------+
| 6 | chester | 3 | 1 |
| 7 | morgan | 3 | 2 |
| 8 | olin | 3 | 3 |
| 9 | marvin | 3 | 4 |
| 10 | gavin | 3 | 5 |
| |
| /* the order of other colunms |
| does not matter */ |
| |
+----+----------+-------+-------+
Example3:
$q = 'code1 3 code2 2';
I want this output:
+----+----------+-------+-------+
| 7 | morgan | 3 | 2 |
| |
| /* the order of other colunms |
| does not matter */ |
| |
+----+----------+-------+-------+
Example4: If there is all three keywords in input, code1 and code2 have higher priority.
$q = 'name jack code1 3 code2 1';
I want this output:
+----+----------+-------+-------+
| 6 | chester | 3 | 1 | // code1 3 code2 1
| 1 | jack | 1 | 1 | // name jack
| |
| /* the order of other colunms |
| does not matter */ |
| |
+----+----------+-------+-------+
Example5: If the value of keyword (name jack => keyword: name, value: jack) does not exist, it means: The keyword and its value will not be considered, Just like it does not exist in the input.
$q = 'code1 2 code2 5';
I want this output:
+----+----------+-------+-------+
| 4 | brent | 2 | 1 |
| 5 | arnold | 2 | 2 |
| |
| /* the order of other colunms |
| does not matter */ |
| |
+----+----------+-------+-------+
For start, I think I have to separate the numbers of string, something like this:
preg_match_all('!\d+!u', $q, $matches);
And then use them in my query,something like this:
... IN (" .implode(',', $matches[0]) . ") ...
Or I don't know, is there any good solution? Actually what I need is a magic ordering ...!
