使用GROUP BY,ORDER BY进行PDO查询构造
I am having trouble using GROUP BY
, ORDER BY
, LIMIT
, in general my queries work fine however when I try to get more specific I keep running into problems. I try to break it down and go step by step but I can't figure out issue.
我在使用GROUP BY,ORDER BY,LIMIT时遇到问题,一般情况下我的查询工作正常,但是当我尝试更具体时,我会遇到问题。我试图将其分解并逐步进行,但我无法弄清楚问题。
From my User Class I call DB like this:
从我的用户类我这样称为DB:
$this->_db->get('listings', array('userid', '=', $this->data()->id, ' AND ',
'email', ' = ', $this->data()->email, ' GROUP BY reference ORDER BY row ASC'));
My DB Class is set up like this:
我的DB类设置如下:
public function get($table, $where){return $this->action('SELECT * ', $table, $where);}
private function action($action, $table, $where = array()){
$operators = array('=', '>', '<', '>=', '<=' , 'AND' ,'OR', 'LIKE', 'GROUP BY','ORDER BY', 'ASC', 'DESC');
if(!empty($where)){
$sql = "{$action} FROM {$table} WHERE ";
if(count($where) > 3){
$sql .= "(";
$isOp = FALSE;
foreach ($where as $key => $value) {
if($isOp){
if(in_array($value, $operators)){
$sql .= " {$value} ";
$isOp = FALSE;
}else{return FALSE;}
}else{
$sql .= " {$value} ";
$isOp = TRUE;
}
}
$sql .= ")";
if(!$this->query($sql, array($value))->error()){return $this;}
}else if(count($where) === 3){
$field = $where[0];
$operator = $where[1];
$value = $where[2];
if(in_array($operator, $operators)){
$sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?"; // NO $value ?
if(!$this->query($sql, array($value))->error()){return $this;}
}
}
}else{
// If array is empty
$sql = "{$action} FROM {$table}";
if(!$this->query($sql)->error()){return $this;}
}
return FALSE;
}
public function query($sql, $params = array()){
$this->_error = FALSE;
if($this->_query = $this->_pdo->prepare($sql)){
$x = 1;
if(count($params)){
foreach($params as $param){
$this->_query->bindValue($x, $param);
$x++;
}
}// End IF count
if($this->_query->execute()){
$this->_lastID = $this->_pdo->lastInsertId();
try{
$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
}catch(Exception $e){
// Catch Error
}
$this->_count = $this->_query->rowCount();
}else{$this->_error = TRUE;}
}
return $this;
}
In general I am building everything from scratch by reading tutorials and following instructions from books.
一般来说,我通过阅读教程和遵循书中的说明从头开始构建所有内容。
Again I am having trouble with the following Group By, Order By, Like, Limit.. I am probably not binding it correctly however I don't have enough knowledge to find my mistake.
我再次遇到以下Group By,Order By,Like,Limit ..我可能没有正确绑定它但是我没有足够的知识来找到我的错误。
Thanks in advance.
提前致谢。
ERROR I GET
我得到的错误
SELECT * FROM listings WHERE ( userid = 4 GROUP BY reference )exception 'PDOException' with message '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 'GROUP BY reference )' at line 1
SELECT * FROM listing WHERE(userid = 4 GROUP BY reference)异常'PDOException',消息'SQLSTATE [42000]:语法错误或访问冲突:1064 SQL语法中有错误;检查与您的MySQL服务器版本对应的手册,以便在第1行的“GROUP BY参考”附近使用正确的语法
1 个解决方案
#1
I would get rid of de ( ) in you code (DB class):
我会在你的代码(DB类)中删除de():
$sql .= "(";
$sql .= ")";
The GROUP BY should not be in the () section of the WHERE.
GROUP BY不应该在WHERE的()部分。
WHERE ( userid = 4 GROUP BY reference )
WHERE(userid = 4 GROUP BY引用)
Must be:
WHERE ( userid = 4 ) GROUP BY reference;
Or better:
WHERE userid = 4 GROUP BY reference;
You use the GROUP BY function when you use SUM, COUNT, etc. in the SELECT. Else you don't need it.
在SELECT中使用SUM,COUNT等时,可以使用GROUP BY函数。否则你不需要它。
更多相关文章
- Linux第二篇----使用rpm方法安装MySQL(含各种出现的错误和排错)
- MySQL查询中的变量会导致错误
- mysql编译安装后各种常见错误集锦
- org.json Android系统错误。JSONException:在字符0处输入结束
- Navicat连接mysql8出现1251错误
- MYSQL错误代码和消息
- mySQL错误: The used table type doesn't support FULLTEXT inde
- MySQL 主从同步Out of Memory 错误分析
- PHP解析错误:语法错误,意外的T_VARIABLE