Query using CodeIgniter's Active Record not returning a value…

Question

Here's the code I'm running.

$query = $this->db->query("DECLARE @tmp TABLE (ContactID UNIQUEIDENTIFIER)
INSERT INTO tblCD (" . ArrayToString($fieldsCDArray) . ")
OUTPUT Inserted.ContactID INTO @tmp
VALUES (" . ArrayToDBString($valuesCDArray) . ")
INSERT INTO tblCCO (ContactID, " . ArrayToString($fieldsCCOArray) . ")
VALUES ((SELECT ContactID FROM @tmp), " . ArrayToDBString($valuesCCOArray) . ")
SELECT ContactID FROM @tmp");

$result = $query->row();

I've ran $this->db->last_query() to verify that the SQL code actually works in SQL server. It ends up returning a single row, as expected. But I get nothing returned from CodeIgniter with the above code. I've also tried running it with result() and result_array() just for kicks, and still nothing.

Does CodeIgniter not like returning results from a query with more than one statement in it or do I have to get the result a different way? (For anyone asking, I’m just trying to get the last inserted id but it's a GUID so I can't use $this->db->insert_id().

Any help or suggestions would be appreciated. Thanks.

Also, ignore the ArrayToDBString() methods, those are definitely not the issues since, like I said, I've printed the last query and ran it through SQL successfully. Also, the result I get when running it through SQL is a single column called ContactID.

How are you verifying that nothing is returned? This code, as is, won't have any output. If you're not sure if it's the query itself that's causing an issue, did you test with a simpler query? You seem to have several queries here, did you try them one at a time? — Wesley Murch
I'm echoing $result and it's showing nothing. I've also done a $this->db->last_query() to pull up the SQL and then ran it directly in Management Studio and verified it returns results there. — Gary the Llama
var_dump() it instead. If it is FALSE, echo won't show it. proceed one step at a time, first see if the code works, then see why there are no results — Damien Pirsy
As I mentioned in the below answer, I tried print_r() as well and just got an empty array back. — Gary the Llama

Wesley Murch Wesley Murch · Accepted Answer · 2011-12-22T21:23:23

I'm echoing $result and it's showing nothing

row() returns an object, not a string, even though you have selected only one column. To access the value, you still have to call it explicitly:

$record = $query->row();
echo $record->ContactID;

If that still doesn't work, try running your queries one at a time instead of passing all of them to $this->db->query() at once.

...and as always, turn error reporting on with error_reporting(E_ALL) and use something like var_dump() or print_r() to debug variables, instead of echo.

Query using CodeIgniter's Active Record not returning a value…

1 Answers