Binding to prepare statement using mysqli is working fine when I’m using query like this
if ($stmt = $dbh->prepare("SELECT url, title, fulltxt FROM links WHERE id = ? ")){// do something}
but it’s nott working when I’m using this query
if ($stmt = $dbh->prepare("SELECT url, title, fulltxt FROM links WHERE MATCH(fulltxt) AGAINST(?) ")) {//do something}
What is the right way of using MATCH() AGAINST() with a question mark in query for binding value.
Advertisement
Answer
New full code based on pastebin:
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body>
<?php
error_reporting(-1);
// here the db details
$query = "%".$_GET['q']."%";
// Connecting to the server and selecting database
$dbh = new mysqli($host, $user, $password, $database);
if ($dbh->connect_error) {
echo 'Unable to connect to database '. $dbh->connect_error;
} else {
// run query to select records
if ($stmt = $dbh->prepare("SELECT url, title, fulltxt FROM links WHERE fulltxt LIKE ?")) {
// binding user-supplier input $class to the parameter as string
$stmt->bind_param("s", $query);
// executing the statement
$stmt->execute();
// binding the table columns Name and Email to the $Name and Email parameters respectively.
$stmt->bind_result($URL, $TITLE, $FULLTXT);
/* fetch associative array */
while ($stmt->fetch()) {
echo $URL. ' -- '. $TITLE. '<br />';
}
} else {
echo "Prepare failed: (" . $dbh->errno . ") " . $dbh->error;
}
}
?>
</body>
</html>
The problem was fixed by replacing MATCH with LIKE and concatenating the variable with percentage characters on both side.