CI Database/Active Records failing for what I need to do?

[eluser]Unknown[/eluser]
Hello.

I've never used JOIN queries before today. After reading about them, I can see the IMMENSE amount of power they offer and I can see how using them would make my life a hell of a lot easier.

With that said, I was trying to go about implementing the usage of JOIN queries into this application I'm writing. I wrote out this query:

SELECT c_transactions.c_id, c_transactions.cash, c_transactions.debt, c_transactions.product, customers.alias FROM c_transactions, customers WHERE c_transactions.date > "' . $yesterday . '" AND (c_transactions.c_id = customers.id)

When I replace $yesterday with '2010-11-01 00:00:00' in Emma (my MySQL administration tool) the query runs fine and returns exactly the dataset that I want.

I tried re-writing the query using CI's active records class. The code is as follows. I couldn't get this method to work at all:

Code:

$this->db->select('c_transactions.date, c_transactions.c_id, c_transactions.cash, c_transactions.debt, c_transactions.product, customers.alias');
        $this->db->from('c_transactions, customers');
        $this->db->where('date >', $yesterday);
        $this->db->where('c_transactions.c_id', 'customers.id');
        $transactions = $this->db->get();

When I saw this didn't work, I decide to try directly setting the SQL, using the code:

Code:

$this->db->sql('SELECT c_transactions.c_id, c_transactions.cash, c_transactions.debt, c_transactions.product, customers.alias FROM c_transactions, customers WHERE c_transactions.date > "' . $yesterday . '" AND (c_transactions.c_id = customers.id);');
        $transactions = $this->db->query();

This also doesn't work. It returns no errors or anything. I just get a blank screen. That prompted me to enter echo() statements after every line of code, and I can see it's breaking right when I call $this->db->sql().

Any ideas? Maybe I'm simply utilizing the Active Records class improperly. I would prefer to use Active Records as opposed to directly setting the SQL, but I'd be happiest just to find a solution at this point.

Thanks!

[eluser]Mischievous[/eluser]
is there a $this->db->sql()?.... thought it was $this->db->query()

[eluser]Mischievous[/eluser]
Threw some code together for ya... (obviously not tested, but worth a shot)

Code:

$yesterday = ‘2010-11-01 00:00:00’;
$query = sprintf('SELECT T.c_id, T.cash, T.debt, T.product, C.alias
                            FROM c_transactions as T
                            INNER JOIN customers as C ON T.c_id = C.id
                            WHERE T.date > %s',
                            $this->db->escape($yesterday)
                            );
if($result = $this->db->query($query))
{
    if($result->num_rows > 0)
    {
        foreach($result->result() as $row)
        {
            echo "<pre>";
            print_r($row);
            echo "</pre>";
        }
        return true;
    } else {
        echo "No results found.";
        return false;
    }
} else {
    echo sprintf("DB Query Failed: %s", $query);
    return false;
}

[eluser]Unknown[/eluser]
[quote author="Mischievous" date="1289442810"]Threw some code together for ya... (obviously not tested, but worth a shot)

Code:

$yesterday = ‘2010-11-01 00:00:00’;
$query = sprintf('SELECT T.c_id, T.cash, T.debt, T.product, C.alias
                            FROM c_transactions as T
                            INNER JOIN customers as C ON T.c_id = C.id
                            WHERE T.date > %s',
                            $this->db->escape($yesterday)
                            );
if($result = $this->db->query($query))
{
    if($result->num_rows > 0)
    {
        foreach($result->result() as $row)
        {
            echo "<pre>";
            print_r($row);
            echo "</pre>";
        }
        return true;
    } else {
        echo "No results found.";
        return false;
    }
} else {
    echo sprintf("DB Query Failed: %s", $query);
    return false;
}

[/quote]

Worked perfectly. I really appreciate it!