How do I change the following MySQL statement into a stored procedure or a stored function?

Here is an SQL statement that I used to calculate the distance from MySQL, which is too complex and I want to use MySQL stored procedures or storage functions to simplify

 $select = 't.*,(ACOS(SIN((' . $this->latitude . '* 3.1415) / 180 )                           *SIN((travel_user_status.latitude * 3.1415) / 180 )                           +COS((' . $this->latitude . '* 3.1415) / 180 ) *                            COS((travel_user_status.latitude * 3.1415) / 180 ) *                           COS((' . $this->longitude . '* 3.1415) / 180 -                            (travel_user_status.longitude * 3.1415)/180 )) * 6378.137)                           as distance';            $join = 'LEFT JOIN travel_user_status ON travel_user_status.userId=t.userId';

Finally want to become this:

 $select = 't.*,distance($this->latitude,$this->longitude)  as distance';            $join = 'LEFT JOIN travel_user_status ON travel_user_status.userId=t.userId';

This distance is like a MySQL function that can be called directly

May I ask how I should do it??

Reply content:

Here is an SQL statement that I used to calculate the distance from MySQL, which is too complex and I want to use MySQL stored procedures or storage functions to simplify

 $select = 't.*,(ACOS(SIN((' . $this->latitude . '* 3.1415) / 180 )                           *SIN((travel_user_status.latitude * 3.1415) / 180 )                           +COS((' . $this->latitude . '* 3.1415) / 180 ) *                            COS((travel_user_status.latitude * 3.1415) / 180 ) *                           COS((' . $this->longitude . '* 3.1415) / 180 -                            (travel_user_status.longitude * 3.1415)/180 )) * 6378.137)                           as distance';            $join = 'LEFT JOIN travel_user_status ON travel_user_status.userId=t.userId';

Finally want to become this:

 $select = 't.*,distance($this->latitude,$this->longitude)  as distance';            $join = 'LEFT JOIN travel_user_status ON travel_user_status.userId=t.userId';

This distance is like a MySQL function that can be called directly

May I ask how I should do it??

Self-Check manual AH: http://dev.mysql.com/doc/refman/5.0/en/create-function-udf.html

