I'm new here. I have only basic knowledge of PHP and Mysql.
I have a math expression like:
[13 + 24 + 92 - x1x2]
which is stored in MySQL database as:
[1<sup>3</sup> + 2<sup>4</sup> + 9<sup>2</sup> - x<sub>1</sub>x<sub>2</sub>].
I want to search that column from MySQL database using the input string $str='1^3 + 2^4 + 9^2 - x_1x_2';
. The MySQL column is set as a full-text index so I need to search it using MATCH AGAINST method.
UPDATE: I added the relevant code from my application below.
My question is why the MySQL query returning 0. if I even change the input string the same thing is happening. Like
$str=$_GET['search']; //$_GET['search'] is equal to '1<sup>3</sup> + 2<sup>4</sup> + 9<sup>2</sup> - x<sub>1</sub>x<sub>2</sub>'
//the $str contain the search string
$query = "(Select col1,col2,MATCH (col3) AGAINST ('".$str."' IN NATURAL LANGUAGE MODE) AS relevance from table_name WHERE MATCH (col3) AGAINST ('".$str."' IN NATURAL LANGUAGE MODE))";
$run = mysqli_query($connect, $query);
$foundnum = mysqli_num_rows($run);
//$foundnum returning 0
Since the string 1^3
is not the same as 1<sup>3</sup>
, there is no SQL method to have them match. Also, there is no simple amount of PHP code to declare that they are the same.
You need to come up with a canonical form for "power" and use it in both the data in the table and the search string. Probably you should pick the "math" representation for searching 1^3
. You could use a second column for the HTML representation 1<sup>3</sup>
.
But, there is another problem. FULLTEXT
indexing works with "words" separated by spacing and punctuation. So the words in those are 1
, 3
, and sup
.
Still more trouble... FULLTEXT
defaults to a minimum of 3 letters for a "word". So sup
would be the only "word".
So, what to do? If you can decide on a canonical form, then see =
, LIKE
, or REGEXP
for string comparisons, not FULLTEXT
.