Is there a way to combine two sql queries, one that fetches a single record and one that fetches multiple and then send them from the controller to view

I want to edit a record and there is a dropdown of options pulling from the database. First, I want to select the user selected record as the first option in the dropdown, and then I want to show all the options available that they didn't select upon initial submission. In the model I currently have:

      
public function SelectedCategory($id)
	{
		$this->db->query("SELECT `location_name` FROM `location` as `l` INNER JOIN `bb_products` as `bbp` ON bbp.`prod_location_id` = l.`location_id` WHERE bbp.`prod_id` = :prod_id");
		$this->db->bind(":prod_id", $id);
		$row = $this->db->single();
		return $row;
	}
	
	public function NotSelectedCats($id)
	{
		$this->db->query("SELECT `location_id`, `location_name` FROM `location` as `l` INNER JOIN `bb_products` as `bbp` ON bbp.`prod_location_id` != l.`location_id` WHERE `prod_id` = :prod_id ORDER BY `location_name` ASC");
		$this->db->bind(":prod_id", $id);
		$results = $this->db->resultSet();
		return $results;
	}

Open in new window


Is it fine to do this or can I combine these into one method?

I thought of doing this initially:

public function GetProdById($id)
	{
		$this->db->query("SELECT `prod_id`, `prod_name`, `long_desc`, `vouch_limits`, `reg_price`, `sale_price`, `start_date`, `end_date` FROM `bb_products` WHERE `prod_id` = :prod_id");
		$this->db->bind(":prod_id", $id);
		$row = $this->db->single();
		$location_name = $row->location_name;
                $location_id = $row->location_id

               // second query goes here where I could just SELECT FROM location WHERE location_id != $location_id 
                 which was defined from the first query above. 
	}

Open in new window


But I don't know how to get those into the controller because if I try and return $location_name or $location_id instead of just returning $row, the application breaks.
LVL 1
Black SulfurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
What mvc framework, if any, are you using?
0
Black SulfurAuthor Commented:
Hi Rob, I am not using a framework.
0
RobOwner (Aidellio)Commented:
So you're implementing the mvc in your own structure? I'm only asking as it would help to know how your controller and models work with each other
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Black SulfurAuthor Commented:
Correct. Let's say I wanted to grab the method, 'NotSelectedCats' in the controller. I would do so like this:

$list_categories = $this->ProductModel-> NotSelectedCats();

$data = [

'categories' => $list_categories

];

// send to view

$this->view('admin/edit-prod', $data);

Open in new window


ProductModel is just the name I have given my product Model where  the db query lives.
0
RobOwner (Aidellio)Commented:
Is it fine to do this or can I combine these into one method?
I see no harm in keeping these as separate methods, given they can be used at different times for different purposes. You just return both datasets to the controller with two calls to the ProductModel and pass the datasets on to the view.

Let the view work with both datasets.
$list_categories = $this->ProductModel->NotSelectedCats($id);
$product_info = $this->ProductModel->SelectedCategory($id);

$data = [

'categories' => $list_categories,
'product' => $product_info

];

// send to view

$this->view('admin/edit-prod', $data);

Open in new window


Am I on the same page here? Sorry if I've missed something
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
yeah, that is exactly whatI have done, as you posted in the controller code above. Great, I am glad that it is fine to do that. Thanks!
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.