How to make and use MySQL stored functions using PHP

How to make a useful function in mysql
I am going to show how to make MySQL functions that insert data into a people table in MySQL. I will also show how to make a function that returns a value of the average age of the people in the table.
Log into MySQL with the following line:
mysql -u Username -p
Then open the database you will use like this:
use test
First create a table called people and run this in MySQL:
CREATE table people(id int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT
,name VARCHAR(50)
,age INT
);
You can insert a user in mysql or in PHP. It will use the same SQL code either way.
You can add people with their ages to a list in 2 different ways. You can either write static PHP code and pass the name and age into the SQL code via varables and execute it or you can write a MySQL function and pass it the name and age values to insert into the table via parameters.
Here is how you would insert using the last blogs method using the mysql_magic function:

$age = 15;
$name = 'Mike';
$insert = mysql_magic("INSERT INTO people VALUES(NULL,?,?)", $name, $age);
echo "insert".$insert;


Now we will make a MySQL function that will do our inserts for us.
type this in the MySQL prompt:

DROP FUNCTION IF EXISTS insert_people;
DELIMITER $
CREATE FUNCTION insert_people(name VARCHAR(50), age INT)
RETURNS INT
BEGIN
INSERT INTO people VALUES(NULL, name, age);
RETURN 1;
END$
DELIMITER ;


Here is how you would execute the insert statement from a PHP script:

$name = "Bill";
$age = 20;
$insert = mysql_magic('SELECT insert_people(?,?) AS the_insert', $name, $age);
echo $insert[0]['the_insert']."
";

If the value echoed is a 1 then it worked.
Now lets make a function that will average the ages of the people:

DROP FUNCTION IF EXISTS average_age;
DELIMITER $
CREATE FUNCTION average_age()
RETURNS INT
BEGIN
DECLARE lv_avg_age INT;
SELECT AVG(age) INTO lv_avg_age FROM people;
RETURN lv_avg_age;
END$
DELIMITER ;


Now get the average from php:

$average = mysql_magic('SELECT average_age() AS the_average');
echo "The average age is: ".$average[0]['the_average'];

If there is something you would like to see here let me know and I will add it.