Summary
I'm running a MySQL query using PHP and PDO. Sometimes, it returns the results I expect. Other times, it returns exactly nothing.
Solution
Since this is apparently a duplicate of a completely different question, I'll throw the correct answer here.
The immediate problem is in the json_encode function. From this SO answer, using echo json_last_error_msg() right after the encode function gives the error "Malformed UTF-8 characters, possibly incorrectly encoded", which hints at the underlying problem.
Note that there doesn't seem to be a way to automatically output these errors. It's not an issue with the PHP server's error reporting, but simple ignorance of how to check the JSON error logs, which must be done manually.
Another useful trick is the JSON_PARTIAL_OUTPUT_ON_ERROR option, such as echo json_encode($array, JSON_PARTIAL_OUTPUT_ON_ERROR);. This gives data for most things, while converting bad values to NULL silently. It's not ideal, but at least helps narrow the problematic data down.
Other answers on the linked SO page also indicate that the wrong character set may be the culprit, but unfortunately all the answers are for older connection methods, not PDO.
Fortunately, the correct answer is available on the PHP PDO::__construct page, as a comment from the user Kiipa at live dot com:
To get UTF-8 charset you can specify that in the DSN.
$link = new PDO("mysql:host=localhost;dbname=DB;charset=UTF8");
Putting the bolded charset=UTF8 in the DSN string fixes all the problems.
Setup
I'm using XAMPP 3.2.2 with Apache 2.4.23 (Win32) / OpenSSL 1.0.2h / PHP 5.6.24 for a webserver and MariaDB 10.1.16 for a database server. I'm testing using localhost on Firefox 50.0.
The Problem
I've got a webpage that uses AJAX to query a database based on user input. The user can type in a first name, last name, and movie title, and the MySQL query returns a list of character roles matching the search results. It works reasonably well, but I found some weird limitations.
If I type in all empty fields, it will find the first 50 results (or whatever number I use for a limit). If I type in the first letter or two of a title, it returns nothing. If I type in a third or fourth letter, it returns meaningful results again. If I set the row limit to 5 or so, it suddenly works with two-letter searches. Removing everything in the query concerning the join with the actor table makes it work.
Generally, it seems like anything that would increase execution time causes the query to fail, but the server has a 30-second execution window before it times out, and these queries are taking like half a second. I've also successfully run queries that took 5 to 10 seconds to run.
The PHP Code
I've created a dummy file, index.php, for testing without the HTML, Javascript, etc. It should echo a JSON-encoded array. Sometimes it does, sometimes there's just an empty page.
<?php
// These would normally be set using POST variables and AJAX.
// Hard-code them for this example.
$title = "I";
$first = "Ki";
$last = "D";
$rowLimit = 50;
searchMovies($title, $first, $last, $rowLimit);
function searchMovies($title, $first, $last, $rowLimit)
{
try
{
// Connect to the database.
$db = "mysql:dbname=imdb;host=127.0.0.1";
$user = "root";
$password = "";
$DB = new PDO($db, $user, $password);
$DB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Add wildcards for searching.
$title = $title . "%";
$first = $first . "%";
$last = $last . "%";
// Setup the query.
$query = "SELECT mov.name, CONCAT(act.first_name, ' ', act.last_name) as actor, mov.year
FROM roles
INNER JOIN
(
SELECT id, name, year
FROM movies
WHERE name LIKE :title
) mov
ON roles.movie_id = mov.id
INNER JOIN
(
SELECT id, first_name, last_name
FROM actors
WHERE first_name LIKE :first
AND last_name LIKE :last
) act
ON roles.actor_id = act.id
ORDER BY mov.year DESC, mov.name ASC
LIMIT :limit";
// Prepare and execute.
$stmt = $DB->prepare($query);
$stmt->bindParam(":title", $title, PDO::PARAM_STR);
$stmt->bindParam(":first", $first, PDO::PARAM_STR);
$stmt->bindParam(":last", $last, PDO::PARAM_STR);
$stmt->bindParam(":limit", $rowLimit, PDO::PARAM_INT);
$stmt->execute();
$array = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Just echo the array to the page.
echo json_encode($array);
}
catch (PDOException $ex)
{
// Oh noes!
echo $ex->getMessage();
}
}
?>
The MySQL Setup
The database I'm using can be found here. It's the IMDB full version, about 50 MB size. There's also a weird issue where you have to create a database to import it into, instead of the .sql file creating one for you. I named my database 'imdb' to work properly with my PHP code.
The tables in question are:
movies: id (int11, PK, NN), name (varchar100), year (int11), rank (float)
actors: id (int11, PK, NN), first_name (varchar100), last_name (varchar100), gender (char1), film_count (int11)
roles: actor_id (int11), movie_id (int11), role (varchar100)
I'm querying the roles table, then joining it with the movies and actors tables so I can display the actor name, movie name, and release year of the role in question. Further, I'm filtering the movies and actors tables to only display a role if its movie's name matches the user input and if its actor's first and last names match the user input.
A Specific Example
There's a record in the roles table with actor_id=608478, movie_id=161108, role='Claudia'. It matches the actors record with actor_id=608478, first_name='Kirsten', last_name='Dunst', gender='F', film_count=57. And it matches the movie record with movie_id=161108, name='Interview with the Vampire: The Vampire Chronicles', year=1994, rank=7.1.
I combined first and last names into a single actor field and ignored most of the fields, leaving me with something that looks like name='Interview with the Vampire: The Vampire Chronicles', actor='Kirsten Dunst', year=1994.
Searching for $title="Inter", $first="Kir", $last="D" should return this record (it does).
Searching for $title="I", $first="Ki", $last="D" should return this record plus 23 others (for example, King Donovan's role as Jack Belicec in the 1956 version of Invasion of the Body Snatchers shows up); instead, it returns nothing.
Looking in my MySQL general log, I see that the server executed the statement:
SELECT mov.name, CONCAT(act.first_name, ' ', act.last_name) as actor, mov.year
FROM roles
INNER JOIN
(
SELECT id, name, year
FROM movies
WHERE name LIKE 'I%'
) mov
ON roles.movie_id = mov.id
INNER JOIN
(
SELECT id, first_name, last_name
FROM actors
WHERE first_name LIKE 'Ki%'
AND last_name LIKE 'D%'
) act
ON roles.actor_id = act.id
ORDER BY mov.year DESC, mov.name ASC
LIMIT 50
Which returns the 24 rows I would expect without issue when I run the statement from a MySQL Workbench query window.
Other Attempted Fixes
1. Setting the DB connection string to "localhost" vs. "127.0.0.1" makes no apparent difference.
2. Concatenating the PHP variables directly into the $query string makes no difference.
3. Yelling at the computer causes the dog to look at me funny, but doesn't help the problem at hand.
4. Changing "INNER JOIN" to "JOIN" makes no difference.
5. Changing to "LEFT JOIN" breaks it. "RIGHT JOIN" is obviously not right.
6. Removing the "LIMIT" clause makes no difference, except the obvious.
