Bootstrap modal shown MySQL data twice

Question

I am trying to create a searching module with PHP 7, MySQL and Bootstrap. The only propblem is that if there is more domain for one role, the role is shown twice with the 2 different domain.

This is the popup window Popup window

This is the 3 table: Tables

I was trying to select the multiple datas from the database and add it to an array, but I am new in PHP and MySQL. This is the PHP code:

$output = '';  


    $sql="SELECT * FROM (SELECT role.role_id AS roleID, role.role_name AS roleNAME, domain.domain_name AS domainNAME
          FROM role 
          LEFT OUTER JOIN role_domain ON role.role_id=role_domain.role_id 
          LEFT OUTER JOIN domain ON domain.domain_id=role_domain.domain_id
          UNION 
          SELECT role.role_id AS roleID, role.role_name AS roleNAME, domain.domain_name AS domainNAME
          FROM role_domain
          RIGHT OUTER JOIN domain ON domain.domain_id=role_domain.domain_id
          RIGHT OUTER JOIN role ON role.role_id=role_domain.role_id) AS U
          WHERE U.roleID='".$_POST["szerep_id"]."'";

    $result = mysqli_query($conn,$sql);


    // A query that stores the multiple roles
    $query="SELECT role.role_name AS roleName, COUNT(role_domain.role_id) as role_count FROM role
            LEFT OUTER JOIN role_domain ON role.role_id = role_domain.role_id
            GROUP BY role_domain.role_id
            HAVING role_count > 1";

    $result1= mysqli_query($conn, $query);

    // Put the multiple roles to an array
    while($row=mysqli_fetch_array($result1))
        {
            $inspector[]=$row["roleName"];
        }

    // Create the table
    $output .= '  
    <div class="table-responsive">  
        <table class="table table-bordered">'; 

    // If the query has an error, it writes the error
    if($result===false)
    {
        die(print_r(mysqli_error($conn), true));
    }


    // Fill up the Popup Window with the information provided
    while($row=mysqli_fetch_array($result))   
    { 
            $output .= '  
            <tr>  
                 <td><label><b>Role name:</b></label></td>  
                 <td>'.$row["roleNAME"].'</td>  
            </tr>  

            <tr>  
                 <td><label><b>Domain:</b></label></td>  
                 <td>'.$row["domainNAME"].'</td>  
            </tr>

        ';
    }  

    $output .= "</table></div>"; 

    echo $output; 

    // Frees all resources for the specified statement
    $stmt=$conn->prepare($sql);
    mysqli_stmt_free_result($stmt);
}     

}

else
{
    echo "Connection could not be established.<br />";

    die(print_r(mysqli_error($conn), true));
}

Thanks for the help!

That's not your _only_ problem. Your code is also vulnerable to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). Never trust user input, including the contents of `$_POST`! You should be using [parameter binding](http://docs.php.net/manual/en/mysqli-stmt.bind-param.php) in your queries. — ChrisGPT was on strike, Apr 16 '18 at 12:36
Your question is a bit hard to follow, but I suspect you need to use [`DISTINCT`](https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html) in one of your selects. I'm guessing the "query that stores the multiple roles". — ChrisGPT was on strike, Apr 16 '18 at 12:37
Could you explain more about what you try to achieve? I don't really understand what should show up on you search — NoOorZ24, Apr 16 '18 at 13:29
I want to show the role once, but if there is more than 1 domain for a role, the role shown twice. — , Apr 16 '18 at 13:30
So you want to display 1) only roles OR 2) roles with all their domains OR 3) all roles and all domains OR 4) all roles that have a domain? — NoOorZ24, Apr 16 '18 at 13:33

score 0 · Answer 1 · answered Apr 16 '18 at 12:53

Assuming your code is working,
Your intended goal to only show 1 record per column with the same value you can simplu use : SELECT DISTINCT(you_column_name)

For example if you want your role appear only once with same domain, you can DISTINCT(domain.domain_id)
I hope this solve your problem.

score 0 · Accepted Answer · answered Apr 16 '18 at 13:49

As you need to display:

Roles with all their domains

You should change your layout to something like:

.table {
    border-collapse: collapse;
    width: 100%;
}

.table td, .table th {
    border: 1px solid #ddd;
    padding: 8px;
}

<table class="table">
    <tr>
        <td>Role name</td>
        <td>role1</td>
    </tr>
    <tr>
        <td>Domains</td>
        <td>
            <ul>
                <li>domain1</li>
                <li>domain2</li>
            </ul>
        </td>
    </tr>
</table>

About SQL:

store your data in array and then group that array: Group array values based on key in php?

groups will make it easy to output table in format that I provided

Update: If you don't want to group your array you can simply order query by role and create new role field when role's id changes

There's also group_concat in mysql, which would negate the need to loop through multiple domain values. — John Bell, Apr 16 '18 at 14:02

Bootstrap modal shown MySQL data twice

2 Answers2