douxiong5972 2018-05-24 15:29
浏览 33
已采纳

通过PHP使用postgresql的Age函数

I am trying to filter my results through Age range. In my db, I have a table which has a column 'dob', so I am trying to use AGE function of postgreSql to get the age from 'dob'. But it's not working. Following is my code.

contact.php

<div class="form-group">
    <label> Filter by Age</label>
    <div class="input-group">
        <input id="start-age" type="text" name="from-age" class="form-control" placeholder="from" />
        <input id="end-age" type="text" name="to-age" class="form-control" placeholder="to" />
    </div>
</div>

<script>
    $(document).ready(function() {
        $('body').on('change', '#end-age', function () {
            var from_age = $('#start-age').val();
            var to_age = $(this).val();

            var queryString1;
            var queryString2;
            if(location.search && from_age != -1 && to_age != -1){
                queryString1 = getQueryObj(location.search);
                queryString2 = getQueryObj(location.search);
                $.each(queryString1,function(i,e){
                    queryString1[i] = decodeURIComponent(e);
                });
                $.each(queryString2,function(i,e){
                    queryString2[i] = decodeURIComponent(e);
                });
                queryString1.from_filter = from_age;
                queryString2.to_filter = to_age;

            } else if(from_age == -1 && to_age == -1){
                queryString1 = getQueryObj(location.search);
                queryString2 = getQueryObj(location.search);
                delete queryString1.from_filter;
                delete queryString2.to_filter;
            } else {
                queryString1 = {from_filter:from_age};
                queryString2 = {to_filter:to_age};
            }
            window.location.replace('/user?'+ $.param(queryString1) + '&' + $.param(queryString2));
        });
    });

</script>

contactController.php

<?php
class userController extends AdminController {

    function __construct(){
        parent::__construct("Contact","contacts");
    }

    function index(Array $params = []){

        $ageSql = "";
        if(isset($_GET['from_filter']) && isset($_GET['to_filter'])) {
            $from_age = $_GET['from_filter'];
            $to_age = $_GET['to_filter'];
            $ageSql = "SELECT id FROM user WHERE AGE(timestamp dob) >= $from_age AND AGE(timestamp dob) <= ($to_age)";
            $params['queryOptions']['where'][] = "contact_id = $ageSql";

        }
        parent::index($params);
    }
}

When I tried the above sql into Postgre, I am getting an error that Age is not a function defined. I want all the users in the range of the age user selects in the filter bar. Help is appreciated. TIA

  • 写回答

1条回答 默认 最新

  • douhan8009 2018-05-24 18:45
    关注

    Try to use now() instead of timestamp. Also if you want to compare only years use EXTRACT YEAR form AGE(), which will give you only year. Assuming your dob is of date or timestamp type:

    $ageSql = "SELECT id FROM table WHERE EXTRACT(YEAR FROM AGE(now(), dob)) >= 25 AND EXTRACT(YEAR FROM AGE(now(), dob)) <= 40";
    $params['queryOptions']['where'][] = "contact_id IN $ageSql"; //use 'IN' in case more than 1 row
    

    And if 'dob' is of some varchar or text type, cast it like this- dob::timestamp

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 怎么在stm32门禁成品上增加记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 解riccati方程组