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?
 
RobConnect With a Mentor Owner (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
 
RobOwner (Aidellio)Commented:
What mvc framework, if any, are you using?
0
 
Black SulfurAuthor Commented:
Hi Rob, I am not using a framework.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.