将 sql(长文本)中的日期与 php 中的整数进行比较


Compare date from sql (longtext) by an integer from php

在我的项目中,我有来自mysql的wordpress自定义字段数据:

---------------------------------------------------
id    | post_id   |   meta_key  |   meta_value 
---------------------------------------------------
1     | 200       |   age_min   |   5    
2     | 200       |   age_max   |   8   
3     | 399       |   ...       |   ...

我的表结构

id => aut_increment 
meta_key => varchar(255)
meta_value => longtext

从我的sql脚本中,我必须在"age_min和age_max"和子项编号之间找到子项:

SELECT DISTINCT p.ID 
        , a.meta_value as nbrmin, b.meta_value as nbr_enfants_min , c.meta_value as age_min, d.meta_value as age_max
        FROM `6288gjvs_posts` as p 
        LEFT JOIN 6288gjvs_postmeta as a ON a.post_id = p.ID 
        LEFT JOIN 6288gjvs_postmeta as b ON b.post_id = p.ID 
        LEFT JOIN 6288gjvs_postmeta as c ON c.post_id = p.ID 
        LEFT JOIN 6288gjvs_postmeta as d ON d.post_id = p.ID 
        WHERE p.post_type = 'product' AND post_status = 'publish' 
        AND a.meta_key = 'childs_min_number' 
        AND a.meta_value <= 2
        AND b.meta_key = 'childs_max_number' 
        AND b.meta_value >= 2
        AND c.meta_key = 'age_min' 
        AND c.meta_value = 1
        AND d.meta_key = 'age_max' 
        AND d.meta_value >= 10

当我启动此查询时,这无法正常工作,因为meta_value类型是长文本,而我的 php 变量($this->age_min、$this->age_max )是整数,所以我无法比较这两种不同的类型?

我试图像这样转换,但没有运气:

... CONVERT(a.meta_value, DECIMAL) ...

如本网站所述:Mysql 转换函数

有人可以帮助我吗?

谢谢。

如果我

错了,请原谅我,但不应该AND b.meta_value >= {$this->age_max}吗?

你不需要

转换类型我认为错误是一致的 AND a.meta_value <= {$this->age_min}

不应该是AND a.meta_value >= {$this->age_min}吗?