0

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?

Barmar
  • 741,623
  • 53
  • 500
  • 612
B Happy
  • 27
  • 2
  • 11
  • 1
    Why don't you just join the servers table into the original query? You also are open to SQL injections. Always parameterize queries. – user3783243 Dec 19 '21 at 01:42
  • I could join servers table into the original query, but still my problem with foreach using – B Happy Dec 19 '21 at 01:55
  • 1
    You shouldn't have nested loops. Just do one query that gets all of the options, by joining the two tables. – Barmar Dec 19 '21 at 03:09
  • It's also bad design to put delimited lists in a table. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Barmar Dec 19 '21 at 03: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 Dec 19 '21 at 03:17

1 Answers1

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;
Barmar
  • 741,623
  • 53
  • 500
  • 612