AJAX live search is duplicating results

I have this script that reads text from an input field than sends the value to a php file to search all tables in the database("pharmacie"). I have the same data in different tables(example: "ABILIFY 10MG COMP. B/28" existes in two tables). How do i display it only once. I tried SELECT DISTINCT.

Results output

<script>
    $(document).ready(function() {
      $('.search input[type="text"]').on("keyup input", function() {
        /* Get input value on change */
        var inputVal = $(this).val();
        var resultDropdown = $(this).siblings(".result");
        if (inputVal.length) {
          $.get("livesearch.php", {
            term: inputVal
          }).done(function(data) {
            // Display the returned data in browser
            resultDropdown.html(data);
          });
        } else {
          resultDropdown.empty();
        }
      });

      // Set search input value on click of result item
      $(document).on("click", ".result p", function() {
        $(this).parents(".search").find('input[type="text"]').val($(this).text());
        $(this).parent(".result").empty();
      });
    });
  </script>

livesearch.php

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "pharmacie");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$tables = mysqli_query($link, "SHOW TABLES");
while ($table = mysqli_fetch_object($tables))
{
    $table_name = $table->{"Tables_in_pharmacie"};
 
if(isset($_REQUEST["term"])){
    // Prepare a select statement
    $sql = "SELECT * FROM " . $table_name . " WHERE Nom_medicine LIKE ?";
    
    if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "s", $param_term);
        
        // Set parameters
        $param_term = $_REQUEST["term"] . '%';
        
        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            $result = mysqli_stmt_get_result($stmt);
            
            // Check number of rows in the result set
            if(mysqli_num_rows($result) > 0){
                // Fetch result rows as an associative array
                while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                    echo "<p>" . $row["Nom_medicine"] . "</p>";
                }
            } 
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        }
    }
     
    // Close statement
    mysqli_stmt_close($stmt);
}
 
}
// close connection
mysqli_close($link);
?>

You loop through all tables, and echo the result for each table, which is why select distinct did not work. You could write back, which results you already returned and skip them.

I wouldn't probably solve it that way, you could rewrite the script even more to archive all of this in a single select, or you could even exclude already printed ones directly in your sql query for faster performance, but as a quick fix this should work for you:

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "pharmacie");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
$alreadyReturned = [];
$tables = mysqli_query($link, "SHOW TABLES");
while ($table = mysqli_fetch_object($tables))
{
    $table_name = $table->{"Tables_in_pharmacie"};
 
if(isset($_REQUEST["term"])){
    // Prepare a select statement
    $sql = "SELECT * FROM " . $table_name . " WHERE Nom_medicine LIKE ?";
    
    if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "s", $param_term);
        
        // Set parameters
        $param_term = $_REQUEST["term"] . '%';
        
        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            $result = mysqli_stmt_get_result($stmt);
            
            // Check number of rows in the result set
            if(mysqli_num_rows($result) > 0){
                // Fetch result rows as an associative array
                while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                    if (in_array($row["Nom_medicine"], $alreadyReturned, true))
                    {
                        continue;
                    }
                    echo "<p>" . $row["Nom_medicine"] . "</p>";
                    $alreadyReturned[] = $row["Nom_medicine"];
                }
            } 
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        }
    }
     
    // Close statement
    mysqli_stmt_close($stmt);
}
 
}
// close connection
mysqli_close($link);
?>