Using id to get another field from the table

[eluser]rijobo[/eluser]
I've got a table product where i for example use materiaal_id. This is the id from the table materiaal. I would like to use the field name of that table.
So when I use this in my view:

Code:

<?php
if ($this-> session-> flashdata('conf_msg')){
    echo $this-> session-> flashdata('conf_msg');
}

echo "<tr>";
echo "<td colspan='2'>";
echo $product['naam'];
echo "</td>";
echo "<tr>";
echo "<td>";
echo "<img src='".base_url()."/images/".$product['border='1' align='left'/>";
echo "</td>";
echo "<td>";
echo $product['langoms']."<br/>\n";
echo $product['materiaal_id']."<br/>\n";
echo $product['steen_id']."<br/>\n";
echo $product['afmeting']."<br/>\n";
echo "&euro;" . $product['prijs']."<br/>\n";
echo anchor('webwinkel/winkelwagen/'.$product['id'],'in winkelwagen')."</p>\n";
?&gt;

These are all fields of the table product. Know I would like to use materiaal_id and show in this view the field name of the table materiaal where id = materiaal_id.

[eluser]Andrew Cairns[/eluser]
Hi Rojobo,

You could add another field to the product array from within the controller which uses the materiaal_id to pull the information you need from the materiaal model.

Let us know how you get on.

[eluser]jedd[/eluser]
[quote author="rijobo" date="1260991800"]
I've got a table product where i for example use materiaal_id. This is the id from the table materiaal. I would like to use the field name of that table.
[/quote]

You need to do this in your model - when you retrieve the data from product. You are likely going to have to do a JOIN here, connecting product to materiaal.

I suggest you first go and read about JOINs in the MySQL documentation (assuming you're using MySQL here).

Then read about ->join functions in the [url="/user_guide/database/active_record.html"]AR section of the CI User Guide[/url]. You haven't shown your database query at all, so it's not clear if you're using AR or vanilla SQL. Both are straightforward, however.

Try doing your queries first at a database / SQL command line so that you understand what's happening, and then within PHP use the print_r() or var_dump() functions to see the nature of the data you get back from these calls.

[eluser]rijobo[/eluser]
I've got it. Thank you!

[eluser]Jeroen Brussich[/eluser]
The world would be such a better place if only people would post the fix to their problems...

[eluser]rijobo[/eluser]
My new query with joins:

Code:

function getProduct($id){
        $data = array();
        $options = array('product.id' => $id);
        $this-> db-> select('materiaal.materiaal, soort.soort, steen.steen, product.naam, product.foto, product.langoms, product.kortoms, product.materiaal_id, product.steen_id, product.afmeting, product.prijs, product.id');
        $this-> db-> from('product');
        $this-> db-> join('materiaal', 'product.materiaal_id = materiaal.id');
        $this-> db-> join('soort', 'product.soort_id = soort.id');
        $this-> db-> join('steen', 'product.steen_id = steen.id');
        $this-> db-> where($options,1);
        $Q = $this-> db-> get();
        if ($Q-> num_rows() > 0){
            $data = $Q-> row_array();
        }
        $Q-> free_result();
        return $data;
    }