Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to avoid duplicate data on the table in PHP

I am currently testing this php code to insert the report data in the database but when I try to submit the report, i get some duplicate results as well

Here is my PHP code

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
    <table>
        <thead>
        <tr>
            <th>Room</th>
            <th>Devices</th>
            <th>Status</th>
            <th>Cause (If the status is broken)</th>
        </tr>
    </thead>

    <?php
    $db_host = "localhost";
    $db_username = "root";
    $db_pass = "";
    $db_name = "umsdb";
    $conn = mysqli_connect($db_host,$db_username,$db_pass,$db_name)
    or die("Error, cannot connect to MySQL");
                            
    $sql= "SELECT * FROM user 
    INNER JOIN location ON user.lid=location.lid 
    INNER JOIN room ON location.lid=room.lid
    INNER JOIN devices ON room.rid=devices.rid;";
    $result = $conn->query($sql);
    $resultCheck = mysqli_num_rows($result);

    if($resultCheck > 0)
    {
             while($row=mysqli_fetch_assoc($result))
        { 
            $room = $row['ROOM_NAME'];
            $device = $row['DEVICE_NAME'];
                                
            echo "<tr>
            <td> " . $room . "</td>
            <td> " . $device . "</td>
            <td><select id ='status[]' name='status[]' size = '1'>
            <option>Select the status</option>
            <option value='Good'>Good</option>
            <option value='Broken'>Broken</option>
            </select>
            </td>
            <td><input type='text' class='form-control' name='cause'></td>
            </tr>";
            $prevRoom = false;
            $prevDevice = false;
            while (next ($row))
            {
                if($prevRoom == $room)
                {
                    //If subject name same as last, then empty value
                            // - or do whatever you want to do differently
                    break;
                    if($prevDevice = $device)
                    {
                        break;
                    }
                    else
                    {
                        $prevDevice = $device;
                        break;
                    }
                }
                else
                {
                //somehow if the subject name is a duplicate echo it once and carry on with the other variables looping
                    $prevRoom = $room;
                    break;
                }
            }
                                    
                                    
        }
    }
    else
    {
        echo "<tr rowspan='3'>No data filled</tr>";
    }
    ?>      
    </table>
    <input type="submit" name="submit" class="btn-primary"> 
    <?php
        $db_host = "localhost";
        $db_username = "root";
        $db_pass = "";
        $db_name = "umsdb";
        $conn = mysqli_connect($db_host,$db_username,$db_pass,$db_name) or die("Error, cannot connect to MySQL");
                            
        if(isset($_POST['submit']))
        {
        $sql= "SELECT * FROM user 
        INNER JOIN location ON user.lid=location.lid 
        INNER JOIN room ON location.lid=room.lid
        INNER JOIN devices ON room.rid=devices.rid;";
        $result = $conn->query($sql);
        $resultCheck = mysqli_num_rows($result);
        if($resultCheck > 0)
        {
            while($row=mysqli_fetch_assoc($result))
            { 
                $uid = $row['uid'];
                $did = $row['did'];

                $status = $_POST['status'];
                foreach($status as $condition)
                {
                    $cause = mysqli_real_escape_string($conn, $_POST['cause']);
                    $date = date("Y/m/d");
                    $sql = "INSERT INTO report (uid,did,R_STATUS,CAUSE,R_DATE)
                    VALUES ('$uid','$did','$condition','$cause','$date');";
                    if(mysqli_multi_query($conn, $sql))
                    {
                        echo "Adding Successfully!";
                        header("Location:home.php");
                    }
                    else
                    {
                        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
                    }
                                            
                }   
            }
        }
        else
        {
        echo "No data found";
        }

                                
    }
    mysqli_close($conn);
    ?>                      
    </form>

This is the report.php. I hope everyone can understand this code because it’s been a while i didn’t make this code very complex

My output for that php code after submitting report has become like this

Devices Status Cause
Smart TV Good Broken Screen
Smart TV Broken Broken Screen
Portable Audio Good Broken Screen
Portable Audio Broken Broken Screen

My expected output after submitting should be like this

Devices Status Cause
Smart TV Broken Broken Screen
Portable Audio Good
Tags:

2

Answers


  1. 0

    Update your sql query with DISTINCT keyword:

    sql= "SELECT DISTINCT * FROM user 
        INNER JOIN location ON user.lid=location.lid 
        INNER JOIN room ON location.lid=room.lid
        INNER JOIN devices ON room.rid=devices.rid;";

    I’m sure this will resolve your issue.

    Login or Signup to reply.
  2. 0

    I provide two ways to avoid this problem:

    Method 1: Create a joint unique index in MySQL

    Method 2: Before inserting, check whether the content to be inserted already exists. If it exists, do not insert it. Otherwise, insert it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search