-1

While it appears I'm unable to get any satisfactory answers concerning my last question posted here, I'm now looking to complete my project using MySQL instead of SQLite as my database background in a simple localhost environment. The one thing I need to accomplish is using a user defined function (UDF) which PHP can handle nicely with SQLite using the following code in an UPDATE query:

function nPoints($n1, $n2, $n3, $n4)
{
    $numb = array($n1, $n2, $n3, $n4);
    sort($numb);
    return $numb[0] * 1 + $numb[1] * 2 + $numb[2] * 3 + $numb[3] * 4;
}

Essentially I am taking 4 numbers and doing a calculation which multiplies the smallest number by 1, then next smallest by 2, 3rd smallest by 3, and then the largest by 4 regardless if there are any ties or not. can I do this likewise as a MySQL user-defined function, and if not then what's the cleanest way without doing things the trickier way such as quick sort logic using for-next loops and such?

If PHP can handle registering such user-defined functions like it can with SQLite that would be better actually but how?

jfalberg
  • 141
  • 4
  • 16

1 Answers1

1

Why use sort()? Just write a quick sorting algorithm its only 4 numbers. Alternatively you can do this in PHP (I assume you are connecting PHP to MYSQL).

No reason not to

SELECT * FROM table; //Perform Calculation UPDATE table SET x = y WHERE something;

Brute Force can be:

$max = $N1 + $N2*2 + $N3*3 + N4*4; IF ($max < $N1 + $N2*2 + $N4*3 + N3*4) THEN $max = $N1 + $N2*2 + $N4*3 + N3*4 END IF ... ... ... return $max;

Eugene Lorman
  • 66
  • 3
  • I would go for the PHP solution given by Eugene Lorman, it's easier to maintain on the long run. – Samuel Ramzan Dec 17 '18 at 05:09
  • Sorry I'm lost here. 1. So in a MySQL function, I cannot use arrays and sort functions to simplify what I need to accomplish and resort to doing things the old way? 2. Can I use PHP like I originally did to accomplish having a MySQL function as desired? If so then how? – jfalberg Dec 17 '18 at 12:19
  • I suppose as a last minute resort I could follow [this example] (https://stackoverflow.com/questions/25070577/sort-4-numbers-without-array). – jfalberg Dec 17 '18 at 18:36