Break the results of a query into groups

PHP
1

I have this

if (mysqli_num_rows($bay_result) > 0) {
				
	while($bays = mysqli_fetch_assoc($bay_result)) {

 		  echo '<rect x="'.$bays['x_coord'].'" y="'.$bays['y_coord'].'" width="'.$bays['width'].'" height="'.$bays['height'].'" class="bay" />';
		  echo '<a xlink:href="../racks/add_rack.php?RoomID=2&Row=1&Bay='.$bays['title'].'">';
		  echo '<text x="'.($bays['x_coord'] + 15).'" y="'.($bays['y_coord'] + 47).'" class="bay_text">'.$bays['title'].'</text>';
		  echo '</a>';
	  } 
	
}

which results in

image
image1920×1080 237 KB

So the query works perfect and returns the bays (the boxes with blue leters in groups of 10) in 5 ddifferent rows.
What id like to dois make the query whange the Row variariable in the link (?Row=1) change depending on which row (1-5) its in.
I think I have to split the results of the query into groups of 10.
How can I do that?
I was thinking Can I put the result into an array, then put that into a for loop like,

$bays_array = array();

while($bays = mysqli_fetch_array($bay_result)) {
  //populate the aray
}

foreach($bays_array as $bays) {
  for ($x = 0; $x <= 10; $x++) {
 		  echo '<rect x="'.$bays['x_coord'].'" y="'.$bays['y_coord'].'" width="'.$bays['width'].'" height="'.$bays['height'].'" class="bay" />';
		  echo '<a xlink:href="../racks/add_rack.php?RoomID=2&Row=1&Bay='.$bays['title'].'">';
		  echo '<text x="'.($bays['x_coord'] + 15).'" y="'.($bays['y_coord'] + 47).'" class="bay_text">'.$bays['title'].'</text>';
		  echo '</a>';
  }
  for ($x = 0; $x <= 10; $x++) {
 		  echo '<rect x="'.$bays['x_coord'].'" y="'.$bays['y_coord'].'" width="'.$bays['width'].'" height="'.$bays['height'].'" class="bay" />';
		  echo '<a xlink:href="../racks/add_rack.php?RoomID=2&Row=2&Bay='.$bays['title'].'">';
		  echo '<text x="'.($bays['x_coord'] + 15).'" y="'.($bays['y_coord'] + 47).'" class="bay_text">'.$bays['title'].'</text>';
		  echo '</a>';
  } 
  for ($x = 0; $x <= 10; $x++) {
 		  echo '<rect x="'.$bays['x_coord'].'" y="'.$bays['y_coord'].'" width="'.$bays['width'].'" height="'.$bays['height'].'" class="bay" />';
		  echo '<a xlink:href="../racks/add_rack.php?RoomID=2&Row=3&Bay='.$bays['title'].'">';
		  echo '<text x="'.($bays['x_coord'] + 15).'" y="'.($bays['y_coord'] + 47).'" class="bay_text">'.$bays['title'].'</text>';
		  echo '</a>';
  } 
  for ($x = 0; $x <= 10; $x++) {
 		  echo '<rect x="'.$bays['x_coord'].'" y="'.$bays['y_coord'].'" width="'.$bays['width'].'" height="'.$bays['height'].'" class="bay" />';
		  echo '<a xlink:href="../racks/add_rack.php?RoomID=2&Row=4&Bay='.$bays['title'].'">';
		  echo '<text x="'.($bays['x_coord'] + 15).'" y="'.($bays['y_coord'] + 47).'" class="bay_text">'.$bays['title'].'</text>';
		  echo '</a>';
  } 
  for ($x = 0; $x <= 10; $x++) {
 		  echo '<rect x="'.$bays['x_coord'].'" y="'.$bays['y_coord'].'" width="'.$bays['width'].'" height="'.$bays['height'].'" class="bay" />';
		  echo '<a xlink:href="../racks/add_rack.php?RoomID=2&Row=5&Bay='.$bays['title'].'">';
		  echo '<text x="'.($bays['x_coord'] + 15).'" y="'.($bays['y_coord'] + 47).'" class="bay_text">'.$bays['title'].'</text>';
		  echo '</a>';
  } 

}

Is this somewhat right?

2

Your programmer instinct should be screaming at you that if you’re copying and pasting code, you’re missing a trick - or more specifically, a loop.

There are many ways to go about this, but PHP does actually have a function defined to do exactly this - it’s called array_chunk.

1 Like
3

ok, so like

	$bays_array = array();
		
	while($bays = mysql_fetch_array($bay_result)){
						
		$bay = array(
		$bays['x_coord'],
		$bays['y_coord']),
		$bays['width'],
		$bays['height']),
		$bays['title']);
		array_push($bays_array,$bay);
	}

array_chunk($bays_array,10);

but then do I need 2 foreach loop to run through both arrays?

4

So, lets cover the standard complaint i’m sure you’ll hear:
“Don’t use mysqli, use PDO”

Next, lets simplify your first loop - and throw in the chunk while we’re at it.
$bays_array = array_chunk(mysqli_fetch_all($bay_result,MYSQLI_ASSOC),10);

Now your array is an array of arrays such that the elements 0…n-1 have 10 elements each, and n has the remainder.

foreach the outer array;
foreach($bays_array as $rowindex => $row) {

do any row-start code here;

then foreach the row elements:
foreach($row as $colindex => $bay) {

then do your bay-specific code.

End your inner foreach

then do your row ending code.

end your outer foreach

and you’re done.

1 Like
5

Maybe I am not understanding the goal here. Here how I arrived at an alternate solution, that doesn’t require a foreach, or even any extra functions ( although array_chunk is a cool function to keep in your back pocket, this method uses less overhead) :

What do we know:
• The while loop is iterating though then entirety of your results.
• We want to group these results into sub groups of ‘x units’ ( in this case 10) units

if (mysqli_num_rows($bay_result) > 0) {
		
		$units_per  = 10;  //this variable could be be set by user
		$counter 	= 0;
		$container_flag  =  false;		
	while($bays = mysqli_fetch_assoc($bay_result)) {
          if  ( $container_flag && $counter%$units_per){
 	          // close the section here
	          echo "</div>\n";
          }
          if  ($counter%$units_per){
 	          // open the section here
	          echo "<div class=\"container\">\n";
 	          $container_flag  =  true;		
          }
 		  echo '<rect x="'.$bays['x_coord'].'" y="'.$bays['y_coord'].'" width="'.$bays['width'].'" height="'.$bays['height'].'" class="bay" />',
		   	   '<a xlink:href="../racks/add_rack.php?RoomID=2&Row=1&Bay='.$bays['title'].'">',
		       '<text x="'.($bays['x_coord'] + 15).'" y="'.($bays['y_coord'] + 47).'" class="bay_text">'.$bays['title'].'</text>',
			   '</a>';
	  } 
      if  ( $container_flag && !$counter%$units_per){
 	          // close the section here
	          echo "</div>\n";
      }
}

And at some point you should replace (mysqli_num_rows($bay_result) > 0) with PDO.

Hope this helps

1 Like
6

While… I agree you’ve managed to avoid a foreach, you’re still looping, and i’ve gotta say the readability goes way, way down.

You also need to adjust your echo to account for the Row=1 in there, so you’ll have to have your loop do a bit more math on every iteration to find the current row, or add yet another counter variable…

1 Like
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.