For Each Loop on Common Value in DB

[Adamhumbug]

Hi All,

I have a table, the column type is an attribute that items in this table share. A simple explanation would be if the field was colour and the items could be one of the colours.

I am struggling with a for each loop to get the items out of this table grouped by this value.

I am wanting to create a dopdown where the item_type is a header.

This is what i have so far which obviously doesnt work:

unction getRentalItems($display){
	include 'includes/dbconn.php';
	$stmt = $conn -> prepare('
		SELECT i.id, item, it.type,  price_GBP, price_USD, price_CAD 
		FROM items i
		LEFT JOIN item_type it on i.type = it.id
		');
	$stmt -> execute();
	$stmt -> bind_result($id, $item, $type, $gbp, $usd, $cad);

	if($display == 'Options'){
		$out = '';
		while($stmt -> fetch()){
			$out .= '<optgroup label="$type">';
			$out .= "<option data-item='$type' data-GBP='$gbp' data-USD='$usd' data-CAD='$cad' value='$id'>$item</option>";
		}
	}
	return $out;
}

I would like the optgroup to be first whenever the type changes.

I hope this explanation makes sense - there is an image to show what i mean - the bold text should only appear once per instance.

Edited October 10, 2020 by Adamhumbug

[requinix]

First you'll have to sort the results by the type. Because otherwise stuff will bounce between one type and another. Then you should probably sort by a secondary another field - I suggest the item name, since that's what the user sees.
Really, most of the time you're running a query for data that gets displayed to a user, you'll want to sort by something. Even if you don't need anything grouped or sorted alphabetically, picking and sorting by a unique column (like an ID) guarantees that you'll get the same data in the same order every time.

Anyway, when you have that sorting, your loop will need to know when the type changes. That means remembering what the previous one was.

Get yourself a variable for it. Start it off empty. Inside the loop, if the current type doesn't match the previous type (and the first row definitely will not) then that means you need to start a new group. If there was a previous type then there was a previous <optgroup> already going for it and you'll need to close that out, then either way you start the new <optgroup>.
Finally, you need to think about that last group. When the loop finishes you'll have been working on one group but the <optgroup> will still be open. So close that one out too.

[Adamhumbug]

Thanks for that i feel like i am getting close.

i have this now

function getRentalItems($display){
	include 'includes/dbconn.php';
	$stmt = $conn -> prepare('
		SELECT i.id, item, it.type,  price_GBP, price_USD, price_CAD 
		FROM items i
		LEFT JOIN item_type it on i.type = it.id
		order by i.type desc
		');
	$stmt -> execute();
	$stmt -> bind_result($id, $item, $type, $gbp, $usd, $cad);

	if($display == 'Options'){
		$out = '';
		$t = '';
		while($stmt -> fetch()){
			
			if ($t == $type){
				$out .= "<option data-item='$type' data-GBP='$gbp' data-USD='$usd' data-CAD='$cad' value='$id'>$item</option>";
			}else{
				$out .= "</optgroup><optgroup label='$type'>";
			}
			$out .= '</optgroup>';
			$t = $type;
			
		}
	}
	return $out;
}

i am getting 2 headers and 2 results but i feel the logic is correct?

[Adamhumbug]

ok much closer now.

Still struggling a bit as you can see

function getRentalItems($display){
	include 'includes/dbconn.php';
	$stmt = $conn -> prepare('
		SELECT i.id, item, it.type,  price_GBP, price_USD, price_CAD 
		FROM items i
		LEFT JOIN item_type it on i.type = it.id
		order by i.type desc
		');
	$stmt -> execute();
	$stmt -> bind_result($id, $item, $type, $gbp, $usd, $cad);

	if($display == 'Options'){
		$out = '';
		$t = '';
		while($stmt -> fetch()){
			
			if ($t == $type){
				$out .= "<option data-item='$type' data-GBP='$gbp' data-USD='$usd' data-CAD='$cad' value='$id'>$item</option>";
			}else{
				$out .= "<optgroup label='$type'>";
				$out .= "<option data-item='$type' data-GBP='$gbp' data-USD='$usd' data-CAD='$cad' value='$id'>$item</option>";
			}
			$out .= '</optgroup>';
			$t = $type;
			
		}
	}
	return $out;
}

 

[Adamhumbug]

Final Solution

function getRentalItems($display){
	include 'includes/dbconn.php';
	$stmt = $conn -> prepare('
		SELECT i.id, item, it.type,  price_GBP, price_USD, price_CAD 
		FROM items i
		LEFT JOIN item_type it on i.type = it.id
		order by i.type desc
		');
	$stmt -> execute();
	$stmt -> bind_result($id, $item, $type, $gbp, $usd, $cad);

	if($display == 'Options'){
		$out = '';
		$t = '';
		while($stmt -> fetch()){
			
			if ($t == $type){
				$out .= "<option data-item='$type' data-GBP='$gbp' data-USD='$usd' data-CAD='$cad' value='$id'>$item</option>";
			}else{
				$out .= "</optgroup><optgroup label='$type'>";
				$out .= "<option data-item='$type' data-GBP='$gbp' data-USD='$usd' data-CAD='$cad' value='$id'>$item</option>";
			}
			
			$t = $type;
			
		}
	}
	return $out;
}

 

[requinix]

It might seem right but you're outputting invalid HTML.

<select>
	</optgroup>
	<optgroup label='Support & Extras'>
		<option>On Site Support</option>
	</optgroup>
	<optgroup label='Licence'>
		<option>AllowME Multi Event Licence (Annual)</option>
		<option>AllowME Multi Event Licence (Quarterly)</option>
		<option>AllowME Multi Event Licence (2 Months)</option>
		<option>AllowME Multi Event Licence (Extension)</option>
	</optgroup>
	<optgroup label='Scanning Hardware'>
		<option>Long Range RFID Scanner</option>
		<option>Handheld Scanner</option>
		<option>Short Range Catering Scanner</option>
</select>

Does that look correct?

[mac_gyver]

if you index/pivot the data, using the type value as the main array index, when you retrieve the data into a php multi-dimensional array, it is clearer where the start and end of each section is when you then use two nested foreach(){} loops to produce the output.

[Adamhumbug]

On 10/11/2020 at 1:15 AM, requinix said:

It might seem right but you're outputting invalid HTML.

<select>
	</optgroup>
	<optgroup label='Support & Extras'>
		<option>On Site Support</option>
	</optgroup>
	<optgroup label='Licence'>
		<option>AllowME Multi Event Licence (Annual)</option>
		<option>AllowME Multi Event Licence (Quarterly)</option>
		<option>AllowME Multi Event Licence (2 Months)</option>
		<option>AllowME Multi Event Licence (Extension)</option>
	</optgroup>
	<optgroup label='Scanning Hardware'>
		<option>Long Range RFID Scanner</option>
		<option>Handheld Scanner</option>
		<option>Short Range Catering Scanner</option>
</select>

Does that look correct?

It does not correct and actually i am having the same issue again with a similar problem.

[mac_gyver]

if you do this -

On 10/10/2020 at 7:04 PM, mac_gyver said:

if you index/pivot the data, using the type value as the main array index, when you retrieve the data into a php multi-dimensional array, it is clearer where the start and end of each section is when you then use two nested foreach(){} loops to produce the output.

you will end up with code that looks like this -

$out = '';
foreach($indexed_data as $type=>$arr)
{
	// start a group
	$out .= "<optgroup label='$type'>";
	foreach($arr as $row)
	{
		// add the output for each $row of data within the group
		$out .= "your makdup here for the <option ...>...</option> tag";
	}
	// finish the group
	$out .= "</optgroup>";
}

 

[Barand]

You were close with your original method but it needed a couple of tweaks

while fetch {
    if $type != $t {
        if $t != ''  output </optgroup>             // only close previous group if there was one
        output <optgroup>
        $t = $type
    end if
    output <option>
end while
output </optgroup>                                  // close final group