0

I'm fairly new to writing programs in PHP, and at the moment, I'm trying to search a database of book titles, based on whether the title contains one word of the search. For example, a search of

Harry Potter Secrets

Will return the following list in order

Harry Potter and the Chamber of Secrets (all three words match)

Harry Potter and the Philosopher's Stone (two words match)

... (all Harry Potter books)

The Secret Garden (one word match)

I've managed to get this working by typing the SQL directly into the database, but I'm trying to devise a lookup function. However, when I came to bind the parameters, using mysqli_stmt_bind_param, the function only accepts entries in the form:

(statement, string types, value1, value2, value3, ...)

I've tried:

mysqli_prepare($con, “SELECT bookTitle FROM books WHERE bookTitle = ? or bookTitle = ? or bookTitle = ? ….”)

#I can add the repetitions of 'bookTitle = ?' in a for loop


mysqli_stmt_bind_param ( ‘ssss’ , $book_titles)

#Where the number of s' is dynamically added and $book_titles is an array

I've noticed that using the object-orientated version allows an array of all values to be passed, but I don't fancy rewriting all of my code again, so I'm going to have to use procedural

My current (hopefully temporary) solution is to not use prepared statements:

function search($query) {
    global $con;
            
    $words = preg_split('/\s+/', $query);
    
    $partOne = "SELECT `bookTitle` FROM books WHERE ";
    $partTwo = " ORDER BY ";
    
    foreach ($words as $word) {
        $cleanWord = cleanUp($word);
        
        $partOne .= 'LOWER(bookTitle) LIKE "%' . $cleanWord . '%"';
        $partTwo .= "SIGN(LOCATE('" . $cleanWord . "', LOWER(recipeName)))";
        
        if (array_search($word, $words) != count($words) - 1) {
            $partOne .= " OR ";
            $partTwo .= " + ";
        }
    }
    
    $statement = $partOne . $partTwo . " DESC";
    
    $result = mysqli_query($con, $statement);
    
    while ($row = mysqli_fetch_assoc($result)) {
        #$row["bookTitle"] is the book title
    }
        
    mysqli_free_result($result);
}

Is there a way of achieving this by using prepared statements instead?

Thank you!

P.S - I've seem other questions answered using the function

call_user_func_array

But unfortunately, all such solutions seem to use objects, rather than procedural

Community
  • 1
  • 1
Oliver
  • 121
  • 6
  • Sure there is a duplicate which shows how to do array unpacking `( ‘ssss’ , ...$book_titles)` – Nigel Ren Jan 19 '20 at 19:03
  • TBH, you want to make the changeover to objects ASAP. A problem such as you’ve just encountered would be a relatively easy fix if you used a wrapper object around your database object. All your code would reference methods of the wrapper and would know nothing about *how* the connection is made to the database. Therefore none of your code would have to be changed; you would just inject a different wrapper in your code. – Tim Morton Jan 19 '20 at 20:17

0 Answers0