PHP hashtag system

Question

I’m developing a hashtag system in PHP, and my question is this::

There’s how I make one SELECT publications based on the content?

For example, the content saved in the database is:

Hi, are you all right? #bomdia

In no case the SELECT would be something like SELECT * FROM msgs WHERE conteudo LIKE %#bomdia% ?

I have seen several articles where, when saving the post in the database, all the detected hashtags are saved in a separate column in the table, for example "hashtags".

What’s the best way to do that?

Answer 1

As mentioned in the comments, the ideal is to separate the tag from the message. I would go a little further and make a table only for tags, taking into account that a message may have multiple or no tag:

Notice I added an index like UNIQUE tag name, to avoid duplicates and speed up searches.

To extract tags from the message use the preg_match_all(), saving in the database only the tag text, without the wire (#):

<?php

class Mensagem {

    protected $mensagem;
    protected $tags = [];

    public function __construct($messagem)
    {
        $this->mensagem = $messagem;
        $this->extractTags($messagem);
    }

    private function extractTags($mensagem)
    {
        // Casa tags como #dia #feliz #chateado
        // Não casa caracteres especias #so-pt
        $pattern = '/#(\w+)/';

        // Alternativa para incluir outros caracteres
        // Basta incluir entre os colchetes
        //$pattern = '/#([\w-]+)/';

        preg_match_all($pattern, $mensagem, $tags);

        // Utiliza o vetor com os grupos capturados entre parenteses
        $this->tags = $tags[1];
    }

    public function getMensagem()
    {
        return $this->mensagem;
    }

    public function getTags()
    {
        return $this->tags;
    }

}

To use the class:

$mensagem = "Partiu #ferias #praia #feliz #so-pt";

$msg = new Mensagem($mensagem);

var_dump($msg);

//Retorna:

object(Mensagem)#1 (2) {
  ["mensagem":protected]=>
  string(35) "Partiu #ferias #praia #feliz #so-pt"
  ["tags":protected]=>
  array(4) {
    [0]=>
    string(6) "ferias"
    [1]=>
    string(5) "praia"
    [2]=>
    string(5) "feliz"
    [3]=>
    string(5) "so"
  }
}

For the search use the following SELECT:

SELECT mensagem FROM mensagens
JOIN mensagens_tags ON mensagens_id_mensagem = id_mensagem
JOIN tags ON tags_id_tag = id_tag
WHERE nome = 'tag';

Example of the bank on sqlfiddle.

The next steps are to build the routine that will persist the tags in the database (insert the new tags and keep the old ones) and the system for the search. This I leave with you.