Integral Integral - 4 months ago 18
MySQL Question

mysql matching select like hashtags, without fulltext

I can't fulltext setting ft_min_word_len because i use web-hosting.


DB Field

hashtag - varchar(256) / utf8_general_ci

DB input value example

| #abc |

| #abc #123 |

| #123 |

| #abc #123 #abcd |

| #1234 |
| #12345 |

I want to search '#123' result

| #abc #123 |

| #123 |

| #abc #123 #abcd |

PHP

$word = $mysqli->real_escape_string($_POST['word']);
mysqli_query($mysqli, "... where (p.hashtag REGEXP '^(.*?(\\\b$word\\\b)[^$]*)$' ) ... ");


I want to more than systax.


add comment

DB Input value more detail

id | title | descript | hastag | viewCnt ...

1 | text1 | text0001 | #123 | 0

2 | text2 | text0002 | #123 #abc | 0

3 | text3 | text0003 | #12345 | 0

...

Answer

You should fix your data structure to have a table with one row per hash tag value and id in the table you are using.

But in any case, you can do what you want using like:

select hashtag
from db
where concat(hashtag, '#') like concat('%', $search, '#%');
Comments