Issue with LIKE/OR operator in mysqli select statement in a prepared statement

    |
  • Added:
  • |
  • In: Basic PHP

I'm creating a simple mysqli prepared statement that selects records from a database based on a search. I want to search two different columns in my database table using the same search term that was submitted to find any matches in either the heading or the content details. I was following the Accessing Databases with Object-Oriented PHP with David Powers video on Lynda.com and adapting that tutorial to fit my needs. I have most of it working, however I'm running into an issue when I add a second variable in my sql statement. The issue is that it is not returning any rows. . Here is my code:

$sql = "SELECT id, heading, details, layout FROM content WHERE ( details LIKE ? OR heading LIKE ? )"; $stmt = $db->stmt_init(); if (!$stmt->prepare($sql)) { $error = $stmt->error; } else { $stmt->bind_param('s', $var1, $var2); $var1 = '%' . $_GET['searchterm'] . '%'; $var2 = '%' . $_GET['searchterm'] . '%'; 

If I remove the part in the SQL after the first placeholder, and remove "$var2" from $stmt->bind_param('s', $var1, $var2); then the query works fine. For example, the following returns the correct results:

 $sql = "SELECT id, heading, details, layout FROM content WHERE details LIKE ?"; $stmt = $db->stmt_init(); if (!$stmt->prepare($sql)) { $error = $stmt->error; } else { $stmt->bind_param('s', $var1); $var1 = '%' . $_GET['searchterm'] . '%'; 

Once I add in the second variable, I get no results returned. I have also tried the following but that returns 0 results as well:

 $stmt->bind_param('s', $var1, $var1); $var1 = '%' . $_GET['searchterm'] . '%'; 

I am brand new to prepared statements, so any suggestions or guidance would be appreciated. Thank you.

This Question Has 1 Answeres | Orginal Question | user2762748

While I'm not very familiar with parameter binding, shouldn't you be specifying a type for each argument, i.e.

$stmt->bind_param('ss', $var1, $var2); 

Search
I am...

Sajjad Hossain

I have five years of experience in web development sector. I love to do amazing projects and share my knowledge with all.

Connect Social With PHPAns
Top