Using function within Select

Question

Using function within Select

Navigation

#1 by (4 votes)

0

I have a select, inside 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 one, and I still get the result!

Dai 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 this I can not do! When I applied the variable $valor1 or $valor2 , instead of showing the value referring to the column, it showed the column name itself, that is, VAL1 and VAL2

My question is


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

In simpler way! Even if I simply want to make an "echo $ value1" within the function, the result is "VAL1" instead of "10"

php mysql

asked by anonymous 15.09.2016 / 05:46

1 answer

Use of loop in codeigniter Lock and unlock editing of an EditText

score 4 · Accepted Answer

See the application for your code:

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

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

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

The first and last are strings. The middle one is a function call. So PHP will get its value before concatenating by calling its function with two strings , which are 'VAL1' and 'VAL2' , resulting in zero, which is the sum of < in> strings that you passed in your example , which has no relation to the values of SELECT (which has not 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 ;
};

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

Solution

If you want to use functions on the DB side, you need the functions to be 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 all the result in an array , and then loop in PHP by calling its function (this time in PHP). This could be done by 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']);
}