SQL Search Query show same results many times although it is only single time in my database-CodePudding

Project- Database name- test2

i have 2 tables in my database

table1 contains

Id  Name    Mobile  Email   
17  name1   1111    name1@test  
18  name2   2222    name2@test  
28  name3   3333    name3@test

table2 contains

Sr. Id  Paymobile   Month   Amount  Date    
1   28  3333        Jan     200     2021-01-06  
2   28  3333        Feb     400     2021-02-06  
3   28  3333        Apr     600     2021-04-08  
4   17  1111        Mar     400     2021-03-05  
6   18  2222        Aug     100     2021-08-27  
7   17  1111        Jun     600     2021-06-21

table1 has single and unique records, table2 has include payment history so the id and mobile have multiple times.

What i Want If i search the mobile on or email id then it will also give the results in two tables like 1st table show ID,Name,Mobile,Email from table1 AND 2nd table show Month,Amount,Date from table2. all is good but there is one issue which is mention below.

Issue when i search the mobile no. 3333 or 2222 in my webpage then it show both results in two table as i want like table1 show Id,Name,Mobile,Email from table1 AND 2nd table show Month,Amount,Date from table2.

But the Main Problem is that it will show Id,Name,Mobile,Email many times although it have single records in my database. like enter image description here

My Configuration

1-form.php

<!DOCTYPE html>
<html>
  <body>
    <!-- (A) SEARCH FORM -->
    <form method="post" action="1-form.php">
      <h1>SEARCH FOR USERS</h1>
      <input type="text" name="search" required/>
      <input type="submit" value="Search"/>
    </form>
 
    <table>
    <style>
        table { font-family: arial, sans-serif;
                border-collapse: collapse;
                width: 100%;
    }
    td, th {border: 1px solid #dddddd;
            text-align: left;
            padding: 8px;
    }

    tr:nth-child(even) {
        background-color: #dddddd;
    }
    </style>
        <tr>
          <th>Id</th>
          <th>Name</th>
          <th>Mobile</th>
          <th>Email</th>
        </tr>
<?php
// (B2) DISPLAY RESULTS 
?>
<?php
// (B) PROCESS SEARCH WHEN FORM SUBMITTED
if (isset($_POST["search"])) {
    // (B1) SEARCH FOR USERS
    require "2-search.php";
?>
  
<?php
    // (B2) DISPLAY RESULTS 
    if(isset($_POST) && array_key_exists('search',$_POST)) {
        if (count($results) > 0) { foreach ($results as $r) {
?>  
            <tr>  
                <td> <?php echo $r['Id'] ;?> </td>                
                <td> <?php echo $r['Name'] ;?> </td>
                <td> <?php echo $r['Mobile'] ;?> </td>
                <td> <?php echo $r['Email'] ;?> </td>
            </tr>
<?php
        }
    }
} else { 
    echo "No results found"; }
}
?>


  </table>
  <p>
    <table>
        <tr>
            <th>Month</th>
            <th>Amount</th>
            <th>Date</th>
        </tr>
<?php
// (B2) DISPLAY RESULTS
if (count($results) > 0) { 
    foreach ($results as $r) {
?>  
        <tr>                  
            <td> <?php echo $r['Month'] ;?> </td>
            <td> <?php echo $r['Amount'] ;?> </td>
            <td> <?php echo $r['Date'] ;?> </td>
        </tr>
<?php
    }
} else { 
    echo "No results found"; 
}
?>
</table>
</html>

and

2-search.php

<?php
// (A) DATABASE CONFIG - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test2");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (B) CONNECT TO DATABASE
try {
    $pdo = new PDO(
        "mysql:host=".DB_HOST.";charset=".DB_CHARSET.";dbname=".DB_NAME,
        DB_USER, DB_PASSWORD, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );
} catch (Exception $ex) { 
    exit($ex->getMessage()); 
}

// (C) SEARCH
$stmt = $pdo->prepare("SELECT table1.*, table2.* 
                        FROM table1 
                        INNER JOIN table2 ON table1.Id = table2.Id 
                        WHERE `Name` LIKE ? OR `Mobile` LIKE ?");
$stmt->execute(["%".$_POST["search"]."%", "%".$_POST["search"]."%"]);
$results = $stmt->fetchAll();
if (isset($_POST["ajax"])) { 
    echo json_encode($results); 
}

CodePudding user response：

An INNER JOIN will show all matching rows for both tables. If you hit ID 28 which is present 1 time in table1 but 3 times in table2, you will get 3 rows in total.

You can add a GROUP BY Id to your query or make 2 queries - one for each table - instead of 1 query with an INNER JOIN.

CodePudding user response：

you can use the keyword DISTINCT :

SELECT DISTINCT table1.*, table2.* 
FROM table1 
INNER JOIN table2 ON table1.Id = table2.Id 
WHERE `Name` LIKE ? OR `Mobile` LIKE ?