Codeigniter query with alias and number as column

I would like to replace column name with numbers in codeigniter

It does work well with letters but not with numbers

$this->db->select ('Observation.id_observation as 1');
            $this->db->from ( 'Observation' );
            $this->db->join ( 'Enregistrement', 'Enregistrement.id_observation = 
             Observation.id_observation' , 'left' );
            $this->db->where('Observation.id_parcelle', $id_parcelle);
            $this->db->where('Observation.id_observateur', $user_id);
            $this->db->where('Observation.date_observation >=', $date_start);
            $this->db->where('Observation.date_observation <=', $date_end);
            return $this->db->get()->result();

Try the following

$this->db->select ('Observation.id_observation as `1`');

This will generate query as

Select `observation` as `1`....

From the Docs

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

2 Table Join, Ambiguous Columns, Aliasing, Active Record, Don't use Active Record and alias the columns in the SQL query. I was looking for something like $this->db->alias('table.column' => 'alias'),� The above method is an alias of getCustomResultObject(). getResultArray() This method returns the query result as a pure array, or an empty array when no result is produced. Typically you’ll use this in a foreach loop, like this:


You can set alias name as string so you need to give within single quotes otherwise you will get syntax error

                $this->db->select ('Observation.id_observation as `1`',FALSE);
                $this->db->from ( 'Observation' );
                $this->db->join ( 'Enregistrement', 'Enregistrement.id_observation = 
                 Observation.id_observation' , 'left' );
                $this->db->where('Observation.id_parcelle', $id_parcelle);
                $this->db->where('Observation.id_observateur', $user_id);
                $this->db->where('Observation.date_observation >=', $date_start);
                $this->db->where('Observation.date_observation <=', $date_end);
                return $this->db->get()->result();

OR

$var = '1';
$this->db->select ('Observation.id_observation as `'.$var.'`',FALSE);

OR

 $query=$this->db->query('SELECT Observation.id_observation as "1" FROM Observation LEFT JOIN Enregistrement ON Enregistrement.id_observation = 
   Observation.id_observation WHERE Observation.id_parcelle = '.$id_parcelle.' AND Observation.id_observateur = '.$user_id.' AND Observation.date_observation >= "'.$date_start.'" AND Observation.date_observation <= "'.$date_end.'" ');
    return $query->result();

query builder select with alias � Issue #4134 � bcit-ci/CodeIgniter , "column" AS "prefix"."alias" , it adds two " around the . I didn't find anything about aliases in the query builder documentation, except for� Type 1 works, type 2 gets escaped the wrong way. query builder generates "table"."column" AS "prefix"."alias", it adds two " around the . I didn't find anything about aliases in the query builder documentation, except for select_avg, etc. I really think this is a bug. I am not certain if my usage was "wrong" in the first place, but I need these


You can try this:

$this->db->select ("Observation.id_observation as '1'");

Hope It will work.

CodeIgniter Active Record aliasing column names to prevent , With regular queries you would alias the id column of the second table, which basically just changes it's name temporarily for this query. Once you have a prepared query you can use the execute() method to actually run the query. You can pass in as many variables as you need in the query parameters. The number of parameters you pass must match the number of placeholders in the query. They must also be passed in the same order as the placeholders appear in the original query:


This is what i had to do in the controller ! I very don't like this solultion but it does work:

$conn = new mysqli($servername, $username, $password, $dbname);
                // Check connection
                if ($conn->connect_error) {
                    die("Connection failed: " . $conn->connect_error);
                }
                $sql = "SELECT 
                Observation.id_observation as '0', 
                Observation.checkseries as '1'
                FROM Observation 
                LEFT JOIN Enregistrement ON Enregistrement.id_observation = Observation.id_observation
                WHERE 
                Observation.id_parcelle = '$parcellecultures->id_parcelle'
                AND Observation.id_observateur = '$user'
                AND Observation.date_observation >= '$date_start'
                AND Observation.date_observation <= '$date_end'
                ";
                //echo $sql;
                $result = $conn->query($sql);

                //print_r($result);exit;
                $conn->close();

I still look for another solution ....

Codeigniter JOIN - not a unique table/alias issue, Codeigniter JOIN - not a unique table/alias issue - php. Error Number: 1066 Not unique table/alias: 'p_results' SELECT `book_uk`.`pin` MySQL return rows where column contains categories defined by array (and add weight to the results ). Use the AS keyword with your column aliases. The query above can be written as: SELECT first_name, last_name, salary AS annual_salary, hire_date, start_date FROM employee; This makes it clear which columns are to be shown in the results and which columns have an alias. That’s why I recommend using the AS keyword in your column aliases.


mysql_fetch_field - Manual, mysql_fetch_field — Get column information from a result and return as an object 1 if the column is a non-unique key; numeric - 1 if the column is numeric; blob - 1 if the deal with field aliases - ie no table name (SELECT T_1.a AS temp) Query Builder Class¶ CodeIgniter gives you access to a Query Builder class. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases, only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own


Generating Query Results — CodeIgniter 4.0.4 documentation, The above method is an alias of getCustomResultObject() . The number of FIELDS (columns) returned by the query. CodeIgniter\Database\BaseResult. The above method is an alias of result_object().. You can also pass a string to result() which represents a class to instantiate for each result object (note: this class must be loaded)


CodeIgniter v4.0.0 API � \CodeIgniter\Database\BaseConnection, Add a table alias to our list. addTableAlias; Returns the total number of rows affected by this query. affectedRows; This function enables you to call PHP� Query Builder Class¶ CodeIgniter gives you access to a Query Builder class. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own