Building a Dynamic Prepared statement for searching

Question

How can I alter this statement to add another search parameter such as " AND username LIKE ? " ?

Currently, searching for something on my website gives the expected results beautifully, but I'd like to search within the listing usernames too, not just the listing titles. This is the part that's giving me trouble.

The code is rather complex but I'll try to minimize the code in question only:

    $fetch_paginated_listings = "SELECT * FROM table WHERE end_date >= CURDATE() ";
    $search_param = $_GET['search'];
    $search_param = (strlen($search_param) > 175) ? substr($search_param,0,175) : $search_param; //Limit the uesr input. If users fill up the search bar, they can crash the db!

    /*Following code is to dynamically populate a prepared statement. Reference: https://www.pontikis.net/blog/dynamically-bind_param-array-mysqli*/
    $a_bind_params = explode(' ', $search_param);
    $a_param_type = array();
    foreach ($a_bind_params as $item) {
      array_push($a_param_type, 's');
      $fetch_paginated_listings .= " AND l_title LIKE ? "; //here, try to add this: " AND l_username LIKE ? " and array_push another 's' to $a_param_type
    }
    /* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
    $a_params = array();
    $param_type = '';
    $n = count($a_param_type);
    for($i = 0; $i < $n; $i++) {
      $param_type .= $a_param_type[$i];
    }
    /* with call_user_func_array, array params must be passed by reference */
    $a_params[] = & $param_type;
    for($i = 0; $i < $n; $i++) {
      /* with call_user_func_array, array params must be passed by reference */
      $a_bind_params[$i] = "%".$a_bind_params[$i]."%";
      $a_params[] = & $a_bind_params[$i];
    }

    $fetch_paginated_listings .= ' ORDER BY l_title ';    
    $fetch_paginated_listings .= ' 
    LIMIT
        ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '';

    $stmt_fetch_paginated_listings = $conn->prepare($fetch_paginated_listings);
    if($stmt_fetch_paginated_listings === false) {
      trigger_error('Wrong SQL: ' . $fetch_paginated_listings . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
    }

    /* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
    call_user_func_array(array($stmt_fetch_paginated_listings, 'bind_param'), $a_params);

    $stmt_fetch_paginated_listings->execute();

My attempts so far:

    foreach ($a_bind_params as $item) {
      array_push($a_param_type, 's');
      array_push($a_param_type, 's');
      $fetch_paginated_listings .= " AND l_title LIKE ? ";
      $fetch_paginated_listings .= " AND l_username LIKE ? ";
    }

^ The error it gives is "Undefined offset 3, Undefined offset 4, Undefined offset 5" perhaps because I cannot find the correct place to add the extra fields.

Lame attempt, I know, but I'm stumped. How should I do it?

EDIT:

Let's say a user types in "foo bar" in the search input field, the words "foo" and "bar" are then placed in an array with a length of 2.

1 - The query checks a column called 'title' to see if there are any matches,

2 - It also checks a column called 'username' to see if there are any matches

3 - The query is done using prepared statements for security purposes

4 - In order to achieve this type of statement (varied amount of array strings) you have to build a dynamic multiple keyword prepared statement. I used this as reference: https://www.pontikis.net/blog/dynamically-bind_param-array-mysqli

5 - I've achieved the first part of the query i.e. the query is successful for a single placeholder

6 - I'm stuck trying to search the username column, since it requires another placeholder

7 - When a user searches for "foo bar" the query should end up like this, but with placeholders: SELECT * FROM table WHERE title LIKE %foo% AND title LIKE %bar% AND username LIKE %foo% AND username LIKE %bar%

Answer 1

You were on the right track as far as you went. What you missed is how to get the right number of bind parameters. $a_bind_params has enough parameters for the title, but when you add username to it, it has to be doubled. i.e., if $a_bind_params = ['bottle','soda'], your new array needs to be ['bottle','soda','bottle','soda'] or ['bottle','bottle','soda','soda']

Answer 2

As @TimMorton's answer states, these are the edits I done (hacky, but works!)

foreach ($a_bind_params as $item) {
      array_push($a_param_type, 'ss'); //added another s
      $fetch_paginated_listings .= " AND l_title LIKE ? AND l_username LIKE ? "; //added the second parameter
    }

Then in the for loop:

for($i = 0; $i < $n; $i++) {
      $a_bind_params[$i] = "%".$a_bind_params[$i]."%";
      $a_params[] = & $a_bind_params[$i];
      $a_params[] = & $a_bind_params[$i]; //added this line
    }

And it works :)