SQL LIKE query in PHP mysqli error

A while back, Techiedan has started using mysqli in his php testing and education. Seemingly all seemed to work fine as there is no need for real_escape_string of the old mysql php syntax. There is now the new preparedstatement (it is actually quite a long time but recently caught on as more is aware of it)

PHP Logo

Though an issue arose which I would like to address. Seemingly MySQL queries like this happen sometimes.

“SELECT card_name FROM cards WHERE card_name like %some kind of string%”;

In the older php mysql we would just replace the string into the syntax. For the improved mysqli, we would use the question mark (?) instead.

“SELECT card_name FROM cards WHERE card_name LIKE %?%”;

From the syntax it should work right? Here is the initial program in php.

$con = mysqli_connect($nameIp,$nameAdmin,$namePass,$nameDataB);
$card2Name = $_REQUEST["cardName"];
$query = "select card_name from mtg_card where card_name like %?%";
$stmt = mysqli_prepare($con,$query);
$stmt->bind_param("s",$card2Name);
$stmt->execute();
$stmt->bind_result($abc);

Running this though has given the following errors though.

Fatal error: Call to a member function bind_param() on a non-object

Was there something wrong? So trying to figure it out, here was another way that TechieDan tried.

$query = "select card_name from mtg_card where card_name like ?";
$stmt = mysqli_prepare($con,$query);
$stmt->bind_param("s","%".$card2Name."%");

Another way, though after testing it produced this error.

Fatal error: Cannot pass parameter 2 by reference

Seems like it is getting error after error even though the syntax should be correct!

After going through the Internet on what certain error meant and also some play testing it looks like someone has mentioned that mysqli does not parse the query properly. We would have to use CONCAT to solve it.

$query = "select card_name from mtg_card where card_name like CONCAT('%',?,'%')";
$stmt = mysqli_prepare($con,$query);
$stmt->bind_param("s",$card2Name);
$stmt->execute();
$stmt->bind_result($abc);

Look at the syntax. What was changed was just the $query line. It seems it is the LIKE clause for mysqli should be used this way.

Leave a Reply