Link to home
Start Free TrialLog in
Avatar of Crazy Horse
Crazy HorseFlag for South Africa

asked on

How to only show database items not chosen from initial captured multi select

This is for an update function whereby the user can update certain aspects of their profile:

I have a database query that checks which categories have been selected when user initially chose them (can be multiple) and it displays them with ticks next to them. I also want to display the rest of the categories that they didn't select. At the moment, it only does this for one record i.e.: it doesn't display one of the already selected but duplicates the rest if there is more than one.

I think I need to put a foreach or some kind of loop in this code but I can't figure it out. Please could someone assist.

function display_selected_cats($link) {
	
	$stmt = $link->prepare("SELECT sp_cats.sp_cat_id, voucher_categories.category_name FROM sp_cats INNER JOIN voucher_categories ON sp_cats.sp_cat_id = voucher_categories.cat_id AND sp_cats.sp_id = ?");
	$stmt->bind_param("i", $_GET['id']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows > 0) {
		while($row = $result->fetch_assoc()){
			
			$cat_name = sanitize($row['category_name']);
			$cat_id = sanitize($row['sp_cat_id']);
			echo "<option value='$cat_id'selected>$cat_name</option>";
		}
	}
	
	$stmt->close();
	
	
	$stmt2 = $link->prepare("SELECT `cat_id`, `category_name` FROM `voucher_categories` WHERE `cat_id` != ?");
	$stmt2->bind_param("i", $cat_id);
	$stmt2->execute();
	$result = $stmt2->get_result();
	$numRows = $result->num_rows;
	if($numRows > 0){
		while($row = $result->fetch_assoc()){
			
			$cat_id2 = sanitize($row['cat_id']);
			$category_name = sanitize($row['category_name']);
			echo "<option value='$cat_id2'>$category_name</option>";
		}
	}
	
	$stmt2->close();
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Crazy Horse

ASKER

I don't know if that will work because I have:

echo "<option value='$cat_id'selected>$cat_name</option>";

Open in new window


I think I have to separate the queries so one can look like this with "selected"

echo "<option value='$cat_id'selected>$cat_name</option>";

Open in new window


and the other one without:

echo "<option value='$cat_id'>$cat_name</option>";

Open in new window


as I obviously don't want ALL the options to show as selected.
Not sure what you want the display to look like, but here's how I might cobble something together it if I'm understanding your description.

Client makes GET request to form page and sees the select multiple form, makes some selections, hits submit
Client submit action makes POST request to server
Server SELECTs all profile elements from database table
Using an iterator over the database SELECTions, server marks the profile selections with a flag that they were selected by the client
Using an iterator over the database SELECTions, server displays each profile selection, with or without a flag

That way the client will see everything, and also see the current selections.

If you're looking for the design of profile updates, this has kind of "been done" already, so there is no reason to reinvent the wheel.  Since the client's profile information can be isolated in a single results set, the design pattern you want is essentially the same as SQL table maintenance.  Basically, you put everything on the screen in a form and let the client make any changes, then when the form is submitted, you replace all of the fields in the database.  This article shows the general design we use for such things.
https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html

A "slicker" design might put the profile element names on the client screen and when clicked, open a modal window for the updates.  This would allow for element-by-element update processes.  Somethings are fairly simple - if the client wanted to change an address or a credit card number, all you need to do is capture the new information, but if the client wanted to change a password, you would need a slightly more elaborate process to verify both the old password and the new password.
Since I suck at explaining, I have attached a picture to this to better explain my problem.

When a user first signs up for example, they select multiple categories of interest by simply clicking on each one in a multi-list dropdown. Then some time later they discover a new hobby and want to edit their list. They will see another multi-select list but their initial choices will already be ticked as you can see. In this case I have randomly ticked Fitness, gardening as well as Home & decor. The problem is that when I go back, I can see Fitness twice, as well as gardening. The only one not showing up again is Home & decor. So, I think my query is working only for one record but not looping through all the already selected ones.

Fitness and gardening should not be showing again as they are already shown ticked as they were previous selections.

User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try..

$stmt = $link->prepare("SELECT sp_cats.sp_cat_id, voucher_categories.category_name FROM sp_cats INNER JOIN voucher_categories ON sp_cats.sp_cat_id = voucher_categories.cat_id AND sp_cats.sp_id = ?");

	$stmt->bind_param("i", $_GET['id']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows1 = $result->num_rows;
	if($numRows1 > 0) {
		while($row = $result->fetch_assoc()){
			
			$cat_name = sanitize($row['category_name']);
			$cat_id = sanitize($row['sp_cat_id']);
			echo "<option value='$cat_id'selected>$cat_name</option>";
		}
	}
	
	$stmt->close();
	
	
	$stmt2 = $link->prepare("SELECT `cat_id`, `category_name` FROM `voucher_categories`");
	$stmt2->execute();
	$result = $stmt2->get_result();
	$numRows2 = $result->num_rows;
	if($numRows2 > 0){
		while($row = $result->fetch_assoc()){
			
			$listCatId = sanitize($row['cat_id']);
			$listCatName = sanitize($row['category_name']);
			if(!in_array($cat_id, $stmt)){
			echo "<option value='$listCatId'>$listCatName</option>";
		
			}
				
		}
	}
	
	$resultNew = array_diff($numRows1, $numRows2);
	
	$stmt2->close();

Open in new window

I tried to simplify this by not using joins and just working with ID's for now. I also tried to use  array_diff_assoc() which seemed like a good choice but it doesn't work.

$stmt = $link->prepare("SELECT `sp_cat_id` FROM `sp_cats` WHERE `sp_id` = ?");
	$stmt->bind_param("i", $_GET['id']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows > 0) {
		while($row = $result->fetch_assoc()){
			
			$spCats = sanitize($row['sp_cat_id']);
			echo "<option value='$spCats'selected>$spCats</option>";
			
		}
	}
	
	$stmt->close();
	
	$stmt2 = $link->prepare("SELECT `cat_id`, `category_name` FROM `voucher_categories`");
	$stmt2->execute();
	$result2 = $stmt2->get_result();
	$numRows2 = $result2->num_rows;
	if($numRows2 > 0){
		while($row2 = $result->fetch_assoc()){
			
			
			$cat_id = $row2['cat_id'];
			$arraydiff = array_diff_assoc($sp_cats, $cat_id);
			echo "<option value='$arraydiff'>$arraydiff</option>";
			
		}
	}
	
	$stmt2->close();

Open in new window

Thanks for your suggestion Pawan. That doesn't work though. It still lists all categories including the ones already showing as selected so it is still duplicating values when it should not.
I just made my life a whole lot easier by discovering I could do this:

SELECT `cat_id`, `category_name` FROM `voucher_categories` WHERE `cat_id` NOT IN (SELECT `sp_cat_id` FROM `sp_cats` WHERE `sp_id` = ?)

Open in new window



It is now working how I want it to :)
If you check this is what I posted in my first comment.

Great!!
@ Pawan,  Oh, I see you did point out NOT IN, but it didn't work how you had it so I looked for other methods and stumbled on it again today, but just used it differently. I completely forgot you did suggest it.