MySQL select as variable to be used in a function defined in PHP

Question

I think this a pretty classical setting: Suppose I have a table tab1, with columns c1, c2. Then I want to select c1, c2 as variable of a function fun(a,b) in another select:

SELECT fun(@a,@b) as r FROM (SELECT @a:=c1, @b:=c2 FROM tab1) AS tab ORDER BY r LIMIT 10;

The fun is pre-defined in PHP:

function fun($d, $t){
      $timenow = time();
      $perd = 45000;
      $di = (int)$d;
      $tf = (float)$t;
      if ($di === 0)
      {
        return 0;
      }else{
        return (Log($di)/Log(10)+($timenow-$tf)/$perd);
      }
    }

The problem is that the variable is not updated at all. I build a test environment in http://sqlfiddle.com/#!9/9ffd6, the fun(@a,@b) is replaced by @a for simplicity.

UPDATE

It seems that my original question canot properly describe my problem. And I updated it, thanks to @wajeeh, the solution could be:

translate my php function into MYSQL form (it is a litter hard for me)
Use php function, but return the mysql results as array. (In that case I need to write the odering function by hand! and then (maybe) need another SELECT of MYSQL?)

Why do you need this function and what would be wrong with just selecting those columns directly from tab1 ? — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 22, 2016 at 5:41
fun(@a,@b) maybe very complicated, for example I need $3*Log(@a+1)/EXP(@b)$ — van abel
– van abel, Commented Apr 22, 2016 at 5:44

Wajih · Accepted Answer · 2016-04-22 06:02:05Z

1

You can call the function directly like this:

Select func(c1, c2) as r From tab1;

SQLFiddle

edited Apr 22, 2016 at 6:02

answered Apr 22, 2016 at 5:54

Wajih

4,4152 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

van abel Over a year ago

Yes, but the function must be defined in MYSQL then? In my case, I defined the function in php, and in that case fun(c1,c2) will not work.

Wajih Over a year ago

How you can call a function from PHP in MySQL ?!!

Wajih Over a year ago

To do that , You must return the results to PHP then enumerate over them calling the function you just defined. @vanabel

Wajih Over a year ago

Try the second answer @vanabel

van abel Over a year ago

Yeah, I see, but it seem quite complicated. Especially I want to use the fun as a rank function to the result (in mysql ORDER BY fun).

|

Wajih · Accepted Answer · 2016-04-22 06:27:16Z

1

public function func($param1, $param2) {
    // your code
}

$query = "Select c1, c2 From tab";
$stmt = $link->prepare($query);
if ($stmt->execute()) {
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
        func($row["c1"], $row["c2"]); 
    }
}

answered Apr 22, 2016 at 6:27

Wajih

4,4152 gold badges28 silver badges42 bronze badges

Comments

Matt Raines · Accepted Answer · 2016-04-23 00:02:42Z

1

I'm not sure what the PHP function does or how it works so this may not be a perfect translation to a MySQL function but hopefully you can use it as a starting point.

DELIMITER //
CREATE FUNCTION FUN(d INT, t FLOAT)
RETURNS FLOAT DETERMINISTIC NO SQL
BEGIN
    IF d = 0 THEN
        RETURN 0;
    ELSE
        RETURN LOG(d)/LOG(10)+(UNIX_TIMESTAMP()-t)/45000;
    END IF;
END//
DELIMITER ;

answered Apr 23, 2016 at 0:02

Matt Raines

4,2478 gold badges34 silver badges38 bronze badges

1 Comment

van abel Over a year ago

I almost do the exactly the same translation, but then there is a problem (not you answer itself, but my original problem) How to initialize it in Laravel, please see Use user defnied function of mysql in Laravel5 in case you're interested in.

Wajih · Accepted Answer · 2016-04-23 06:01:46Z

0

So you can do that:

Select IF(c1 = 0, 0, LOG(c1)/LOG(10)+(UNIX_TIMESTAMP()-c2)/45000) as r From tab1

SQLFiddle

Thanks to @Matt

answered Apr 23, 2016 at 6:01

Wajih

4,4152 gold badges28 silver badges42 bronze badges

2 Comments

van abel Over a year ago

Is there any possibility to make the constant 45000 as a vaiable that passed in by PHP? e.g. $T=45000, in fact, I defined it as an env constant in Laravel 5.

Wajih Over a year ago

if you define your query directly from PHP, of course you can do that. like: $T=45000; $sql = "Select .... $T .... From tab1" then you call the query.

Collectives™ on Stack Overflow

MySQL select as variable to be used in a function defined in PHP

4 Answers 4

6 Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related