MySQL错误1064执行SELECT查询时

问题描述：

function search_keyword(){ 
      $keyword = trim($_POST['keyword']); 
      $search_explode = explode(" ", $keyword); 
      $x = 0; 

      $sql = " (SELECT name, id_global_info AS id, body AS body, tag AS tag ,info_type_id AS info_type, \"global_info\" AS mytable FROM global_info WHERE "; 
      foreach($search_explode as $each){ 
       $x++; 
       if($x == 1){ 
        $sql .= " name LIKE '%$each%' ";}       
       else { 

        $sql .= " AND name LIKE '%$each%' "; 
       } 
      } 

       $sql .= ") UNION ALL "; 

       $sql .= " (SELECT name, id_person AS id, surname AS body, info AS tag , location AS info_type, \"person\" AS mytable FROM person WHERE "; 
      foreach($search_explode as $each){ 
       $x++; 
       if($x == 1){ 
        $sql .= " name LIKE '%$each%' ";}       
       else { 

        $sql .= " AND name LIKE '%$each%' "; 
       } 
      } 

       $sql .= ") UNION ALL "; 

       $sql .= "(SELECT name, id_event AS id, body AS body, caffe_id AS tag , date AS info_type, \"event\" AS mytable FROM event WHERE "; 
      foreach($search_explode as $each){ 
       $x++; 
       if($x == 1){ 
        $sql .= " name LIKE '%$each%' ";}       
       else { 

        $sql .= " AND name LIKE '%$each%' "; 
       } 
      } 

      $sql .= ") UNION ALL "; 

       $sql .= "(SELECT name, id_caffe AS id, description AS body, adress AS tag, location_id AS info_type, \"caffe\" AS mytable FROM caffe WHERE "; 
      foreach($search_explode as $each){ 
       $x++; 
       if($x == 1){ 
        $sql .= " name LIKE '%$each%' ";}       
       else { 

        $sql .= " AND name LIKE '%$each%' "; 
       } 
      } 

      $sql .= ") "; 
      echo $sql; 
      $q = $this->db->query($sql); 
      return $q = $q->num_rows() == 0 ? FALSE : $q->result(); 
     }

当我搜索exapmle

"mali oglasi"

我获得以下错误：

Error Number: 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 'AND name LIKE '%mali%' AND name LIKE '%oglas%') UNION ALL ( SELECT name, id_e' at line 1

这是MySQL正在生成查询：

(SELECT name, id_global_info AS id, body AS body, tag AS tag ,info_type_id AS info_type, "global_info" AS mytable FROM global_info WHERE name LIKE '%mali%' AND name LIKE '%oglas%') 
UNION ALL 
(SELECT name, id_person AS id, surname AS body, info AS tag , location AS info_type, "person" AS mytable FROM person WHERE AND name LIKE '%mali%' AND name LIKE '%oglas%') 
UNION ALL 
(SELECT name, id_event AS id, body AS body, caffe_id AS tag , date AS info_type, "event" AS mytable FROM event WHERE AND name LIKE '%mali%' AND name LIKE '%oglas%') 
UNION ALL 
(SELECT name, id_caffe AS id, description AS body, adress AS tag, location_id AS info_type, "caffe" AS mytable FROM caffe WHERE AND name LIKE '%mali%' AND name LIKE '%oglas%')

什么似乎是错误？

答

第一件事的第一件事：不要忘记逃避你的输入价值。这可以在你的情况下，无论是在初始值来实现，或者在$each

// If your query() method calls mysql_query() 
$keyword = mysql_real_eascape_string(trim($_POST['keyword'])); 
// Or if query() is mysqli::query() 
$keyword = $this->db->real_escape_string(trim($_POST['keyword'])); 
// Or if this is Codeigniter's API 
$keyword = $this->db->escape_like_str(trim($_POST['keyword']));

你需要在每个foreach循环的开始重置$x foreach循环的每次迭代：

 // Reset $x to 0 before the start of each of your loops. 
     $x = 0; 
     foreach($search_explode as $each){ 
      $x++; 
      if($x == 1){ 
       $sql .= " name LIKE '%$each%' ";}       
      else { 

       $sql .= " AND name LIKE '%$each%' "; 
      } 
     }

注意：通常建议使用参数化查询，而不是通过串联和插值来构建查询。 Codeigniter使用?占位符。

我已经改变了你建议的方式，但我仍然得到同样的错误 – Sasha 2012-03-29 12:54:02

@Sasha对不起 - 我听错了。这里真正的问题是，您需要将'$ x'的值重置为每个foreach循环的零。 – 2012-03-29 12:57:01

这是问题所在。谢谢：） – Sasha 2012-03-29 13:01:06

答

您查询错误驻留在第二，第三和第四SELECT语句，你有WHERE AND name而不是WHERE name

MySQL错误1064执行SELECT查询时

相关推荐