duanpai9945
2017-04-16 21:44 阅读 30
已采纳

绑定参数时MySQLi语法错误

Hi I'm working on displaying an event during a certain time period. The code is as follows. It was working when I ran the query without binding the parameters, but doing so caused me some issues. The code is as follows:

$category = $_GET['category'];
$time = $_GET['time'];
$time_ahead = strtotime("+1 month", $time);

$query = "SELECT * FROM happenings ";
$query .= "WHERE ((date_start > ? AND date_start < ?) OR (date_start < ? AND date_end > ?) OR (date_end > ? AND date_end < ?))";
if($category)
$query .= " AND category = ?";
$query .= " ORDER BY date_start ASC";

$stmt = $mysqli->prepare($query) or die ("Could not prepare statement:" . $mysqli->error);

if($category)
$stmt->bind_param('sssssss', $time, $time_ahead, $time, $time_ahead, $time, $time_ahead, $category) or die("Could not bind parameters statement:" . $stmt->error);
else
$stmt->bind_param('ssssss', $time, $time_ahead, $time, $time_ahead, $time, $time_ahead) or die("Could not bind parameters statement:" . $stmt->error);

$stmt->execute() or die("Execute failed: (" . $stmt->errno ." ". $stmt->info .") " . $stmt->error);
$result = $stmt->get_result();

The error I am getting is:

Could not run the query: 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 date_start < ?) OR (date_start < ? AND date_end > ?) OR (' at line 1

When I print out the query I get this

SELECT * FROM happenings WHERE ((date_start > ? AND date_start < ?) 
  OR (date_start < ? AND date_end > ?) OR (date_end > ? AND date_end < ?)) 
  ORDER BY date_start ASC

Now it's been a while, but I can't think of where the syntax is wrong. It ran fine without binding, so is there something else I need to do? I don't believe I'm using any reserved keywords or the like. I appreciate any help!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doupeng6890 doupeng6890 2017-04-16 22:05

    For the record, the answer was guessed in the comments above.

    The code accidentally had called $mysqli->query() instead of $mysqli->prepare().

    The query() method does not recognize parameter placeholder syntax.

    点赞 评论 复制链接分享

相关推荐