mercredi 15 juin 2016

How to select a row based on a part of value?

I have this table:

// qanda
+----+---------+-----------------------+------------------------+
| id |  title  |        content        |          tags          |
+----+---------+-----------------------+------------------------+
| 1  | title1  | content1              | <a>tagA<a/><a>tagB</a> |
| 2  | title2  | content2              | <a>tagA</a><a>tagC</a> |
| 3  | title3  | content3              | <a>tagM</a><a>tagB</a> |
| 4  | title4  | content4              | <a>tagD</a>            |
| 5  | title5  | content5              | <a>tagA</a><a>tagG</a> |
+----+---------+-----------------------+------------------------+

And I want to select every row which has tagB. So this is expected result:

+----+---------+-----------------------+------------------------+
| 1  | title1  | content1              | <a>tagA<a/><a>tagB</a> |
| 3  | title3  | content3              | <a>tagM</a><a>tagB</a> |
+----+---------+-----------------------+------------------------+

How can I do that?


Currently I have created an FULLTEXT index on tags column and find it like this:

. . . WHERE MATCH ( tags ) AGAINST ( :tag )

But my approach has two problems:

  1. Sometimes tags columns is containing something like this value: <a class="tagA">tagB</a>. In this case I want to select that row only for tagB, But it will be selected both for tagA and tagB.

  2. I cannot select multiple tags. Ex: I cannot select rows which have either tagA or tagB or tagC.

Aucun commentaire:

Enregistrer un commentaire