Use Array data in another mysql query using PHP and HTML select option-CodePudding

Dears,

I have a problem with my php code.. What I am trying to do is to use the array data in a second mysql query to list the available options.

Explanation:

$conn = mysqli_connect($dbhost,$dbuser,$dbpass) or sqlerror();
mysqli_select_db($conn,$dbname);
$sql = mysqli_query($conn,"SELECT * FROM ADMIN WHERE id='".$_SESSION["adminusername"]."'");
$isadmin=mysqli_fetch_array($sql);
$arraydata= $isadmin["Files"];
mysqli_close($conn);
$array3 = explode(',',$arraydata);

Now, The code above give me: $arraydata = "26,27,28,29"

I used $array3 to remove the comma .. then tried foreach as following code should be executed:

$conn = mysqli_connect($dbhost,$dbuser,$dbpass) or sqlerror();
mysqli_select_db($conn,$dbname);
foreach ($array3 as $singleID) {
    $sql=mysqli_query($conn,"SELECT FileID,FileTitle FROM Servers WHERE 
        FileType='CFG' AND FileID='".$singleID."'");
    if(mysqli_num_rows($sql)){
        $select= '<select  name="serverfile" tabindex="6">';
        while($rs=mysqli_fetch_array($sql)){
            $select.='<option value="'.$rs['FileID'].'">'.$rs['FileTitle'].'</option>';
        }
    }
    $select.='</select>';
    echo $select;
}

I was hopping this will show a select options for the items in Array that the condition in the second mysql query match, if array item found show it in the options of select ..

Anyone can fix this?

CodePudding user response：

Start the <select> before the outer loop, not each time through.

$select= '<select  name="serverfile" tabindex="6">';
foreach ($array3 as $singleID) {
    $sql=mysqli_query($conn,"SELECT FileID,FileTitle FROM Servers WHERE 
        FileType='CFG' AND FileID='".$singleID."'");
    while($rs=mysqli_fetch_array($sql)){
        $select.='<option value="'.$rs['FileID'].'">'.$rs['FileTitle'].'</option>';
    }
}
$select.='</select>';
echo $select;

As mentioned in the comments, you can combine the two queries with a JOIN. I also show how to use a parameter to prevent SQL injection.

$stmt = mysqli_prepare($conn, "
    SELECT FileID, FileTitle
    FROM Servers AS s
    JOIN ADMIN AS a ON FIND_IN_SET(s.FileID, Servers.Files)
    WHERE s.FileType = 'CFG'
    AND a.id = ?");
mysqli_stmt_bind_param($stmt, "s", $_SESSION["adminusername"]);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$select= '<select  name="serverfile" tabindex="6">';
while($rs=mysqli_fetch_array($result)){
    $select.='<option value="'.$rs['FileID'].'">'.$rs['FileTitle'].'</option>';
}
$select.='</select>';
echo $select;