query problem, quick SQL question

[eluser]theknight[/eluser]

Code:

$this->db->select("Contacts.Id, CAST(FirstName AS TEXT) as FirstName, CAST(Surname AS TEXT) as Surname, CAST(Gender AS TEXT) as Gender, CAST(MobilePhoneNumber AS TEXT) as MobilePhoneNumber, CAST(WorkPhoneNumber AS TEXT) as WorkPhoneNumber, CAST(Email AS TEXT) as Email, UserAccount_Id, Address_Id");
        //$this->db->from('Contacts');
        //joins the two tables
        $this->db->join('Contacts', 'Contacts.UserAccount_Id=UserAccounts.Id');
        //User Account, get username
        $usernameCheck = $this->db->get_where($this->table, "username='". $username ."'");
    $count= $usernameCheck->num_rows();

Now I want to get the result set, in particular the "email" address for that column for that particular username.

I know how to do this in php ms_sql library but I am having a harder time doing this using the database class in codeigniter.

How do I do this?

[eluser]WanWizard[/eluser]
See http://ellislab.com/codeigniter/user-gui...sults.html, there are several examples in the documentation.

[eluser]theknight[/eluser]
Thanks

I have amended my code like this:

Code:

$this->db->select("Contacts.Id, CAST(FirstName AS TEXT) as FirstName, CAST(Surname AS TEXT) as Surname, CAST(Gender AS TEXT) as Gender, CAST(MobilePhoneNumber AS TEXT) as MobilePhoneNumber, CAST(WorkPhoneNumber AS TEXT) as WorkPhoneNumber, CAST(Email AS TEXT) as Email, UserAccount_Id, Address_Id");
        //$this->db->from('Contacts');
        //joins the two tables
        $this->db->join('Contacts', 'Contacts.UserAccount_Id=UserAccounts.Id');
        //User Account, get username
        $usernameCheck = $this->db->get_where($this->table, "username='". $username ."'");
        
        foreach ($usernameCheck->result_array() as $row)
        {
        echo $row['email'];
        }

But it is not echoing the email associated with the email address out - why?

Additional info, the two tables are joined.

Thanks

[eluser]danmontgomery[/eluser]

Code:

$this->output->enable_profiler();

To see the query as it's being run.

[eluser]theknight[/eluser]
Thanks for that, here was what was outputted.

Code:

SELECT Contacts.Id, CAST(FirstName AS TEXT) as FirstName, CAST(Surname AS TEXT) as Surname, CAST(Gender AS TEXT) as Gender, CAST(MobilePhoneNumber AS TEXT) as MobilePhoneNumber, CAST(WorkPhoneNumber AS TEXT) as WorkPhoneNumber, CAST(Email AS TEXT) as Email, UserAccount_Id, Address_Id
FROM UserAccounts
JOIN Contacts ON Contacts.UserAccount_Id=UserAccounts.Id
WHERE username='dummy'

I don't understand why it is selecting it from "UserAccounts" table, those fields are in the "Contacts" table not UserAccounts.

Secondly how can I iterate through the fields to get the email address designated to that username?

Thanks.

[eluser]theknight[/eluser]
Ok fixed the joins, but can't seem to return the result set:

SELECT CAST(FirstName AS TEXT) as FirstName, CAST(Surname AS TEXT) as Surname, CAST(Gender AS TEXT) as Gender, CAST(MobilePhoneNumber AS TEXT) as MobilePhoneNumber, CAST(WorkPhoneNumber AS TEXT) as WorkPhoneNumber, CAST(Email AS TEXT) as Email, UserAccount_Id, Address_Id
FROM Contacts
JOIN UserAccounts ON Contacts.UserAccount_Id=UserAccounts.Id
WHERE username='dummy'

want to retrieve email address for user dummy

--------------------------

Thanks.

[eluser]bgreene[/eluser]
// blah,blah UserAccount_Id, Address_Id FROM Contacts JOIN Contacts ON
typo in your sql, should be: Address_Id FROM UserAccounts JOIN Contacts ON

[eluser]theknight[/eluser]
how do I iterate through the result sets, nothing gets outputted when i do:

foreach ($usernameCheck->result_array() as $row)
{
echo $row['Username'];
}


btw Address_Id etc are stored in Contacts table, which is then joined to the useraccounts table.

[eluser]bgreene[/eluser]
"are stored in contacts table" -ok, but your sql joined contacts to contacts.
better to do like:
SELECT c.FirstName,c.Surname,c.Gender,c.MobilePhoneNumber,c.WorkPhoneNumber,c.Email,
c.UserAccount_Id,c.Address_Id FROM Contacts c
JOIN UserAccounts u ON c.UserAccount_Id=u.Id
WHERE u.username=‘dummy’
then try the sql in something like heidisql.com so you can see what the result set looks like and then write the display code in your view