您在SQL语法中有错误;

Question

我的代码是迄今为止

 $limitText =""; 
     if($history_length){ 
      $limitText = ' limit '. $history_length; 
     } 

     if(!$history_days){ 
      $history_days = '180'; 
     } 
$db = $this->getInvokeArg('bootstrap')->getPluginResource('db')->getDbAdapter(); 
     //changing code to add min(currBalance) -- as sum(points) is valid only for debit. Also sort by desc instead of (major bug) 
     $history_stmt = $db->query("SELECT sum(points) as points,credit_date,min(currBalance) as currBalance,extRefId,transactedAt,pointType FROM credits where userid = '".$userid."' and credit_date >= date('now','-".$history_days." days') group by extRefID,pointType order by creditid desc ".$limitText); 
     $history_results = $history_stmt->fetchall(); 

     $expiry_stmt = $db->query("SELECT availablePoints,expiry_date FROM credits where userid = '".$userid."'and availablePoints > 0 and expiry_date <= date('now','+".$expiry_duration." days') order by expiry_date asc "); 
     $expiry_results = $expiry_stmt->fetchall(); 

我得到一个错误

<b>Message:</b> SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''-180 days') group by extRefID,pointType order by creditid desc' at line 1 </p> 

我能做些什么，我不能够精解

Answer 1

看来你是在一个错误的使用MySQL Date function方式 - 你实际上计划使用php date函数吗？而不是

"[...] credit_date >= date('now','-".$history_days." days') group by [...]" 

你必须写：

"[...] credit_date >= '".date('-'.$history_days.' days')."' group by [...]" 

只有变量（如$ HISTORY_DAYS）将由PHP在封闭用双引号（“）的字符串得到扩展，而不是函数调用如果。你将一个函数调用放到字符串中，php不能识别它，它会按原样传递给mysql，而不是先由php执行;但是你希望php对它进行评估，所以你必须排除它从字符串常量中将它与连接运算符（。）一起添加到您的字符串中。

然而，你的PHP日期函数调用似乎也是不正确的;得到“当前日期减去一定量的天”，最好用mysql日期函数是这样的：

"[...] credit_date >= DATE_SUB(NOW(), INTERVAL '$history_days' DAY) group by [...]" 

而只是一个普通照会的安全性：这是不是从一段脚本你提供清晰，但如果$history_days,$history_length,$user_id或$expiry_duration（用于SQL语句内部的变量）中的值只有用户设置的最低可能性，则不应将它们直接插入到SQL语句中，而是do something to prevent SQL injection。