PHP/PDO - Binding data to prepared SQL statement breaks it

Question

I am trying to bind values to a query in PHP. I have done this successfully many times, but for some reason my code isn't working.

function get_movies($vars, $page) {

    global $db;

    $get_movies = $db->prepare('SELECT * FROM `movies` WHERE LOWER(genres) LIKE :genre AND `qualities` LIKE :quality AND `rating` >= :imdb_min AND `rating` <= :imdb_max AND `year` >= :year_min AND `year` <= :year_max ORDER BY id DESC');

    $get_movies->bindValue(':genre', $vars['genre']);
    $get_movies->bindValue(':quality', $vars['quality']);
    $get_movies->bindValue(':imdb_min', $vars['imdb_min']);
    $get_movies->bindValue(':imdb_max', $vars['imdb_max']);
    $get_movies->bindValue(':year_min', $vars['year_min']);
    $get_movies->bindValue(':year_max', $vars['year_max']);

    try {
        $get_movies->execute();
        $movies = $get_movies->fetchAll(); // list of all movies fitting parameters
        $movie_offset = ($page - 1) * VIDEOS_PER_PAGE;
        $movies = array_slice($movies, $movie_offset, VIDEOS_PER_PAGE);
        return $movies;
    } catch (Exception $e) {
        throw $e;
        return false;
    }

}

The above code does not work. No exception is thrown, but it returns 0 results. However, if I built the query manually (ex: replacing each :key with the $vars['key'] and preparing the statement from the resulting string) the query returns results perfectly fine.

Any tips would be greatly appreciated.

Edit: Here's the $vars array passed to the $get_movies function.

$vars = array(
    'genre' => "Action", 
    'quality' => 1080, 
    'imdb_min' => 0.1,
    'imdb_max' => 10.0, 
    'year_min' => 2000,
    'year_max' => 2019
);

I have added the $vars array to my initial post. The for loop goes through each value in the array and binds them to the prepared statement. — Justin G, Feb 27 '19 at 17:03
"Note that even when manually binding each individual parameter (not using the foreach and $binder variable) the code still does not work." - That is only you should have posted to provide an MCVE. — Paul Spiegel, Feb 27 '19 at 17:06
Not the issue but for your paging, have a look at limit (https://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging) — Nigel Ren, Feb 27 '19 at 17:07
Paul, that alone is not the problem at hand, the parameter binding does not work in any scenario. Even when passing the $vars via execute ($get_movies->execute($vars)) the query fails to return results. — Justin G, Feb 27 '19 at 17:08
I mean, you should post the simplest code that reproduces your problem. Everything else is distracting. — Paul Spiegel, Feb 27 '19 at 17:10
I suggest you `SET GLOBAL general_log=ON;` in your MySQL instance and you can log the actual SQL that is executed, including bound values. See my answer to https://stackoverflow.com/questions/210564/getting-raw-sql-query-string-from-pdo-prepared-statements/210693#210693 — Bill Karwin, Feb 27 '19 at 17:13
One thing to check - `bindValue()` has a 3rd optional param - `data_type` that defaults to string. I can see you have some decimal values in your `$vars`. Maybe you could be explicit about the datatype of each to see if that helps? — lumonald, Feb 27 '19 at 17:16
Remove the conditions one by one and see when it starts to work. If you remove all conditions, and it still doesn't work, then you know, it has nothing to do with the parameters. — Paul Spiegel, Feb 27 '19 at 17:40
Can you try passing those floats as strings instead? (Don't cast them though) — rtheunissen, Feb 27 '19 at 17:47

ket-c · Answer 1 · 2019-02-27T17:55:42.590

0

When I use pdo, this is how I do my query Eg

$stmt=db_conn->prepare ("SELECT * FROM table WHERE id = ? AND name = ? AND = uptime = ?");
$stmt->bindParam (1, $firstvariable);
$stmt->bindParam (2, $secondvariable);
$stmt->bindParam (3, $thirdvariable);
$stmt->execute(); 
$result=$stmt->fetchAll();

It Will bind respectfully to the blind parameters (?)

Try and ignore the movie offset and movies variables for now and fetch the results using foreach loop.

Try this & see. Hope it helps you.

edited Feb 27 '19 at 17:55

answered Feb 27 '19 at 17:25

ket-c

211
1
10

Why don't you try bindParam instead of bindValue and the see the outcome. Also you may want to print some errors to see the cause you can use echo $get_movies->error – ket-c Feb 27 '19 at 17:41
I've edited my answer with some suggestions. Check it out – ket-c Feb 27 '19 at 17:56

score 0 · Answer 2 · answered Feb 27 '19 at 18:02

0

I managed to fix the problem. When executing the query manually, the " characters surrounding strings in the LIKE comparison are interpreted as indicators that the value in between them is a string. When binding a value with " characters surrounding the string, the characters are included in the value itself. This is why no exception was thrown and the query returned 0 results. Thank you for the help.

answered Feb 27 '19 at 18:02

Justin G

172
3
19

<> - Which you forgot to include in your data sample (`$vars`). – Paul Spiegel Feb 27 '19 at 18:10

PHP/PDO - Binding data to prepared SQL statement breaks it

2 Answers2