This bunch of queries seems...overkill

I have a racks table where a rack can be either 1-6


create table racks (
   rack_id INT NOT NULL AUTO_INCREMENT,
   room_id TINYINT,
   ...  
 PRIMARY KEY ( rack_id )
);

Id like put the racks for each room into a select box.
I run this

<?php
$sql = 'SELECT rack_id,room_id,title FROM racks WHERE room_id = 1';
$result = mysqli_query($conn, $sql);

$sql = 'SELECT rack_id,room_id,title FROM racks WHERE room_id = 2';
$result1 = mysqli_query($conn, $sql);

$sql = 'SELECT rack_id,room_id,title FROM racks WHERE room_id = 3';
$result2 = mysqli_query($conn, $sql);

$sql = 'SELECT rack_id,room_id,title FROM racks WHERE room_id = 4';
$result3 = mysqli_query($conn, $sql);

$sql = 'SELECT rack_id,room_id,title FROM racks WHERE room_id = 5';
$result4 = mysqli_query($conn, $sql);

$sql = 'SELECT rack_id,room_id,title FROM racks WHERE room_id = 6';
$result5 = mysqli_query($conn, $sql);
?>

then, I want to give each rooms racks a select box, I

<?php         
	if (mysqli_num_rows($result) > 0) {
		echo '<select class="form-control" id="Room1" name="Rack1" required>';
		echo '<option value="0">Select</option>';
		while($row = mysqli_fetch_assoc($result)) {

				echo '<option value="'.$row['rack_id'].'">'.$row['title'].'</option>';

		}
		echo '</select>';
	}
//I do this 5 more times

I was thinking, is there a better way to do this so I dont have to query the database soo much?

Select everything in one query then put the results into an array using the room_id as the key, then you can loop over the the array to create the dropdowns.

Here is a start to what I am talking about.

<?php

$racks = [
    1 => ['room_id' => 'A', 'title' => 'Title for A'],
    2 => ['room_id' => 'B', 'title' => 'Title for B'],
    3 => ['room_id' => 'C', 'title' => 'Title for C'],
    4 => ['room_id' => 'D', 'title' => 'Title for D'],
];

function rackDropdown($racks)
{
    foreach ($racks as $key => $values) {
        echo "<select class='form-control' id='Room$key' name='Rack$key' required>\n";
        echo "<option value='$key'>{$values['title']}</option>\n";
        echo '</select><br>';
    }
}

rackDropDown($racks);

ok,think I understand, so the query would be

$sql = 'SELECT rack_id,room_id,title FROM racks WHERE room_id <  0';
$rooms= mysqli_query($conn, $sql);

wouldnt that result in a rooms array, but how would you break it up into another array so we can group all the 1s, 2s, 3s, 4s, 5s, 6s together?

//something like
foreach($rooms as $key =>$value) {
   if($rooms['room_id'] = 1) {
      racks[1].push($key => $value);
    }
}

Post an SQL dump of your table and describe how you want to use the dropdowns. Do you want to just output 6 successive drop downs or do you want more control to call one at a time?

Why would room_id suddenly need to be negative? Is it ever negative?

Couldn’t you just use ORDER BY in the query to make it easy to group them? Or just using your own code, rather than

if($rooms['room_id'] = 1) {  // which, hours later, I've noticed has a big typo
      $racks[1].push($key => $value);

use

$racks[$rooms['room_id']].push($key => $value);

I haven’t used push like this, so maybe just something like

$racks[$rooms['room_id']][$key] = $value;

might be something I’d try.

1 Like

I think you mean room can be 1-6 rather than rack?

Or are there 6 racks spread over x amount of rooms?

Yes, Heres how my racks table is set up

create table racks (
   rack_id INT NOT NULL AUTO_INCREMENT,
   room_id TINYINT,
   title VARCHAR(25),
   manufacturer VARCHAR(50) DEFAULT 'Urtnowski Manufaturing Co.',
   model VARCHAR(50) DEFAULT 'Urtnowski',
   width TINYINT DEFAULT 20,
   height TINYINT DEFAULT 100,
   depth TINYINT DEFAULT 30,
   slots TINYINT DEFAULT 45,
   row VARCHAR(15),
   bay VARCHAR(10),
   front_clearance VARCHAR(15) DEFAULT 'What is this?',
   rear_clearance VARCHAR(15) DEFAULT 'What is this?',
   notes TEXT NULL,
   created_by VARCHAR(50) DEFAULT 'Admin',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   enabled BOOLEAN DEFAULT 1,
   PRIMARY KEY ( rack_id )
);

The query I think is good is

$sql = 'SELECT rack_id,room_id,title FROM racks ORDER BY room_id ';
$result = mysqli_query($conn, $sql);

so then $result would hold all the racks (ordered so that all the 1s are together all the way to 6) and ill only have 1-6 rooms.
Right now I just have some dummy racks set up for each room and im trying to populate a (select rack) selection based on which room is choosen from another select box which allows 1-6.

$sql = 'SELECT rack_id,room_id,title FROM racks';
$result = mysqli_query($conn, $sql);

$racksPerRoom = [];
while ($row = mysqli_fetch_assoc($result)) {
    $racksPerRoom[$row['room_id']] = [
        'rack_id' => $row['rack_id'],
        'title' => $row['title']
    ];
}

foreach ($racksPerRoom as $roomId => $racksInRoom) {
    printf('<select class="form-control" id="Room%d" name="Room%d" required>', $roomId, $roomId);
    echo '<option value="0">Select</option>';
    foreach ($racksInRoom as $rack) {
        printf('<option value="%s">%s</option>', $row['rack_id'], htmlentities($row['title']));
    }
    echo '</select>';
}

ok, so then $result holds an array of all the racks,
the waw create an array, $racksPerRoom, and rill it up using the while loop so
that $racksPerRoom[1] holds all the racks [rack_id, title] of all racks in room 1. and so on.

I think I understand what the foreach loops are doing, but what is the % doing in the id and name of the select element?

That is just how printf works