Using Fulltext leads to wrong results?

Question

I am trying to perform a simple text search.

However, if I use mysqli_real_escape_string it makes the program buggy.

I have a Fulltext column title, but the functionality does not work in phpMyadmin after I do :

PhpMyadmin

 - **id|title** 
 - 1|cool boy
 - 2|cool bottle
 - 3|cool guy
 - 4|hot man

Here is the rest of my php files :

file1.php

   //from a form

   $str = $_POST['q'];
   $str=mysqli_real_escape_string($con,$str);
   mysqli_query($con,"insert into tbl(title)values($str)");

file2.php

$str = $_GET['q'];

$data = mysqli_query($con,"select * from tbl where match(title)against('$str')");
while($row = mysqli_fetch_assoc($data)){
   //do stuff . but yields nothing
}

How can I properly perform a Fulltext search of my title column?

echo_Me · Answer 1 · 2014-05-09T14:46:02.027

1

Try this

  select * from tbl where title LIKE '%cool%'

EDIT: if you wanna do fulltext search then change this

  against('cool')

to

 against('cool' IN BOOLEAN MODE)

In MySQL there are three types of full-text searches:

boolean search
natural language search (used by default)
query expansion search

From MySQL manual entry:

DEMO HERE

EDIT2:

do in file1.php

   session_start(); //this will be in very top of your file
   $str = $_POST['q'];
   $_SESSION['str'] = $str;
   $str=mysqli_real_escape_string($con,$str);
   mysqli_query($con,"insert into tbl(title)values($str)");

in file2.php

   session_start(); //this will be in very top of your file
   $str = $_SESSION['str'];
   $data = mysqli_query(.........

edited May 09 '14 at 14:46

answered May 09 '14 at 13:33

echo_Me

37,078
5
58
78

makes it too slow need to use fulltext searching – May 09 '14 at 13:34
try this and show the result please `SHOW TABLE STATUS` – echo_Me May 09 '14 at 13:36
results empty rows `0` – May 09 '14 at 13:42
have you checked the demo and changed what i suggested ? – echo_Me May 09 '14 at 13:44
if the values are in table why are you showing the insert code ? you wanna select now right ? ,it can be something wrong in your code maybe , this query i gave you works perfect. – echo_Me May 09 '14 at 13:51
are you fetching the query ? – echo_Me May 09 '14 at 13:57
1st i insert a `mysqli_real_escape_string` `$str` into db. Then i get the `$str` i.e `$str="cool"` from user and fetch the results using fulltext. – May 09 '14 at 13:59
you fetching like that ? mysqli_fetch_array – echo_Me May 09 '14 at 14:00
no sir it will not work GET like that , you should maybe use SESSION instead check my edited answer – echo_Me May 09 '14 at 14:43
the problem is not that @echo .... problem is `mysqli_real_escape_string` makes the string unidentifieable. – May 09 '14 at 14:44
you must be sure that `$_POST['q']` exist and there is , try echo it and see if there is , otherwise you have problem sending it from your form. – echo_Me May 09 '14 at 14:47

score 1 · Answer 2 · answered May 09 '14 at 13:34

1

select * from tbl where title LIKE 'cool%'

add only one % after your string/variable

answered May 09 '14 at 13:34

SagarPPanchal

9,839
6
34
62

Using Fulltext leads to wrong results?

2 Answers2