php - 如何在 mysql 查询中执行 php 函数

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;
    }
}

希望有人能帮忙。我正在执行两个邮政编码之间的距离检查(以英里为单位)，并将所有小于距离的邮政编码添加到结果中。

以前我所做的就是迭代所有匹配性别和年龄的邮政编码，然后进行小于距离检查的英里，但结果在 json 数组中，现在希望在 mysql 查询中执行它(如果可能的话，最好使用 codeigniter)框架。非常感谢

Answer 1

首先，使用绑定(bind)参数而不是 XSS clean:

参见Does CodeIgniter automatically prevent SQL injection?

假设您有经度和纬度，您可以添加它来选择距离

$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            
        ");

您还需要一个 order by 来排序，以及一个 having 来仅返回距离内的值。

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