mySQL Join

[eluser]R. Oerlemans[/eluser]
Hi Everyone,

This moment I gonna work the first time with mySQL join (I think), but i dont know where to start in codeIgniter.

I have 2 tables' :


abonnementen
=============================
id | provider_id | speed | setup

providers
=============================
id | name | url

Now I want get both in one query

MYSQL Query normal

Code:

SELECT * FROM `abonnementen` ORDER by `speed`

But now I want to get the provider name (from the providers table) in the same query. Hopefully someone could help me.

Sorry for my bad English.

Thanks a lot.

[eluser]David Johansson[/eluser]
the normal mysql query could be:

Code:

SELECT * FROM `abonnementen` `t1`,`providers` `t2` WHERE `t1`.`provider_id` = `t2`.`id` ORDER BY `speed`

will soon submit an alternative using active record class

[eluser]David Johansson[/eluser]

Code:

$query = $this->db->from('abonnementen')->join('providers', 'providers.id = abonnementen.provider_id')->get();

[eluser]R. Oerlemans[/eluser]
Thanks thanks.

Now i'v this:

Code:

$query = $this->db->from('abonnementen')->join('providers', 'providers.id = abonnementen.p_id')->get();
            $array_data = $query->result_array();
            
            /* Page */
                $settingsList = Array(
                    'data' => $array_data
                );

with this in my view

Code:

<?php
    
        foreach($data as $item) ::
        
    ?>

But i get this error, somewhere i've make a mistake but :o

Parse error: syntax error, unexpected T_PAAMAYIM_NEKUDOTAYIM in ****** on line 14

[eluser]David Johansson[/eluser]
what does it say on line 14?

[eluser]David Johansson[/eluser]
i think the problem is that you are using two colons in your view.

[eluser]R. Oerlemans[/eluser]
Thanks. :-)


I'm not very good with active records. But what's wrong with this?

Code:

$array_data = $this->db    ->get_where('abonnementen', array('id' => $id))
                                            ->join('providers', 'providers.id = abonnementen.p_id')
                                                ->result_array();

Fatal error: Call to undefined method CI_DB_mysql_result::join() in *** on line 99

[eluser]David Johansson[/eluser]
you can't call the join function after you call the get_where function since the get_where() executes the query to the database and returns the result. the working code should be:

Code:

$array_data = $this->db->join('providers', 'providers.id = abonnementen.p_id')->get_where('abonnementen', array('id' => $id))->result_array();

[eluser]R. Oerlemans[/eluser]
You're a hero! :-)