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?

@venance,

Did you verify that the output from the query used the field names that you used in your query? Did you run the query (in phpMyAdmin/or database tool) to confirm that the field names you created are in the result set?

@venance,

Did you verify that the output from the query used the field names that you used in your query?  Did you run the query (in phpMyAdmin/or database tool) to confirm that the field names you created are in the result set?

Yes I did that to phpMyadmin the statement is fine but the problem is on output.

@venance,

Did you verify that the output from the query used the field names that you used in your query?  Did you run the query (in phpMyAdmin/or database tool) to confirm that the field names you created are in the result set?

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.

The query() method immediately runs a database query. But you don't get the result.
Use the select() method instead

PHP Code:

->select('column as name', false); 


The query() method immediately runs a database query. But you don't get the result.
Use the select() method instead

PHP Code:

->select('column as name', false); 


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"

The query() method immediately runs a database query. But you don't get the result.
Use the select() method instead

PHP Code:

->select('column as name', false); 


 Did paginate() method ignore the use of AS keyword when trying to rename db table field/column?

 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"); 


You are not getting the result of this query. 

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()
{
  $this->db->select('column AS name', false)->where('a', 'b');
  return $this;
} 


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

---

 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"); 


You are not getting the result of this query. 

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()
{
  $this->db->select('column AS name', false)->where('a', 'b');
  return $this;
} 


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.

With method I get error "Undefined index: name"

Code:

findAll()

But if I use findAll() with

Code:

$row['column']

I get all the data

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

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)];
// Add this code and you will receive the last request to the database. 
dd($this->model->getLastQuery());