CodeIgniter - Selecting from database by another query's result

Question

Forgive me if it is asked before, but i couldn't find the relevant keyword to search. I will try to explain my problem.

I have DB tables named "ilan" and "places". In "ilan" table, there are 3 columns which holds IDs of places - city, district, street.

And I want to learn what to do to print ilan.name and related places.name in the view.

I have a controller like:

class Estate_control extends CI_Controller{

public function _construct(){
    parent::_construct();   
}

function index(){

    $this->load->helper('url');
    $this->load->model("estate_model");

    $data['tum_bayiler'] = $this->estate_model->tum_bayileri_listele();
    $data['sonEmlaklar'] = $this->estate_model->sonEmlaklar();      

    $this->load->view("index",$data);

}
}

I have model like:

class Estate_model extends CI_Model{

function __construct(){
        parent::__construct();
}

function sonEmlaklar(){
    $query = $this->db
            ->select()
    ->from("ilan")
    ->order_by("gunceltarih DESC")
    ->limit(36)
    ->get();

    return $query->result();            
}

}

My DB tables:

ilan:

ilan_id, gunceltarih, ... , ilan_city(an ID in places table), ilan_district(an ID in places table), ilan_street(an ID in places table), ...

places:

places_id, places_name, ...

Any ideas on what to do to print ilan.name and related places.name in the view.

Thanks everyone.

@Mike i think "join" won't work for me. I have 3 columns related with "places". So I have to make 3 queries for each "ilan" row? — Harun Yilmaz
– Harun Yilmaz, Commented May 23, 2012 at 18:00

Mateo Torres · Accepted Answer · 2012-05-24 23:26:42Z

1

I think this is the relevant part of the code

$query = $this->db
        ->select("ilan.*, places.name")
->from("ilan")
->join('places', 'places.id = ilan.ilan_city OR places.id = ilan.ilan_district OR places.id =ilan.ilan_street')
->order_by("gunceltarih DESC")
->limit(36)
->get();

edited May 24, 2012 at 23:26

answered May 23, 2012 at 17:44

Mateo Torres

1,6251 gold badge14 silver badges24 bronze badges

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

2 Comments

Harun Yilmaz Over a year ago

"Join" does not fit my situation because I have 3 columns relates with "places". I think I have to make a new query for each "ilan" row. But I couldn't find the way

Mateo Torres Over a year ago

I know you have 3 columns, that's why you use the OR conditions (or AND if you need the 3 of them to be equal), please post an example of the tables and the result you want, if you need a very specific kind of query you will need to write the SQL yourself.

Rani Yadav · Accepted Answer · 2025-05-03 11:41:52Z

in you model, modify the sonEmlaklar() function like this :

$this->db->select('ilan.*, city.places_name as city_name, district.places_name as district_name, street.places_name as street_name');
$this->db->from('ilan');
$this->db->join('places as city', 'ilan.ilan_city = city.places_id', 'left');
$this->db->join('places as district', 'ilan.ilan_district = district.places_id', 'left');
$this->db->join('places as street', 'ilan.ilan_street = street.places_id', 'left');
$this->db->order_by('gunceltarih DESC');
$this->db->limit(36);
$query = $this->db->get();

in the view, loop through sonEmlaklar:

<?php foreach ($sonEmlaklar as $ilan): ?>
    <div>
        <h3><?= $ilan->name; ?></h3>
        <p>City: <?= $ilan->city_name; ?></p>
        <p>District: <?= $ilan->district_name; ?></p>
        <p>Street: <?= $ilan->street_name; ?></p>
    </div>
<?php endforeach; ?>

this will fetch and display the ilan.name along with then related place names

Harun Yilmaz · Accepted Answer · 2012-06-01 07:08:02Z

I found a way out. I don't know how efficient it is but it works for me.

I created a function inside index function in my controller as follows:

function index(){

    //whatever here

function getPlace($e){
    $ci = get_instance(); //to get properties of parent class
    $query = $ci->estate_model->ilanSehir($e);


    return $query;
}   

    //whatever here

}

Then I put a function inside my model as follows:

function ilanSehir($e){ 
$query = $this->db->select("*")
    ->from("places")
    ->where("places_id",$e)
    ->get();
 return $query->row();    //This will return one row so it must be row() instead of result()

}

And in my view I called the function with the parameter I want:

<?php $place = getPlace($ilan->ilan_city);
      echo $place->places_name;
?>

As I said before, I don't know how efficient it is but it worked for me. Hope it helps. If I'm wrong somewhere, please warn me.

Thank you all who answered

Collectives™ on Stack Overflow

CodeIgniter - Selecting from database by another query's result

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related