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
Meet with owner

Sajjad Hossain

Hey, I am Sajjad, working in web development sector since 2012. I love to do amazing things. Let's do a project together.
Connect Social With PHPAns
Top