codeigniter use part of query result in another query

Question

It was hard to come up with a title. I am using CodeIgniter with models/views/controller. I have the following tables in my MySQL database that are relevant:

enter image description here

In my model I have the following function:

function get_shoptable() {
    $this->db->from('productshop')->where('productId', $this->productId);
    $query = $this->db->get();
    return $query->result();
}

In my controller I use the above function like

$data['bookshop'] = $this->Product_model->get_shoptable();

In my view I am foreaching $bookshop. My problem is, what is the best wayto show shopName, instead of showing shopId. Taking in regards that $bookshop should be as it is (except of shopid), because I am creating a HTML table with product data.

just don't use the shopId from the result set.either use select statement to choose specific columns — Rajeev Ranjan
– Rajeev Ranjan, Commented Jul 30, 2013 at 12:41
Thanks for your comment. Your answer does not really help me, maybe I should specificy my question. Can I make a query which gets all the data from productshop (with the restriction of the productId) and gets the needed shopName from shop? — OTN
– OTN, Commented Jul 30, 2013 at 12:44
@Orhan, Check my answer, If you want I can extend it more all the example. — Erman Belegu
– Erman Belegu, Commented Jul 30, 2013 at 12:55

Nil'z · Accepted Answer · 2013-07-30 12:44:27Z

3

Try some like this:

function get_shoptable() {
    $this->db->from('productshop')
    ->join('shop', 'productshop.shopId = shop.shopId')
    ->where('productshop.productId', $this->productId);
    $query = $this->db->get();
    return $query->result();
}

answered Jul 30, 2013 at 12:44

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OTN Over a year ago

Very cool, thanks, that worked! But how can that work if you do not select shopName? In the profiler I see the query build like: SELECT * FROM (productshop) JOIN shop ON productshop.shopId = shop.shopId WHERE productshop.productId = 116774

Erman Belegu · Accepted Answer · 2013-07-30 13:01:34Z

1

Model:

function get_products() {
    $this->db->select('productshop.productUrl, productshop.price, productshop.deliveryTime, productshop.shippingCast, productshop.inventory, productshop.productId, productshop.shopId, shop.shopName');
    $this->db->from('productshop');
    $this->db->join('shop', 'productshop.shopId = shop.shopId');
    $this->db->where('productshop.productId', $this->productId);
    return $this->db->get()->result_array();
}

Controller:

function products() {
    $data['products'] = $this->model_name->get_product();
    $this->load->view('products', $data);

}

VIEW:

<?php foreach($products as $p): ?>
<h1><?php echo $p['productUrl']; ?></h1>
<h1><?php echo $p['shopName']; ?></h1>
<?php endforeach(); ?>

edited Jul 30, 2013 at 13:01

answered Jul 30, 2013 at 12:54

Erman Belegu

4,08928 silver badges39 bronze badges

Comments

Rajeev Ranjan · Accepted Answer · 2013-07-30 12:50:13Z

0

get an overlook to active class of codeigniter for details of functions

function get_shoptable()
 {
    $this->db->from('productshop')
    $this->db->join('shop', 'productshop.shopId = shop.shopId')
    $this->db->where('productshop.productId', $this->productId);
    $query = $this->db->get();
    return $query->result();
}

answered Jul 30, 2013 at 12:50

Rajeev Ranjan

4,1523 gold badges30 silver badges42 bronze badges

Collectives™ on Stack Overflow

codeigniter use part of query result in another query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related