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

Question

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?

Why don't you just join the servers table into the original query? You also are open to SQL injections. Always parameterize queries. — user3783243
– user3783243, Commented Dec 19, 2021 at 1:42
I could join servers table into the original query, but still my problem with foreach using <select option > in html .. — B Happy
– B Happy, Commented Dec 19, 2021 at 1:55
You shouldn't have nested loops. Just do one query that gets all of the options, by joining the two tables. — Barmar
– Barmar, Commented Dec 19, 2021 at 3:09
It's also bad design to put delimited lists in a table. See stackoverflow.com/questions/3653462/… — Barmar
– Barmar, Commented Dec 19, 2021 at 3:10
@Barmar How can I do query gets all information? what about the select option drop list in html, I want it to show all data from the queries — B Happy
– B Happy, Commented Dec 19, 2021 at 3:17

Barmar · Accepted Answer · 2021-12-19 03:20:11Z

1

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;

answered Dec 19, 2021 at 3:20

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

B Happy Over a year ago

Thank you for this solution, Best Answer

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related