Reset a query

[lurtnowski]

I am trying to print the results the same query in two places on my page,
SELECT beginning_slot, ending_slot, device, orientation FROM devices WHERE rack_id = 2 AND enabled = 1
the result is

which is right on…
But I am trying to display the results where orientation = 0 on one part, and the results where orientation = 1 on another,

	<?php

    while($row1 = mysqli_fetch_assoc($result)) {

		if($row1['orientation'] == 1) {
	
		...
		}
	}
	?>

which works as expected, but when I try to show the 2 results where the orientation=0 like

	<?php

    while($row1 = mysqli_fetch_assoc($result)) {

		if($row1['orientation'] == 0) {
	
		...
		}
	}
	?>

nothing shows up
do I need to reset
$row1 or something?

[rpkamp]

You’ve got two options here:

1. Do the query, loop over the results and create buckets, based on the value of ‘orientation’ and then loop those buckets.

$results = [];
while ($row = mysqli_fetch_assoc($result)) {
    $results[$row['orientation']][] = $row;
}

foreach ($results as $orientation => $rows) {
    foreach ($rows as $row) {
        // do something with all rows with orientation = $orientation
    }
}

2. Split it up into two queries:

• SELECT beginning_slot, ending_slot, device, orientation FROM devices WHERE rack_id = 2 AND enabled = 1 AND orientation = 0
• SELECT beginning_slot, ending_slot, device, orientation FROM devices WHERE rack_id = 2 AND enabled = 1 AND orientation = 1

Options 2 is a lot easier, option 1 is slightly more performant (depdending on how many hits you’d get)

[benanamen]

@rpkamp

Do the query, loop over the results and create buckets, based on the value of ‘orientation’ and then loop those buckets.

This would be the way to do it. You don’t want to bang the DB more than necessary. Granted, in a small, low traffic site it isn’t going to matter, but still…

* I assume “buckets” means arrays as you have shown.

[rpkamp]

@benanamen

• I assume “buckets” means arrays as you have shown.

The process of stuffing similar things together is called bucketing (as I think you already know). Hence you put stuff in buckets. That those are implemented by arrays is a detail.

[droopsnoot]

Wouldn’t it be as easy to ORDER BY the row orientation, and notice when the orientation value changes and handle any display things such as changing to a new display table?

@lurtnowski

do I need to reset

You could use data_seek() to start again at the top of the results set, too. I know next to nothing about mysqli, but the doc tells you: https://www.php.net/manual/en/mysqli-result.data-seek.php

[m_hutley]

fetchAll, array_filter, foreach.

(also, Remon, you missed another set of square brackets in post #2.)

[rpkamp]

@m_hutley

(also, Remon, you missed another set of square brackets in post #2.)

Nice catch, I’ve added them

[rpkamp]

@m_hutley

fetchAll, array_filter, foreach.

Someone has been doing a lot of functional programming?