[eluser]zauber[/eluser]
[quote author="codelearn" date="1198122203"]
Do you see what I mean and/or did you answer that exact question and I'm just slow?
[/quote]
No, you're not slow, I was in fact answering the exact opposite question. From what I
now understand you want to fetch all the coupons for a given school. I was telling you how to get the school for a given coupon.
[quote author="codelearn" date="1198122203"]
The "coupon table" only has the rest_id field (the ID field in the "restaurant" table)
[/quote]
Really? Only one column containing rest_id, and nothing else (name, coupon number, whatever)? If a coupon is nothing more than a restaurant number, then how do you tell coupons apart? Or are you saying that the coupon table is simply a list of restaurants that "have coupons"? If this is the case, I would strongly advise that you change your database layout to instead have just a true/false column in the restaurant table called "has_coupon".
[quote author="codelearn" date="1198122203"]
Code:
$this->db->where('school_id',$this->uri->segment(2));
$this->db->get('rest_coupons');
[/quote]
Sorry, but I myself am quite a noob to codeigniter, so I'm not familiar with the database interface. I am however reasonably proficient with SQL, so I just tend to use the following syntax:
Code:
$q = $this->db->query("SOME QUERY HERE WITH param1= ?, param2=?", array($myParam1,$myParam2));
$result = $q->result();
I'll do it that way, and then perhaps someone can fill you in on how to do the same thing with the "implicit syntax" (just made the term up, hope you get it)
Nevertheless, it's apparent you're trying to fetch a list of coupons for a given school, so I will assume that you have some way of telling coupons apart - like a coupon code. So I will assume your coupons-table has (at least) two columns: "coupon_code" - the identifier of a coupon, and "rest_id" containing the identifier for the restaurant with which a coupon is associated.
In that case, this is what you want:
Code:
$q = $this->db->query("
SELECT coupon.coupon_code
FROM coupon INNER JOIN restaurant ON restaurant.id = coupon.rest_id
WHERE restaurant.school_id=?",
array(
$this->uri->segment(2),
)
);
$result = $q->result();
If instead, what you mean was, "tell me the restaurants for school X
that have coupons", then this should do it:
Code:
$q = $this->db->query("
SELECT id
FROM restaurant
WHERE
id IN (SELECT rest_id FROM coupon) AND
restaurant.school_id=?",
array(
$this->uri->segment(2),
)
);
$result = $q->result();