lustfulglances lustfulglances - 7 months ago 17
SQL Question

PHP returning tripled results

So I know what the script is doing and I think I understand why it is showing up 3 times, but I'm not sure how to fix it!

Here is the script as it stands:

$flagquery = "SELECT incident_flag FROM incident_attributes WHERE incident=2157";
$flagresult = mysqli_query($conn, $flagquery);
if (mysqli_num_rows($flagresult) > 0) {
while($firow = mysqli_fetch_array($flagresult)){

foreach ($items as $flagrow) {
$id = $flagrow['id'];
$name = htmlspecialchars($flagrow['name'], ENT_QUOTES);

$form .= " <div class='form-group'>
<label class='col-sm-2 control-label'>$name</label>
<div id='name-input-wrapper' class='col-sm-8 controls'>
<input type='checkbox' value='$id' name='flags[]' ";
if ($firow["incident_flag"] == $id) $form .= 'checked';

$form .= ">
</div>
</div>";
}
}
}
echo $form;


Here is the related array

Array
(
[1] ( Array
(
[id] => 1
[name] => Bag
[flag] => 0
)

[2] => Array
(
[id] => 2
[name] => Screen
[flag] => 0
)

[3] => Array
(
[id] => 3
[name] => HD
[flag] => 0
)

)


Here is the mysql DB incident_attributes

id incident incident_flag
1 2157 1
2 2157 2
3 2157 3


The whole goal of the script is to flag the box checked. Any other approaches to this?

Answer

You echo 3 checkbox for each result's row (3), so you have 9 checkboxes instead of 3. You have to invert the two foreach, and to limit nested foreach to checked evaluation.

Since the query result become the nested foreach, you have to fetch rows before executing loops:

$firows = mysqli_fetch_all( $flagresult, MYSQLI_ASSOC );

foreach( $items as $flagrow ) 
{
    $id    = $flagrow['id'];
    $name  = htmlspecialchars( $flagrow['name'], ENT_QUOTES );

    $form .= " <div class='form-group'>
                    <label class='col-sm-2 control-label'>$name</label>
                    <div id='name-input-wrapper' class='col-sm-8 controls'>
                    <input type='checkbox' value='$id' name='flags[]' ";
    foreach( $firows as $firow )
    {
        if( $firow["incident_flag"] == $id ) $form .= 'checked';
    }

    $form .= ">
                    </div>
                </div>";
}

As alternative (without nested foreach):

$firows = mysqli_fetch_all( $flagresult, MYSQLI_ASSOC );
$flags  = array_column( $firows, 'incident_flag' ); // $flags now is [ 1,2,3 ]

foreach( $items as $flagrow ) 
{
    (...)
    $form .= " <div class='form-group'> ... "
    if( in_array( $id, $flags ) ) $form .= 'checked';
    (...)
}
Comments