How to use AS keyword and Aliases sql with query() |
Hi, I have a problem with query() when trying to execute my statement with "AS" keyword, Seem like renaming db table column like this
Model: public function test(){ $this->db->query("SELECT column AS name FROM table"); return $this; } Controller $data= ['test' => $this->model->test()->paginate(5)]; Views <?php foreach ($test as $row): ?> <?= $row['name']; ?> <?php endforeach; ?> When I use $row['column'] It works fine, but when I use $row['name'] I get error msg undefined "name". How can I fix this issue? (02-20-2021, 10:02 AM)php_rocs Wrote: @venance,Yes I did that to phpMyadmin the statement is fine but the problem is on output. (02-20-2021, 10:02 AM)php_rocs Wrote: @venance,When I run sql to phpMyadmin I get result looks like this: +-----------+ | name | +-----------+ | value1 | | value2 | +-----------+ And If I use <?= $row->column ?> I get all the data, But if I use AS "newcolumnname" I get back that error. (02-20-2021, 10:49 AM)iRedds Wrote: The query() method immediately runs a database query. But you don't get the result.Ok but I want to write my SQL statement because in some case I can write complex and wrong sql without having a need to chain alots of methods. When I use: 'test'=>$this->model->test()->asObject()->paginate(1), and output this is work fine <?= $row->column ?> But the problem if I try to use renamed column "name". It throw an error "Undefined property: stdClass::$name" (02-20-2021, 11:50 AM)venance Wrote: Did paginate() method ignore the use of AS keyword when trying to rename db table field/column? I will try to explain When you call in the test method this code. PHP Code: $this->db->query("SELECT column AS name FROM table"); The paginate method in this line PHP Code: $this->model->test()->paginate(5) Makes two queries to the database: 1. Calls ths countAllResults() method. 2. Calls the findAll() method. And since you don't define any conditions, then ... you get requests like. SELECT COUNT() FROM table SELECT * FROM table LIMIT 5 OFFSET 0 <-- You will receive the result of this request. But if in the test() method you would use the code. PHP Code: public function test() Then the requests would look different SELECT COUNT() FROM table WHERE a = b SELECT column as name FROM table WHERE a = b LIMIT 5 OFFSET 0 If you have such a complex query that you cannot compose with QueryBuilder, then it is easier for you to override the paginate method in your model.
I get result if I use paginate() and output <? $row['column']?>.
But the issue I get if I try to use a renamed table field <? $row['name']?>. I get error " Undefined property: stdClass::$name". I cant figure out what cause this issue. I current using ci4 (02-20-2021, 09:15 PM)iRedds Wrote:(02-20-2021, 11:50 AM)venance Wrote: Did paginate() method ignore the use of AS keyword when trying to rename db table field/column? With method I get error "Undefined index: name" Code: findAll() But if I use findAll() with Code: $row['column'] I get all the data (02-20-2021, 09:31 PM)venance Wrote: I get result if I use paginate() and output <? $row['column']?>. Your test method doesn't affect pagination in any way. For this reason, you get the error. Pagination knows nothing about your "as name" PHP Code: $data= ['test' => $this->model->test()->paginate(5)]; |
Welcome Guest, Not a member yet? Register Sign In |