Problem using REPLACE() in mysql

Hi,
this is is my select

PHP Code:

$builder = $this->db->table('table_name')->select('id,REPLACE(price,'.',',') AS my_price');
echo $builder->getCompiledSelect(); 


The result is:

Code:

SELECT `table_name`.`id`, REPLACE(table_name.price, '.', ', ') AS my_price FROM `table_name`

there is a space after the comma (', ') of replace. Is it a bug? how can I solve it?

Try select("...", false) ?

Simple CI 4 project for beginners codeigniter-expenses ( topic )

Try select("...", false) ?

yes, but it doesn't work

I see, it appears that the extra space is being added specifically when you're replacing with a comma. This might be related to how the database driver or the query builder handles certain characters. although I am yet to find out why, will update if I find any.

---

The issue is stemming from the fact that they are splitting the select part using a comma ','. To gain a better understanding, examine the output of these operations and observe why the extra space is introduced after the comma.
function that causing this mess,,,,, I have added step1 and step2 for demo

PHP Code:

protected function compileSelect($selectOverride = false): string
    {
        if ($selectOverride !== false) {
            $sql = $selectOverride;
        } else {
            $sql = (!$this->QBDistinct) ? 'SELECT ' : 'SELECT DISTINCT ';

            if (empty($this->QBSelect)) {
                $sql .= '*';
            } elseif ($this->QBSelect[0] instanceof RawSql) {
                $sql .= (string) $this->QBSelect[0];
            } else {
                // Cycle through the "select" portion of the query and prep each column name.
                // The reason we protect identifiers here rather than in the select() function
                // is because until the user calls the from() function we don't know if there are aliases
                foreach ($this->QBSelect as $key => $val) {
                    $noEscape = $this->QBNoEscape[$key] ?? null;
                    $this->QBSelect[$key] = $this->db->protectIdentifiers($val, false, $noEscape);
                }
                #step 1
                print_r($this->QBSelect);
                die;
                $sql .= implode(', ', $this->QBSelect);
                #step 2
                exit($sql);
            }
        }

        if (!empty($this->QBFrom)) {
            $sql .= "\nFROM " . $this->_fromTables();
        }

        if (!empty($this->QBJoin)) {
            $sql .= "\n" . implode("\n", $this->QBJoin);
        }

        $sql .= $this->compileWhereHaving('QBWhere')
            . $this->compileGroupBy()
            . $this->compileWhereHaving('QBHaving')
            . $this->compileOrderBy();

        if ($this->QBLimit) {
            $sql = $this->_limit($sql . "\n");
        }

        return $this->unionInjection($sql);
    } 


#Step 1 out with 'comma' and other string as a replacement string

PHP Code:

WITH COMMA

Array
(
    [0] => `id`
    [1] => REPLACE(price
    [2] => '.'
    [3] => '
    [4] => ') AS my_price
) 


PHP Code:

WITHOUT COMMA

Array
(
    [0] => `id`
    [1] => REPLACE(price
    [2] => '.'
    [3] => 'AA') AS my_price
) 


and finally, they are imploding all parts with ", " Here is why you are having an extra comma in your replacement string
Step 2 output

PHP Code:

SELECT `id`, REPLACE(price, '.', ', ') AS my_price 


if you want to solve this you have to write your code in this way to avoid this conflict, I have tested this and it works.

PHP Code:

$this->db = db_connect();
 $builder = $this->db->table('table_name')->select(["id", "REPLACE(price, '.', ',') AS my_price"]);
 echo $builder->getCompiledSelect(); 


Hope it helps

Learning Codeigniter 

Yes, it works.
thank you so much