WHERE MATCH AGAINST any part of a word

Question

Updated question

Below works if I type 'format bibliotek' or 'format' or 'bibliotek' 1 post contains 'bibliotek' and 1 post contains 'format' in column knowmed_content. But if I search for 'bib', nothing is returned?

     if(isset($_POST['SearchIt'])) {
      $SearchIt = $_POST['SearchIt'];
    } else {
      $SearchIt = Null;
    }
    if(isset($SearchIt)) {
      $getknowledge = $conn->prepare("SELECT * FROM knowmed_main WHERE MATCH (knowmed_content) AGAINST ('%".$SearchIt."%' IN BOOLEAN MODE)");
    } else {
      $getknowledge = $conn->prepare("SELECT k.knowmed_id, k.knowmed_headline, k.knowmed_content, k.knowmed_tags, k.knowmed_created_by, k.entry_date, k.knowmed_active, u.Fname FROM knowmed_main AS k INNER JOIN users AS u ON k.knowmed_created_by = u.userid ORDER BY k.knowmed_id");
    }
    $getknowledge->execute();
    $resultknowledge = $getknowledge->get_result();
    $getknowledge->close();

    if ($resultknowledge->num_rows > 0) {
      while($row = $resultknowledge->fetch_assoc()) {
        $getknowmedId = $row["knowmed_id"];
        $getknowmedHeadline = $row["knowmed_headline"];
        $getknowmedContent = $row["knowmed_content"];
        $getknowmedUserentry = $row["knowmed_created_by"];

        echo "<li><a class=\"read-more\" href=\"read-question.php?dpid=".$getknowmedId."\" title=\"Læs mere\"><div class=\"bottom\">Spørgsmål oprettet d. <br />Af: ".$getknowmedUserentry."</div><div class=\"contentText\"><p><b>".$getknowmedHeadline."</b></p></div></li></a>";
      }
    } else {echo "Either no questions are created or search result returns empty..";}?>

Although you are using a prepared statement, you are still open for SQL injection. Have a look [here](https://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement) on how to use a prepared statement with `LIKE` — DarkBee, Apr 01 '22 at 10:06
Your `LIKE` pattern will only match if the two words are next to each other in the text. So if you search for `blue bird` it will match `the blue bird is here` but not `the bird is blue`. You probably want full-text searching. — Barmar, Apr 01 '22 at 10:09
@DarkBee I remove the LIKE = ? on purpose for this test. Byt ty. — MauiRiis, Apr 01 '22 at 10:38
So far I have gotten this: $getknowledge = $conn->prepare("SELECT * FROM knowmed_main WHERE MATCH (knowmed_content) AGAINST ('%".$SearchIt."%' IN BOOLEAN MODE)"); but if I search 'bib' it will not return post were 'bibliotek' is in. — MauiRiis, Apr 01 '22 at 11:47

Vikraant Vishwakarma · Answer 1 · 2022-04-01T11:15:56.380

0

you can use mysql regexp. it search based on given pattern.

mysql regexp doc

try this code and change respect table_name, column_name

select * from table_name where column_name regexp 'first_word|second_word'

edited Apr 01 '22 at 11:15

answered Apr 01 '22 at 10:10

Vikraant Vishwakarma

63
11

2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 01 '22 at 10:54

score 0 · Answer 2 · answered Apr 01 '22 at 11:58

0

Found it.

$getknowledge = $conn->prepare("SELECT * FROM knowmed_main WHERE MATCH (knowmed_content) AGAINST ('*".$SearchIt."*' IN BOOLEAN MODE)");

answered Apr 01 '22 at 11:58

MauiRiis

21
5

WHERE MATCH AGAINST any part of a word

2 Answers2