PHP and JSON Query to pull data from Freebase

Question

I need to pull out the info of the Production Company and Distribution Company for any given movie and I'm using the Freebase for this. Happens I never used JSON and don't know how to integrate the query with my PHP file.

The tutorials I've been reading are not at all clear on this so as always I come here to get the help I always get! :)

So... The info I need to pull out is this Freebase Query Editor

And the PHP I have is this (commented lines are the lines I'm sure that are wrong and/or lines from the tutorial I followed with no sucess here:

$moviename = "The Matrix";

// [{ "name": "The Matrix", "type": "/film/film", "production_companies": [{ "name": null }], "distributors": [{ "distributor": [{ "name": null }] }] }]
$simplequery = array('name'=>$moviename, 'type'=>"/film/film", 'production_companies'=>array('name'=>null));

//{"id":"/topic/en/philip_k_dick", "/film/writer/film":[]}
//$simplequery = array('id'=>$_POST["freebasewriter"], 'name'=>null, '/film/writer/film'=>array(array('name'=>null, 'id'=>null)));

$queryarray = array('q1'=>array('query'=>$simplequery));
$jsonquerystr = json_encode($queryarray);

#run the query
$apiendpoint = "http://sandbox.freebase.com/api/service/mqlread?queries";
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "$apiendpoint=$jsonquerystr");
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$jsonresultstr = curl_exec($ch);
curl_close($ch); 

$jsonquerystr = urlencode(json_encode($queryarray) );

//print_r($resultarray);
//$produtoraarray = $resultarray["q1"]["result"]["production_companies"];
//print_r($produtoraarray);
//$produtorarray = $resultarray["q1"]["result"][];
//$freebaseserver = "http://sandbox.freebase.com";

Can anyone give me a help here? Thanks in advance!

Appreciate it a lot and Merry Christmas by the way :)

EDIT @Yanir Shahak answer helped and now I'm getting this:

Array ( [code] => /api/status/ok [q1] => Array ( [code] => /api/status/error [messages] => Array ( [0] => Array ( [code] => /api/status/error/mql/result [info] => Array ( [count] => 3 [result] => Array ( [0] => Array ( [name] => Warner Bros. Entertainment ) [1] => Array ( [name] => Village Roadshow Pictures ) [2] => Array ( [name] => Silver Pictures ) ) ) [message] => Unique query may have at most one result. Got 3 [path] => production_companies [query] => Array ( [name] => The Matrix [production_companies] => Array ( [error_inside] => . [name] => ) [type] => /film/film ) ) ) ) [status] => 200 OK [transaction_id] => cache;cache02.sandbox.sjc1:8101;2011-12-24T02:01:34Z;0004 )

Now ... How do I take the data out of there into arrays I can use?

This code is not working as I get undefined indexes

//print_r($resultarray);
//$produtoraarray = $resultarray["q1"]["result"]["production_companies"];
//print_r($produtoraarray);
//$produtorarray = $resultarray["q1"]["result"][];
//$freebaseserver = "http://sandbox.freebase.com";

I've edited my answer to match you edit. – Yaniro Dec 26 '11 at 07:28 — Yaniro, Dec 26 '11 at 07:28

Shawn Simister · Accepted Answer · 2011-12-28T22:37:50.053

Your query is returning an error because its missing some important details. You've written:

$simplequery = array('name'=>$moviename, 
                     'type'=>"/film/film", 
                     'production_companies'=>array('name'=>null));

which corresponds to the following MQL query:

{
  "name": "The Matrix", 
  "type": "/film/film", 
  "production_companies": {
    "name": null
  }
}

This fails because there is often more than one film with a given name and there can be more than one production company involved as well. To properly handle those cases in MQL you need to wrap the production_companies property as well as the complete query in arrays to indicate that you expect multiple results for those parts of the query. In MQL that looks like this:

[{
  "name": "The Matrix", 
  "type": "/film/film", 
  "production_companies": [{
    "name": null
  }]
}]

and in your code that would translate to something like this:

$notsosimplequery = array(array('name'=>$moviename, 
                                'type'=>"/film/film",
                                'production_companies'=>array(array('name'=>null))));

You should also consider using the new MQL Read Service which is faster and has higher limits on how many queries you can make per day.

Just now I noticed I used your answer to solve my problem a month ago, without accepting it here! Done! Cheers — Afonso Gomes, Jan 31 '12 at 23:08

Yaniro · Answer 2 · 2011-12-26T07:25:59.103

All you have to do is replace:

$jsonquerystr = json_encode( $queryarray );

With:

$jsonquerystr = urlencode( json_encode( $queryarray ) );

Apparently you have to encode the special characters in the json string prior to sending them to the API server using cURL as cURL won't do it for you...

When you get your result back, it will be in the form of a JSON (JavaScript Object Notation) which is another way to represent data (attributes & values) much like XML. You will have to decode the json (notice that when you sent the query you encoded a JSON) and use the result as an object like so:

$data = json_decode( $jsonresultstr );
foreach ( $data->q1->messages[ 0 ]->info->result as $company )
{
    echo( $company->name . "<br/>" );
}

PHP and JSON Query to pull data from Freebase

2 Answers2