How to perform a php function within a mysql query

Question

function SearchMembers()
{
    // grab user input
    //$my_gender = $this->security->xss_clean($this->input->post('my_gender'));
    $this->security->xss_clean($this->input->post('looking_for_gender'));
    $age_from = $this->security->xss_clean($this->input->post('age_from'));
    $age_to = $this->security->xss_clean($this->input->post('age_to'));
    $member_postcode = $this->security->xss_clean($this->input->post('postcode'));
    $member_distance = $this->security->xss_clean($this->input->post('distance'));                               

    // Prep the query

    $this->db->where('Gender', $looking_for_gender);
    $this->db->where('Age >=', $age_from);
    $this->db->where('Age <=', $age_to);
    $miles =  $this->search_form_model->distCalc('postcode',$member_postcode);
    $this->db->where($miles < $member_distance);
    // Run the query
    $query = $this->db->get('member');
    // Let's check if there are any results

    if ($query->num_rows() > 0)
    {
        foreach ($query->result() as $row)
        {
            return $query->result();
        }

        // If the previous process did not validate
        // then return false.
        return false;
    }
}

Hope someone can help. I'm performing a distance check between two postcodes in miles, and add all the ones that are less than distance to the result.

Previously all I have done is iterated through all postcodes that match gender and age then done a miles less than distance check and but the results in a json array, now a want to perform it within the mysql query if possible ideally using codeigniter framework. many thanks

That `return` will just exit the function at the first iteration — Damien Pirsy, Nov 19 '13 at 20:54
MySQL cannot execute PHP code. You can build a UDF in mysql to do that sort of thing, but it won't be in PHP. Your sole option, with no changes to the setup, is to fetch all data to php and then apply filters there, which will be very inefficient if your filter tends to reject most of the results. — Marc B, Nov 19 '13 at 20:59
It will both `return` on the first iteration, AND return the second result, not the first. Are you sure that is how you want the function to work? — Jeremy Rodi, Nov 19 '13 at 21:00
I just get the following output ,SELECT * FROM (`member`) WHERE `Gender` = 'female' AND `Age` >= '18' AND `Age` <= '55' AND `0` IS NULL I'm gathering this is because the distCalc is not working like i hoped. — user1136994, Nov 19 '13 at 21:02
Please edit your question - you appear to have missed half the class and method(function) off the start of your code — vogomatix, Nov 19 '13 at 21:06
Added the rest of the function, I know the distCalc works fine when previously used. — user1136994, Nov 19 '13 at 21:18

score 0 · Answer 1 · edited May 23 '17 at 11:56

First and foremost, use bound paramaters instead of XSS clean:

See Does CodeIgniter automatically prevent SQL injection?

Assuming you have the longitude and latitude, you can add this to select the distance

$this->db->select("
            Gender,Age, --etc.
            (
                (
                    ACOS(
                          SIN($lat * PI() / 180) 
                        * SIN(`lat` * PI() / 180) 
                        + COS($lat * PI() / 180) 
                        * COS(`lat` * PI() / 180) 
                        * COS(($lon - `lon`) 
                        * PI() / 180)) 
                        * 180 / PI()
                ) * 60 * 1.1515
            ) AS distance            
        ");

You will also need an order by to sort, and a having to only return values within the distance..

$this->db->order_by('distance');
$this->db->having('distance <= '.$member_distance.' OR postcode='.$member_postcode);

How to perform a php function within a mysql query

1 Answers1