MasAdam MasAdam - 6 months ago 12
MySQL Question

Calling a Stored Function

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.

K.I K.I
Answer

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:

This is how I would declare and leter call a function:

 <?php
     /* Defining a PHP Function */
     function writeMessage() {
        echo "You are really a nice person, Have a nice time!";
     }

     /* Calling a PHP Function */
     writeMessage();
  ?>

Taken from here.

UPDATE 2:

If you want to call a stored procedure you can use

$query("CALL sellerQuality(score)") 

More on this here.

Comments