CODEWITHSUNDEEP
phphtmlmysql
B Happy
B Happy

Reputation: 31

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

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 class="smallInput" 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?

Upvotes: 0

Views: 49

Answers (1)

Barmar
Barmar

Reputation: 781750

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

$select= '<select class="smallInput" 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 class="smallInput" name="serverfile" tabindex="6">';
while($rs=mysqli_fetch_array($result)){
    $select.='<option value="'.$rs['FileID'].'">'.$rs['FileTitle'].'</option>';
}
$select.='</select>';
echo $select;

Upvotes: 1

Related Questions