Use php to find specific values in results of mysql query

Question

I have an sql query that looks like this:

//do a query to find all the samples in the currently selected rack 
$sql = "SELECT SampleID, ColumnNumber, RowNumber FROM Samples WHERE Rack = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $rack);
$stmt->execute();
$rackContents = $stmt->get_result();

In each rack, a ColumnNumber and RowNumber combination describes a position in the rack, starting with:
ColumnNumber = 1, RowNumber = 1 then
ColumnNumber = 2, RowNumber = 1
and so on until (for example, in a 10x10 rack)
ColumnNumber = 10, RowNumber = 10.

I want to display the SampleIDs in each position of the rack in a table. Using a nested loop, I can draw a table that has a <td> for each rack position. What I can't figure out is how to display the SampleID based on the ColumnNumber and RowNumber combination for each position.
This is how I build the table:

//start a loop that visits each row
    $rownumber = 1; 
    
    while ($rownumber <= $numberofrows){
        //start a loop that visits each column
        //start a row
        echo "<tr>";
        $columnnumber = 0; 
        while ($columnnumber <= $numberofcolumns){
            
            if ($columnnumber == 0){
            echo "<td>" . $rownumber . "</td>";
            }
            else{
                
                //find the sample number for the relevant column and row
                //inside the array we got in an sql query earlier, $rackContents
                    echo "<td>" . x . "</td>";
                //What do I put in place of the x to output the appropriate 
                //SampleId for the ColumnNumber/RowNumber combo?
            }

        $columnnumber++;
        }//end the columnnumber loop
    
    //end the row
    echo "</tr>";
    $rownumber++;    
    }//end the rownumber loop

I've been looking at array_search, but I just can't get anywhere.

Answer 1

If I am understanding you correctly I think you just need to fetch the array from your result. Although I am not sure this is the result you actually want.

while ($rackContent = $rackContents->fetch_array(MYSQLI_NUM))
    $rownumber = 1;
    while ($rownumber <= $numberofrows){
        echo "<tr>";
        $columnnumber = 0; 
        while ($columnnumber <= 3){
            
            if ($columnnumber == 0){
                echo "<td>" . $rownumber . "</td>";
            } else {
                echo "<td>" . $rackContent['SampleID'] . "</td>";
            }

        $columnnumber++;
    
    echo "</tr>";
    $rownumber++;    
    }
}

But I think you probably want something more like this.

while ($rackContent = $rackContents->fetch_array(MYSQLI_NUM))
        echo "<tr>";
        echo "<td>" . $rownumber . "</td>";
        echo "<td>" . $rackContent['SampleID'] . "</td>";
        echo "<td>" . $rackContent['ColumnNumber'] . "</td>";
        echo "<td>" . $rackContent['RowNumber'] . "</td>";
        echo "</tr>"; 
}