Welcome Guest, Not a member yet? Register   Sign In
Querying a DB with a value from a previous query?
#1

[eluser]dobbler[/eluser]
Hi,

I'm trying to replicate this Cold Fusion code in a project.

Code:
<cfquery datasource="#application.dsn#" name="verify">
        SELECT u.UserId, u.FirstName, u.Surname, u.RoleId
        FROM Users u
        WHERE LCase(u.EmailAddress) = '#LCase(form.email)#'
        AND LCase(u.Password) = '#LCase(form.password)#'
    </cfquery>
    
    <cfquery datasource="#application.dsn#" name="verify2">
        SELECT ResearchElementId
        FROM UsertoLevel3Link
        WHERE UserId = #verify.UserId#
    </cfquery>
    
    <cfquery datasource="#application.dsn#" name="verify3">
        SELECT GroupId, GroupStatusId
        FROM lnkUserGroup
        WHERE UserId = #verify.UserId#
    </cfquery>
    
    <cfquery datasource="#application.dsn#" name="verify4">
        SELECT InstitutionId, PositionId, DepartmentId
        FROM User_Institution
        WHERE UserId = #verify.UserId#
    </cfquery>

Where you can see that the subsequent sql calls are using the UserID value from the first verify call..

Is it possible to replicate this in CI? I've tried a couple of things but keep hitting a brick wall..

Any help would be greatly appreciated.

Rob.
#2

[eluser]bohara[/eluser]
It has been a long time since I worked with ColdFusion but I think something like this is close to what you are looking for.

Code:
$verify = $this->db->select('UserId', 'FirstName', 'Sirname', 'RoleId');
$verify = $this->db->from('users');
$verify = $this->db->where('EmailAddress', $_POST['email']);
$verify = $this->db->where('Password', $_POST['password']);

$match = $verify->result_array();

if (! empty ($match)) {
    
    $row = $query->row_array();
    
    $CurrentUserId = $row['UserId'];
    
    /* ----------------------------------
    Run your other queries here
    ------------------------------------*/
    $verify2 = $this->db->select('ResearchElementId');
    $verify2 = $this->db->from('UsertoLevel3Link');
    $verify2 = $this->db->where('UserId', $CurrentUserId);
    
    $verify3 = $this->db->select('GroupId', 'GroupStatusId');
    $verify3 = $this->db->from('lnkUserGroup');
    $verify3 = $this->db->where('UserId', $CurrentUserId);
    
    ETC....
    
} else {
        
    //No match - do something else.
        
}

Cheers
#3

[eluser]dobbler[/eluser]
Thanks bohara, much appreciated.

I'm running into this problem and I've Googled it but got no results:

Code:
Fatal error: Call to undefined method CI_DB_mysql_driver::result_array() in /Users/rob/Sites/MMI/system/application/models/basic_mdl.php on line 38

Line 38 is:

Code:
$match = $verify->result_array();

It's weird..

Here's the rest of the code:

Code:
function verify($email, $password) {

/*
        $this->load->library('validation');
        $rules['EmailAddress'] = 'trim|required|callback__check_login';
        $rules['password'] = 'trim|required';
        $this->validation->set_rules($rules);        
*/        
        
        $verify = $this->db->select('UserID', 'FirstName', 'Surname', 'RoleId');
        $verify = $this->db->from('users');
        $verify = $this->db->where('lcase(EmailAddress)', $email);
        $verify = $this->db->where('lcase(Password)', $password);

        $match = $verify->result_array();

        if (! empty ($match)) {

            $row = $verify->row_array();

            $CurrentUserId = $row['UserID'];


        } else {

            //No match - do something else.

        }

I'll try searching in this forum.. I probably should have done that in the first place..

Thanks again!

Rob.
#4

[eluser]dobbler[/eluser]
I think I found it: http://ellislab.com/forums/viewthread/67513/

I'm on a different computer so can't check just yet..
#5

[eluser]bohara[/eluser]
I didn't test the code, I just put it together as an idea. Let me see if I can find out what is wrong.
#6

[eluser]bohara[/eluser]
Try switching
Code:
$verify = $this->db->select('UserID', 'FirstName', 'Surname', 'RoleId');
$verify = $this->db->from('users');
$verify = $this->db->where('lcase(EmailAddress)', $email);
$verify = $this->db->where('lcase(Password)', $password);

To
Code:
$this->db->select('UserID', 'FirstName', 'Surname', 'RoleId');
$this->db->from('users');
$this->db->where('lcase(EmailAddress)', $email);
$this->db->where('lcase(Password)', $password);
$verify = $this->db->get();

Oops, We were not actually running the query we built. Also If you are on php5 you can chain the methods together and shorten the code a bit. see here Active Record - Method Chaining

Hope that helps




Theme © iAndrew 2016 - Forum software by © MyBB