Using Function within Select

Question

I have a select, within it I have a function :

SELECT VAL1, VAL2, ".funcao('VAL1', 'VAL2')." AS soma FROM Conta WHERE soma < 100

Table structure CONTA

--------------------
| ID | VAL1 | VAL2 |
--------------------
| 1  |  10  |  50  |
--------------------
| 2  |  50  |  80  |
--------------------
| 3  |  30  |  70  |
--------------------

If I use the following structure in the function :

function funcao($valor1, $valor2)
    { 
        $retorno = $valor1 + $valor2;
        return $retorno; 
    };

I can return the value of each column in a good, and still get the result!

Then I tried to work the function more and apply the value inside a string. Ex:

function funcao($valor1, $valor2)
    { 
        $res = "Os números a ser somados são ".$valor1." e ".$valor2."";
        echo $res ;

        //Esse echo serve apenas de exemplo, pra mostrar o retorno da variável 
    };

Only that I can’t do it! When I applied the variable $valor1 or $valor2, instead of displaying the column value, it showed the column name itself, i.e., VAL1 and VAL2

My question is

Is it possible to work with these variables in a more complex function according to the example cited, without losing the value of the column? Since when I try to do this the value is as "VAL1" instead of 10 or 50.

In simpler mode! Even if I just want to do a "echo $valor1" within the function, the result is "VAL1" instead of "10"

Answer 1

View your code application:

query( "SELECT VAL1, VAL2, ".funcao('VAL1', 'VAL2')." AS soma FROM Conta WHERE soma < 100" );

In this case, PHP will put these three things together, because you used the concatenation operator (.):

• "SELECT VAL1, VAL2, "

• funcao('VAL1', 'VAL2')

• " AS soma FROM Conta WHERE soma < 100"

The first and the last are strings. The middle is a function call. Then PHP will get its value before concatenating, calling its function with two strings, who are 'VAL1' and 'VAL2', resulting in zero, which is the sum of the two strings that you passed in your example, which has nothing to do with the values of SELECT (that even happened yet).

Then the result will be this:

query( "SELECT VAL1, VAL2, 0 AS soma FROM Conta WHERE soma < 100" );

Only after concatenating the string in PHP, it will be sent to Mysql.

In the second example the thing becomes more evident:

function funcao($valor1, $valor2)
{ 
    $res = "Os números a ser somados são ".$valor1." e ".$valor2."";
    echo $res ;
};

Yet you don’t have return in function, only echo, it becomes easier to realize that 'VAR1' and 'VAR2' are only strings for PHP, so they exit on the screen as they were sent. And even if you put a return, to string final would not be a valid SQL.

Solution

If you want to use functions on the DB side, you need the functions to be those of SQL, not PHP. Example:

query( "SELECT VAL1, VAL2, VAL1+VAL2 AS soma FROM Conta WHERE soma < 100" );

Another example, with strings on the SQL side:

query( "SELECT VAL1, VAL2, CONCAT( "Resultado:", VAL1+VAL2, "rea ) AS soma FROM Conta" );

Another possibility would be to get the whole result in a array, and then loop in PHP calling its function (this time in PHP). This could be done when displaying the values on the screen.

Example:

while ($row = $result->fetch_row()) {
    echo $row['VAL1'].' somado com '.$row['VAL2'].' resulta em '.($row['VAL1']+$row['VAL2']);
}