Executing a prepared PDO statement with the like clause

Answer 1

$stmt->execute(array($searchtype, '\'%'.$searchterm.'%\'')); 

This isn't how parameterised queries work. Inserted parameters act as literal strings already, you don't have to add quote delimiters around them or escape them (that's the whole point), and if you try, you're literally comparing against the string single-quote-searchterm-single-quote.

Consequently if you are (as I suspect) intending to compare a particular column against a literal string, you don't parameterise the column name. At the moment you are comparing a literal string to another literal string, so it'll either always be true or always false regardless of the data in the row!

So I think what you probably mean is:

$query= "SELECT * FROM books WHERE $searchtype LIKE ?";
$like= "%$searchterm%";
$stmt->execute(array($like)); 

thought naturally you will have to be very careful that $searchtype is known-good to avoid SQL-injection. Typically you would compare it against a list of acceptable column names before using it.

(Aside: there is a way of putting arbitrary strings in a schema name that you can use for a column, but it's annoying, varies across databases and there isn't a standard escaping function for it. In MySQL, you backslash-escape the backquote character, quotes and backslashes and surround the name with backquotes. In ANSI SQL you use double-quotes with doubled-double-quotes inside. In SQL Server you use square brackets. However in reality you vary rarely need to do any of this because really you only ever want to allow a few predefined column names.)

(Another aside: if you want to be able to allow $searchterm values with literal percents, underlines or backslashes in—so users can search for “100%” without matching any string with 100 in—you have to use an explicit escape character, which is a bit tedious:)

$query= "SELECT * FROM books WHERE $searchtype LIKE ? ESCAPE '+'";
$like= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $searchterm);
$stmt->execute(array("%$like%")); 

Answer 2

Don't add the quotes when binding prepared variables and dont bind the column name

$query = sprintf( "select * from books where %s like ?", $searchtype );
...
$stmt->execute(array($searchtype, '%'.$searchterm.'%'));