title: "Fuzzy" posts tags and posts titles search in mysql and php
body: <p>I want to create a search function that searches for posts based on their title and the tags the posts has. I wonder if it is possible to do this with just a mysql query or if I need to use 2 queries and then sort the results using php.</p>
  
  <p>The tables I want to query have the following structure:
  <a href="https://i.stack.imgur.com/x8cy5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/x8cy5.png" alt="Table structure"></a></p>
  
  <p>I've tried doing something like this, but it returns duplicate posts:</p>
  
  <pre><code>SELECT post_tags.id, post_tags.tag_id, posts.id, tags.tag_name, 
  posts.title
  FROM post_tags
  JOIN tags ON (tags.tag_name LIKE '%something%' AND post_tags.tag_id = tags.id)
  JOIN posts ON (posts.title LIKE '%something%' OR post_tags.post_id = posts.id);
  </code></pre>
  
  <p>I've also heard somewhere that using <code>LIKE</code> in mysql is really slow because there is no indexing.</p>
  
  <p>My question is therefore, what is best practice for this case, if there is one?</p>
  
  <p>-- edit -- </p>
  
  <p>I added the php code which uses 2 queries to achieve what I want</p>
  
  <pre><code>function search($query, $con = null){
      $conn = (isset($con) ? $con : get_conn());
      $logged_in_user_id = current_user.id;
  
      $posts = Array();
      $param = "%$query%";
      if($stmt = $conn-&gt;prepare("SELECT posts.id, posts.title ...
                                        FROM post_tags
                                        JOIN tags ON  post_tags.tag_id = tags.id
                                        JOIN posts ON  (post_tags.post_id = posts.id OR posts.title LIKE ?)
                                        WHERE posts.title LIKE ? OR tags.tag_name LIKE ?;")){
          $stmt-&gt;bind_param('sss', $param,$param, $param);
          $stmt-&gt;execute();
          $stmt-&gt;bind_result($id, $title ...);
          while($stmt-&gt;fetch()){
              if(!array_key_exists($id, $posts)){
                  $posts[$id] = new Post($id, $title ...);
              }
          }
      }
  
      return $posts;
  }
  </code></pre>