CODEWITHSUNDEEP
phpmysqljsonmysqliextract
user4047384
user4047384

Reputation:

mysql JSON_EXTRACT - don't return any results

I create a simple search form. Firstly I explode words and make some query (for search multiple words at once). Also, I want to search only in Movie titles. Movie titles can be found in movie_data.

My database called movies have rows: id and movie_data - contains movie data in JSON. Example storaged data in json: 

{"movie_title":"Forrest Gump","movie_cover":"http://1.fwcdn.pl/po/09/98/998/7314731.6.jpg","movie_name_en":"","movie_desc":"Historia życia Forresta, chłopca o niskim ilorazie inteligencji z niedowładem kończyn, który staje się miliarderem i bohaterem wojny w Wietnamie.","movie_year":"1994","movie_genres":"DramatKomedia"}

Here is my code. This code don't return any results. 

$query = 'Forrest Gump';
$words = explode(' ', $query);

$i = 0;
$len = count($words);
$query_build = '';
foreach ($words as $item) {
    if ($i == $len - 1) {
       $query_build .= ' titlemov LIKE "%$item%"';
    } else {
       $query_build .= ' titlemov LIKE "%$item%" OR ';
    }
    // …
    $i++;
}


$sql = "SELECT movie_data, JSON_EXTRACT(movie_data, '$.movie_title') AS titlemov 
FROM movies WHERE $query_build";

This code don't result any items

UPDATE I updated my code. 

$query = 'Forrest Gump';
$words = explode(' ', $query);

$i = 0;
$len = count($words);
$query_build = '';
foreach ($words as $item) {
    if ($i == $len - 1) {
       $query_build .= " JSON_EXTRACT(movie_data, \'$.movie_title\') LIKE '%$item%'";
    } else {
       $query_build .= " JSON_EXTRACT(movie_data, \'$.movie_title\') LIKE '%$item%' OR ";
    }
    // …
    $i++;
}


$sql = "SELECT * FROM movies WHERE $query_build";

Now i getting error:

Warning: mysqli::query(): (22032/3141): Invalid JSON text in argument 1 to function json_extract: "Missing a comma or '}' after an object member." at position 228. in /search.php on line 43

Line 43: 

$result = $conn->query($sql);

var_dump of $sql var:

SELECT * FROM movies WHERE JSON_EXTRACT(movie_data, '$.movie_title') LIKE '%Forrest%' OR JSON_EXTRACT(movie_data, '$.movie_title') LIKE '%Gump%'

Hmm?

Upvotes: 0

Views: 2832

Answers (1)

Ray
Ray

Reputation: 41508

titlemov doesn't exist for your where clause to use. What you want to use inside your where clause (specified in the $query_build) is the JSON_SEARCH function. Here's what it looks like in general with wildcards for the movie name:

SELECT movie_data, movie_data->'$.movie_title' AS titlemov FROM movies 
 WHERE  JSON_SEARCH(movie_data->'$.movie_title', 'one', "%orrest Gum%") IS NOT NULL;

You'll just need to modify your $query_build string. I also drop the needless JSON_EXTRACT function calls.

Try this:

$query_build = " JSON_SEARCH(movie_data->'$.movie_title', 'one', '%$item%') IS NOT NULL";

Final note, I'm assuming $item is not a user supplied string, otherwise this opens you up to SQL injection. If it is supplied by user, you'll want to use a prepared statement and bind this value.

Upvotes: 1

Related Questions