How to Bind Parameter In String mysqli PHP

Question

I am trying to bind a parameter in a LIKE statement in SQL using mysqli like so:

$title = $_POST['title'];
$author = $_POST['author'];
$isbn = $_POST['isbn'];
$q = 'SELECT * from `books`';
if (!isblank($title) || !isblank($author) || !isblank($isbn)) {
  $q .= ' WHERE 1 = 1';
}
if (!isblank($title)) {
  $q .= ' AND `title` LIKE "%?%"';
}
if (!isblank($author)) {
  $q .= ' AND `author` LIKE "%?%"';
}
if (!isblank($isbn)) {
  $q .= ' AND `isbn` = ?';
}
echo $q;
if ($statement = $db->prepare($q)) {
  if (!isblank($title) && !isblank($author) && !isblank($isbn)) {
    $statement->bind_param('sss', $title, $author, $isbn);
  } else if (!isblank($title) && !isblank($author)) {
    $statement->bind_param('ss', $title, $author);
  } else if (!isblank($title) && !isblank($isbn)) {
    $statement->bind_param('ss', $title, $isbn);
  } else if (!isblank($author) && !isblank($isbn)) {
    $statement->bind_param('ss', $author, $isbn);
  } else if (!isblank($title)) {
    $statement->bind_param('s', $title);
  } else if (!isblank($author)) {
    $statement->bind_param('s', $author);
  } else {
    $statement->bind_param('s', $isbn);
  }
  if ($statement->execute()) {
    $statement->bind_result($returned_book); // hello carlo how are you today
    while ($statement->fetch()) {
      echo var_dump($returned_book);
    }
  }
}

The problem is that mysqli will states that there are not enough parameters to bind to because the ? is in the string. If I get rid of the quotes ($q .= ' ANDtitleLIKE %?%';) the parameter binds correctly but the SQL syntax is invalid. How can I bind to this LIKE query?

This looks pretty unmaintainable. If possible you should switch to PDO; there you can add elements to an array in the if conditions where you build the query. Then you simply send that array to the execute method. No conditional binding necessary although it would be possible using named parameters and repeating the initial conditions. A possible solution here would be to remove the quotes and %'s and add the %'s to your string values when you bind. — jeroen
– jeroen, Commented Feb 2, 2016 at 14:39

deceze · Accepted Answer · 2016-02-02 14:43:07Z

3

Binding doesn't work like copy and paste replace. You need to put the ? in place of a value. The value that you're binding then must contain the %:

$q = '... LIKE ?';

...

$theValue = "%$theValue%";
$statement->bind_param('s', $theValue);

answered Feb 2, 2016 at 14:43

deceze♦

525k89 gold badges807 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mihai Matei Over a year ago

This is the correct usage.. and, of course, it should be the accepted answer

Zbynek Vyskovsky - kvr000 · Accepted Answer · 2016-02-02 14:44:32Z

0

To enclose the strings in SQL, use apostrophes instead of quotes. Quotes are not supported. In php wrap the string into quotes:

$q .= " AND `title` LIKE CONCAT('%', ?, '%')";

edited Feb 2, 2016 at 14:44

answered Feb 2, 2016 at 14:37

Zbynek Vyskovsky - kvr000

19.1k3 gold badges41 silver badges49 bronze badges

5 Comments

carloabelli Over a year ago

This still causes binding to fail because the ? is in the quotes

Zbynek Vyskovsky - kvr000 Over a year ago

@cabellicar123 : You have to fix all the occurencies, I just gave an example how to fix.

carloabelli Over a year ago

yes I did so on both lines with quotes. I do not believe mysqli binds to ? that are in quotes

jeroen Over a year ago

You shouldn't use quotes when you use a prepared statement.

Zbynek Vyskovsky - kvr000 Over a year ago

@cabellicar123 : Secondly, just actually have to do also the opposite - move ? out of the quote string. See the updated answer.

Collectives™ on Stack Overflow

How to Bind Parameter In String mysqli PHP

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related