Calling a Stored Function

Question

So I have a stored function like this,

CREATE FUNCTION `sellerQuality` (score real) RETURNS varchar(10) charset latin1
    DETERMINISTIC
BEGIN
    DECLARE theValue varchar(10);
    IF (score = 0) THEN 
        set theValue = 'UNKNOWN';
    ELSEIF (score <= 10 and score > 7) THEN 
        SET theValue = 'GOOD';
    ELSEIF ( score<=7 and score>0) THEN 
        SET theValue = 'POOR';
    END IF;
    return (theValue);
END;

I also have a table called PERFORMANCE that looks like this

PERFORMANCE - ID, NAME, AGE, SCORE

What I want is basically to print an output of the name, age, score and also the standing of the score.

Example: Tony, 29, 90, GOOD ; Rob, 20, 60, POOR

So what I did is something like this:

$info = "SELECT * FROM PERFORMANCE P";   
    $query = mysqli_query($conn, $sql);
    $query = mysqli_query($conn, $info) or die(mysqli_error($conn));
    $count = mysqli_num_rows($query);
    if ($count != 0) {
        while ($result = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
            $quality = SELECT `sellerQuality` ($result["score"]);
            echo $result["name"] . "<br>";
            echo $result["age"] . "<br>";
            echo $result["score"] . "<br>";
            echo $quality. "<br>";
        }
    } else {
        echo "No results found.";
    }
    mysqli_close($conn);

I thought it looks good already, however when I run the page it gives me:

Failed to load resource: the server responded with a status of 500 (Internal Server Error)

UPDATE:

I just want to know whether this is the proper way to call function from mySQL to php?

$quality = SELECT `sellerQuality`($result["score"]) AS `sellerQuality`;

Thanks.

A "500 Internal Server Error" status code (or a blank page) means that your script is throwing an error but you haven't configured PHP to display error messages. That's something you need to fix before you go further; it's impossible to code properly without the aid of error messages. Here's a [brief explanation](http://stackoverflow.com/a/5680885/13508). The error reporting thumb rule is: show in development, log in production. — Álvaro González, May 31 '16 at 14:40
@ÁlvaroGonzález I added `ini_set('display_errors', 1); error_reporting(~0);` already, but I still looks the same. Why the error doesn't appear? — MasAdam, May 31 '16 at 14:46
The error happens because you have [invalid PHP code](https://eval.in/580363), thus the code you've added won't run either. The error messages can probably be found in the web server error log. To catch parse errors, you need to set error reporting features from outside (that depends on how you run PHP, but system-side php.ini is a good place for your local development box). — Álvaro González, May 31 '16 at 15:01
In case it isn't clear yet, your PHP is invalid because you cannot inject raw SQL inside PHP. You always have to provide SQL as PHP string. — Álvaro González, May 31 '16 at 15:49

K.I · Accepted Answer · 2016-06-01T13:35:39.363

1

In your page include

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

To see all the errors.

I am guessing the error will be on this line:

$quality = SELECT `sellerQuality` ($result["score"]);

But its better to let php tell you where your errors are.

UPDATE:

If you want to call MySQLfunction you can use

$quality = $query('SELECT sellerQuality(score) AS quality');

And later utilize it like this:

echo "The quality is: ".$quality[0]['quality'];

Example taken from here.

edited Jun 01 '16 at 13:35

answered May 31 '16 at 14:51

K.I

568
1
6
19

Sorry, I added this above my session start and it still didn't do anything. Where can I see the error? – MasAdam May 31 '16 at 14:56
I think you are right, I'm just going to write the function on php code. – MasAdam May 31 '16 at 15:14
Wait, isnt that mean I'm using function from php instead of mySQL? – MasAdam May 31 '16 at 15:18
Yes thats correct. If you want to call a stored procedure you can use $query("CALL p(1)") , replace p with your procedure name and 1 with your value. More on this here: http://php.net/manual/en/mysqli.quickstart.stored-procedures.php – K.I May 31 '16 at 15:29
The stored routine is a function, not a procedure. If you use `CALL` you'll simply make MySQL run some code and then discard the result. – Álvaro González Jun 01 '16 at 12:27
1

Thank you for pointing this out @Álvaro González, I have updated my answer it should be correct now. Also please note, that the question was updated multiple times and it was formulated very differently at the start that's why it was not answered correctly. – K.I Jun 01 '16 at 13:29

Calling a Stored Function

1 Answers1