Jump to content

For Each Loop on Common Value in DB


Go to solution Solved by requinix,

Recommended Posts

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.

Screenshot 2020-10-10 at 21.36.50.png

Edited by Adamhumbug
Link to post
Share on other sites
  • Solution

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.

Link to post
Share on other sites

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?

Screenshot 2020-10-10 at 23.56.37.png

Link to post
Share on other sites

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;
}

 

Screenshot 2020-10-11 at 00.05.57.png

Link to post
Share on other sites

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;
}

 

Link to post
Share on other sites

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?

Link to post
Share on other sites

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.