CODEWITHSUNDEEP
phpmysqlmysqli
MackDaddy
MackDaddy

Reputation: 601

MySQLi syntax error when binding parameters

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!

Upvotes: 0

Views: 106

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562250

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.

Upvotes: 1

Related Questions