Hi,
I am having trouble running a UNION query using the db class and was wondering if anyone could help me.
This is my query in long form:
(select f.form_field_id, f.label, f.value, d.field_name, d.db_name FROM form_fields f INNER JOIN fields_default d ON f.field_id=d.field_id WHERE f.form_id = 25 AND f.is_custom = 0) union (select f.form_field_id, f.label, f.value, d.field_name, d.db_name FROM form_fields f INNER JOIN custom_fields d ON f.field_id=d.custom_field_id WHERE f.form_id = 25 AND f.is_custom = 1)
The query produces the correct result when run through phpmyadmin, however I cannot produce the result with fuel.
I have tried the following ways:
$result = \DB::query($query)->execute();
$result = \DB::query($query, \DB::SELECT)->execute();
When i print_r($result) i see the following:
[_result:protected] => Resource id #41 [_total_rows:protected] => 4
[_result:protected] => Resource id #41 [_total_rows:protected] => 4
So the result is showing the correct number of rows but I cannot retrieve the data.
Am I doing something wrong?
Thanks
Paul
i think i got the same problem with Paul ..
Here's my code ...
$query = \DB::query('SHOW CREATE TABLE `blog`')->execute();
but i only got the number of result as (int) when i var_dump .. in this case ..i only got ... int(1)
When i used as_array() ... i got this error
ErrorException [ Error ]: Call to a member function as_array() on a non-object
Ok, i had the same behavior. I have the num_of_rows, but not the result itself. \DB::SELECT fixed the problem.
Could someone explan me why and how \DB::SELECT, as a second patameter for DB::qiery(), fixed the problem. Didn't see anything about \DB::SELECT in the docs
If you don't specify a type, the DB query class with try to autodetect it. that works for "SELECT", "INSERT" and "CREATE".
This type is then used by the DB drivers query method to determine what the expected return value is. For SELECT, that would be the result, for INSERT or CREATE an insert_id and row_count, and for others only an affected row count.
So if you have some SQL that does return a resultset but isn't a SELECT, you'll have to tell it to query() manually.
Donate
