I am creating a search for blood donors and everything is working fine and after completing the search I am facing some issues in it. My search is working but it's not showing perfect result. Let me elaborate,
#My Table Structure
#Table 1- blood_donor
---- ------ ---------- -------------
| id | name | blood_id | location_id | // columns
---- ------ ---------- -------------
| 1 | rony | 1 | 1 |
---- ------ ---------- -------------
| 2 | jony | 2 | 2 |
---- ------ ---------- -------------
| 3 | tony | 3 | 3 |
---- ------ ---------- ------------- // Data example
#Table 2- blood_group
---- ------------
| id | group_name | // columns
---- ------------
| 1 | a positive |
---- ------------
| 2 | b positive |
---- ------------
| 3 | o negative |
---- ------------ // Data example
#Table 3- location
---- ---------------
| id | location_name | // columns
---- ---------------
| 1 | khulna |
---- ---------------
| 2 | dhaka |
---- ---------------
| 3 | sylhet |
---- --------------- // Data example
My keyword
$keyword = "o negative blood donor in khulna";
Now I separated the words
$keyword_array = explode(" ",$keyword);
Here is my query
$query ="SELECT * FROM
blood_donor AS bd
LEFT JOIN blood_group AS bg ON bg.id = bd.blood_id
LEFT JOIN location AS lo ON lo.id = bd.location_id
WHERE
bd.name LIKE '%$keyword%' OR
bg.group_name LIKE '%$keyword%' OR
lo.location_name LIKE '%$keyword%' OR";
// this above part giving me a result set for 100% match for the full keyword
// Now I generate another result set for separated keyword below
if (count($keyword_array) > 1) {
foreach ($keyword_array as $array) {
$query = $query . "
bd.name LIKE '%$array%' OR
bg.group_name LIKE '%$array%' OR
lo.location_name LIKE '%$array%' OR";
}
}
$query = substr($query,0,-3); // Removed the extra OR from the end
// My order by section
$query = $query . " ORDER BY
CASE
// If full match found, then bring them top
WHEN bd.name LIKE '%$keyword%' THEN 1";
// If partial match found, then bring them afterwards
if (count($keyword_array) > 1) {
$keyword_count = count($keyword_array);
$query = $query . " WHEN";
for ($i = 0; $i < $keyword_count; $i ) {
$query = $query . " bd.name LIKE '%$keyword_array[$i]%' AND";
}
$query = substr($query,0,-3); // To remove the last AND
$query = $query . " THEN 2";
}
$query = $query . " ELSE 3
END
LIMIT $items_to_skip, $items_per_page"; // These two variables are OFFSET and LIMIT
The keyword I am searching with, in the full match section it has nothing to do because there are no such sentence available in my database as `o negative blood donor in khulna that is okay but when the keywords getting separated then in the separated result section it's generating result what actually I am not looking for!
My separated keywords generating an array which is
Array
(
[0] => o
[1] => negative
[2] => blood
[3] => donor
[4] => in
[5] => khulna
)
And my SQL generating the query like this
bd.name LIKE '%o%' OR
bg.group_name LIKE '%o%' OR
lo.location_name LIKE '%o%' OR
bd.name LIKE '%negative%' OR
bg.group_name LIKE '%negative%' OR
lo.location_name LIKE '%negative%' OR
bd.name LIKE '%blood%' OR
bg.group_name LIKE '%blood%' OR
lo.location_name LIKE '%blood%' OR
bd.name LIKE '%donor%' OR
bg.group_name LIKE '%donor%' OR
lo.location_name LIKE '%donor%' OR
bd.name LIKE '%in%' OR
bg.group_name LIKE '%in%' OR
lo.location_name LIKE '%in%' OR
bd.name LIKE '%khulna%' OR
bg.group_name LIKE '%khulna%' OR
lo.location_name LIKE '%khulna%'
This query giving me all the three rows because all the name contain the letter o but I don't want that! I want o negative as a single word so the result must be two rows. row 1 and 3. But how to achieve that? I really can't figure it out. Also I don't know how to make two different word as a single word.
There are eight blood groups and I want to treat every blood group name as a single word! Example: A Negative is two word, but I want them as one word and how am I going to do it, is it possible?
I need an array like this. And how to do it?
$keyword = "o negative blood donor in khulna";
$keyword_array = explode(" ",$keyword);
print_r($keyword_array);
Array
(
[0] => o negative
[1] => blood
[2] => donor
[3] => in
[4] => khulna
)
Thanks in advance.
EDIT - My Approach From @Brombeer
It is working, but when I am working with this array array('b positive','b negative','ab positive','ab negative') then its working but not as expected! And my keyword is b negative b positive ab negative and ab positive blood donor in khulna But when I am bringing 'ab positive','ab negative' at the front of the array, then it's working. The reason is ab positive have the word b positive in it. That is why if this two blood group is at the end of array then foreach not reaching to it. Its throwing result from nearest match. Please check
// If my array and keyword look like this
$specials = array('ab positive','ab negative','b positive','b negative');
$keyword = 'b negative b positive ab negative and ab positive blood donor in khulna';
foreach($specials as $special) {
if(strstr($keyword, $special)) {
$keyword = trim(str_replace($special, '', $keyword));
$found[] = $special;
}
}
$keyword_array = explode(" ",$keyword);
$keyword_array = array_merge($keyword_array, $found);
print_r($keyword_array);
// Then it's generating this array
Array
(
[0] => and
[1] => // How to fix this?
[2] => blood
[3] => donor
[4] => in
[5] => khulna
[6] => ab positive
[7] => ab negative
[8] => b positive
[9] => b negative
)
// When my array and keyword look like this
$specials = array('b positive','b negative','ab positive','ab negative');
$keyword = 'b negative b positive ab negative and ab positive blood donor in khulna';
foreach($specials as $special) {
if(strstr($keyword, $special)) {
$keyword = trim(str_replace($special, '', $keyword));
$found[] = $special;
}
}
$keyword_array = explode(" ",$keyword);
$keyword_array = array_merge($keyword_array, $found);
print_r($keyword_array);
// Then it's generating this array
Array
(
[0] => a // How to fix this?
[1] => and
[2] => a // How to fix this?
[3] => blood
[4] => donor
[5] => in
[6] => khulna
[7] => b positive
[8] => b negative
)
CodePudding user response：
You could use a pattern to match the words that you want:
(?i)\b[abo]\s (?:posi|nega)tive\b|\S
The pattern in parts matches:
(?i)Inline modifier for a case insensitive match\bA word boundary to prevent a partial word match[abo]\smatch eitheraboroand 1 or more whitespace characters(?:posi|nega)tive\bMatch either positive or negative followed by a word boundary|Or\SMatch 1 or more non whitespace chars (All other words)
See a Regex demo or a PHP demo.
$keyword = "o negative blood donor in khulna";
$pattern = "/(?i)\b[abo]\s (?:posi|nega)tive\b|\S /";
preg_match_all($pattern, $keyword, $keyword_array);
print_r($keyword_array[0]);
Output
Array
(
[0] => o negative
[1] => blood
[2] => donor
[3] => in
[4] => khulna
)
CodePudding user response：
<?php
preg_match_all('/(\w\s )?\w /', 'o negative blood donor in khulna', $matches);
$keyword_array = $matches[0];
and here is an advice: please use prepared statement for SQL
CodePudding user response：
Create a "special" variable, remove that variable/string from $keyword, explode your $keyword and add the "special" variable back to the resulting array:
<?php
$special = 'o negative';
$keyword = "o negative blood donor in khulna";
$keyword = trim(str_replace($special, '', $keyword));
$keyword_array = explode(" ",$keyword);
$keyword_array[] = $special;
print_r($keyword_array);
will output:
Array
(
[0] => blood
[1] => donor
[2] => in
[3] => khulna
[4] => o negative
)
Edit: To use multiple keywords to be replaced you can use this:
<?php
$specials = array('a positive','b negative','o negative','ab positive');
$keyword = "o negative blood donor in khulna";
foreach($specials as $special) {
if(strstr($keyword, $special)) {
$keyword = trim(str_replace($special, '', $keyword));
$found[] = $special;
}
}
$keyword_array = explode(" ",$keyword);
$keyword_array = array_merge($keyword_array, $found);
print_r($keyword_array);
If a "special" keyword was found it will be replaced in the original string and saved to a $found array. Your $keyword_array will be merged with the $found array in the end.
This will also work if you have multiple of those "special" keywords in your original string: Using $keyword = "o negative b negative blood donor in khulna a positive"; would output:
Array
(
[0] => blood
[1] => donor
[2] => in
[3] => khulna
[4] => a positive
[5] => b negative
[6] => o negative
)
CodePudding user response：
If I understand you correctly, you have an issue with blood group/type, as it is like "O negative" or "AB positive" or "0 "...and so on, so basically two words in one...Try this (its not optimal, but it does the job)
// Define blood groups and types here..You can also include more later
$bloodGroups = [
"negative",
"positive",
" ",
"-"
];
$bloodTypes = [
"a",
"b",
"o",
"0", //Some might go for zero...
"ab"
];
$keyword = "o negative blood donor in khulna";
// Also try with...So upper case, signs, number
// $keyword = "AB - blood donor in khulna";
// $keyword = "0 blood donor in khulna";
$keywordArray = explode(" ",$keyword);
// Array to push blood group and type
$bloodGroupType= [];
foreach($keywordArray as $key => $value) {
$value = strtolower($value);
//If found in bloodTypes array, we have a blood type...push it to first position in bloodGroupType array and also unset from main array
if(in_array($value, $bloodTypes )) {
// Number to char
if($value == "0") {
$value = "o";
}
$bloodGroupType[0] = $value;
unset($keywordArray[$key]);
}
//If value is found in groups array, we have a blood group...push it to second position in bloodGroupType array and also unset from main array
if (in_array($value, $bloodGroups )) {
//Change signs to words
if($value == "-") {
$value = "negative";
}
if($value == " ") {
$value = "positive";
}
$bloodGroupType[1] = $value;
unset($keywordArray[$key]);
}
}
// Create string from bloodGroupType, and push to "main array"
$searchBloodType = implode(" ", $bloodGroupType);
array_push($keywordArray,$searchBloodType);
print_r($keywordArray);
So now you can loop over this array. Also..This solution will also work if you mix the string..
BR