+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: CI Database/Active Records failing for what I need to do? (/showthread.php?tid=35776)
CI Database/Active Records failing for what I need to do? - El Forum - 11-10-2010
[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:
Quote: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!
CI Database/Active Records failing for what I need to do? - El Forum - 11-10-2010
[eluser]Mischievous[/eluser]
is there a $this->db->sql()?.... thought it was $this->db->query()
CI Database/Active Records failing for what I need to do? - El Forum - 11-10-2010
[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;
}CI Database/Active Records failing for what I need to do? - El Forum - 11-11-2010
[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;
}Worked perfectly. I really appreciate it!