PHP Google Places API Example

So, recently while working on a project I needed to gather large amounts of businesses in a particular city in the United States. A lot of websites out there offer databases or comma delimited files containing this data, but I wanted it for free. Fortunately for me, Google has an API that has all the data we need and more. It has business name, address, phone number, website, rating, and some even have reviews we can parse. Google limits API users to 1 thousand queries per day unless you pay for more. Seeing as I want my website to allow more then 1k searches per day, I was faced with writing a script that would parse the places, check if they exist in our database, and if not, add them. That way every visitor or person searching my site would be querying our local MySQL database and not hitting the Google API using my 1k limited API key. So with this method, I can pull 1k listings into my database everyday. To get more places into my database daily, of course I can create multiple API key’s.

Now, the Google places API has limited data but it will return a reference number that we can use as a relationship to the Google Place Details table or API if you will. So once we decide that a listing is not in our database, we will grab the data we can get from the Places API and then use the reference number to hit the Details API for more information on that place.

The way the script is written requires 3 MySQL tables. We will also want to store categories, and reviews so I have created tables for them and so we can use the Google reference in our internal database as our own relationships. Whats really nice is that you can put this script in a CRON job and have it run daily on all the categories you want and then your database will continue to grow automatically as listings make it to the Google API. Another good option would be to make it so that the websites search script hits the Google API and verifies all of your local results to the Google API to make sure users are seeing the most up to date information.

MYSQL:

CREATE TABLE IF NOT EXISTS `ffx_items` (
`item_id` int(255) NOT NULL AUTO_INCREMENT,
`item_name` varchar(255) NOT NULL,
`item_type` varchar(255) NOT NULL,
`item_categories` varchar(255) NOT NULL,
`item_likes` int(255) NOT NULL,
`item_dislikes` int(255) NOT NULL,
`item_rating` int(255) NOT NULL,
`item_photos` varchar(255) NOT NULL,
`item_text` text NOT NULL,
`item_url` varchar(255) NOT NULL,
`google_id` varchar(255) NOT NULL,
`discovered_by` varchar(255) NOT NULL,
`item_address` varchar(255) NOT NULL,
`item_phone` varchar(255) NOT NULL,
`item_website` varchar(255) NOT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1407 ;

CREATE TABLE IF NOT EXISTS `ffx_categories` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`category` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=162 ;

CREATE TABLE IF NOT EXISTS `ffx_item_reviews` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`reviewer_name` varchar(255) NOT NULL,
`reviewer_rating` int(255) NOT NULL,
`review_text` text NOT NULL,
`google_reference` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1853 ;

PHP SCRIPT:

Key variables:

$query = $_GET['query']; -> This is what you are searching for and should be a $_GET from your URL(IE: http://www.yoursite.com/gather.php?query=Los+Angeles+Cafe)
$type = $_GET['type']; -> This can be used for a specific category to narrow results.
$key = “”; -> this needs to be your API key from google.
$db_user = “”; -> database username
$db_pass = “”; -> database password
$db_db = “”; -> your database name


<!--?php //------------------------------------------------------------------------------------------------------------------>
//
// gather.php - hit the google places api and pull in data to our local mysql database.
//
// coded by: kevin hansen @ techmeout.tv
//
// variables:
//
//---------------------------------------------------------------------------------------------------------------->

function seoUrl($string) {

$string = strtolower($string);

$string = preg_replace("/[^a-z0-9_s-]/", "", $string);

$string = preg_replace("/[s-]+/", " ", $string);

$string = preg_replace("/[s_]/", "-", $string);

return $string;
}

$query = $_GET['query'];
$type = $_GET['type'];
$key = "";
$db_server = "localhost";
$db_user = "";
$db_pass = "";
$db_db = "";

$xml = new SimpleXMLElement("https://maps.googleapis.com/maps/api/place/textsearch/xml?query=".$query."&type=".$type."&sensor=true&key=".$key.", null, true);

if($xml->status == 'OK') {

mysql_connect($db_server,$db_user,$db_password);
mysql_select_db($db_db);

foreach($xml->result as $place) {

$name = $place->name;
$type = $place->type[0]. "," .$place->type[1] . "," . $place->type[2];
$address = $place->formatted_address;
$googleid = $place->id;
$rating = $place->rating;
$reference = $place->reference;

foreach($place->type as $category) {
$sql = "SELECT * FROM `ffx_categories` WHERE `cat` = '".$category."'";
$q = mysql_query($sql);
$return = mysql_num_rows($q);
if($return == 0) {
mysql_query("INSERT INTO `fairfaxy_db`.`ffx_categories` (`id`, `category`) VALUES (NULL, '".$category."')");
}
}

$sql = "SELECT * FROM `ffx_items` WHERE `item_name` LIKE '%".mysql_escape_string($name)."%'";
$q = mysql_query($sql);
$r = mysql_num_rows($q);

if($r < 1) { echo "NAME: " . $place->name . "
";
echo "TYPE: " . $place->type[0]. "," .$place->type[1] . "," . $place->type[2] . "
";
echo "ADDRESS: " . $place->formatted_address . "
";
echo "GOOGLE ID: " . $place->id . "
";
echo "GOOGLE REFERENCE: " . $place->reference . "
";
echo "RATING: " . $place->rating . "
";

//use the reference id to now pull more data from the google place details api
$xml2 = new SimpleXMLElement("https://maps.googleapis.com/maps/api/place/details/xml?reference=".$reference."&sensor=true&key=".$key.",null,true);

echo "PHONE: " . $xml2->result->formatted_phone_number. "
";
echo "WEBSITE: " . $xml2->result->website . "
";
echo "REVIEWS:
";
foreach($xml2->result->review as $review) {
echo "
";
echo "<strong>" . $review->author_name . "</strong>
<strong>Rating: " .$review->aspect->rating. "</strong>
";
echo $review->text . "

<hr />

";
mysql_query("INSERT INTO `ffx_item_reviews` (`id`, `reviewer_name`, `reviewer_rating`, `review_text`, `google_reference`) VALUES(NULL, '".mysql_escape_string($review->author_name)."','".$review->aspect->rating."','".$review->text."', '".$place->reference."')");

}
echo "

";

mysql_query("INSERT INTO `ffx_items` (`item_website`, `item_address`, `item_phone`, `item_id`, `item_name`, `item_type`, `item_categories`, `item_likes`, `item_dislikes`, `item_rating`, `item_photos`, `item_text`, `item_url`, `google_id`, `discovered_by`) VALUES('".$xml2->result->website."', '".$address."','".$xml2->result->formatted_phone_number."', NULL, '".mysql_escape_string($name)."','DIR','".$type."','0','0','".$rating."','','".$name."
".$address."
".$xml2->result->formatted_phone_number."
".$xml2->result->website."','".seoURL($name)."','".$googleid."','GOOGLE_API')");

}
}

mysql_close();

} else {

echo "FAILED TO PARSE -> " . $xml->status;

}

?>

9 Responses to PHP Google Places API Example

  1. Thanks for posting your script. I’d love to get it working. The mysql tables were created fine but I’m getting errors trying to run the php script.

    Is it possible that the box above strips out some characters and thus corrupts the script? Is there are way to attach a clean version to this page for download?

    Thanks. — Roger

  2. Roger, I am getting following error:
    ———–
    Parse error: syntax error, unexpected ‘SELECT’ (T_STRING) in D:\xampp\htdocs\applancers\index.php on line 50
    ———–

    It’s for following line in your PHP script:
    ———–
    $sql = “SELECT * FROM `ffx_categories` WHERE `cat` = ‘”.$category.”‘”;
    ———–

    • Santosh,

      There is nothing incorrect about the syntax.

      See below to verify as I have rewritten it.

      $sql = “SELECT * FROM `ffx_categories` WHERE `cat` = ‘”.$category.”‘”;

      Keep in mind that if you copied and pasted out of the blog there could be some syntax problems when you pasted it.

      Please rewrite the line to see if it helps.

  3. Hi, Im problem, the script save the parameters into my data base, put dont show when search it

    FAILED TO PARSE -> OVER_QUERY_LIMIT

    • Hi Carlos,

      I am not sure what you mean. Can you clarify this please so I can assist?

      Thanks

      • Hello, my problem is that the query limit is google api 1000 queries a day. and when it reaches this limit no data above shows my database, saving it, but it shows

        You can check in http://www.pruebastic.com/maps/maps.php

        Mi code:

        Untitled Document

        status == ‘OK’) {

        foreach($xml->result as $place) {

        $name = $place->name;
        $types = $place->types[0]. “,” .$place->types[1] . “,” . $place->types[2];
        $address = $place->formatted_address;
        $googleid = $place->id;
        $rating = $place->rating;
        $reference = $place->reference;

        foreach($place->types as $category) {
        $sql = “SELECT * FROM ffx_categories WHERE category = ‘”.$category.”‘”;
        $q = mysql_query($sql);
        $return = mysql_num_rows($q);
        if($return == 0) {
        mysql_query(“INSERT INTO ffx_categories (id, category) VALUES (NULL, ‘”.$category.”‘)”);
        }

        }

        $sql = “SELECT * FROM ffx_items WHERE item_name LIKE ‘%”.mysql_escape_string($name).”%’”;
        $q = mysql_query($sql);
        $r = mysql_num_rows($q);

        if($r name . ”
        “;
        echo “types: ” . $place->types[0]. “,” .$place->types[1] . “,” . $place->types[2] . ”
        “;
        echo “ADDRESS: ” . $place->formatted_address . ”
        “;
        echo “GOOGLE ID: ” . $place->id . ”
        “;
        echo “GOOGLE REFERENCE: ” . $place->reference . ”
        “;
        echo “RATING: ” . $place->rating . ”
        “;

        //use the reference id to now pull more data from the google place details api
        $xml2 = new SimpleXMLElement(“https://maps.googleapis.com/maps/api/place/details/xml?reference=”.$reference.”&sensor=true&key=”.$key.””,null,true);

        echo “PHONE: ” . $xml2->result->formatted_phone_number. ”
        “;
        echo “WEBSITE: ” . $xml2->result->website . ”
        “;
        echo “REVIEWS:
        “;
        foreach($xml2->result->review as $review) {
        echo ”
        “;
        echo “” . $review->author_name . “
        Rating: ” .$review->aspect->rating. “
        “;
        echo $review->text . ”

        “;
        mysql_query(“INSERT INTO ffx_item_reviews (id, reviewer_name, reviewer_rating, review_text, google_reference) VALUES(NULL, ‘”.mysql_escape_string($review->author_name).”‘,’”.$review->aspect->rating.”‘,’”.$review->text.”‘, ‘”.$place->reference.”‘)”);

        }
        echo ”

        “;

        mysql_query(“INSERT INTO ffx_items (item_website, item_address, item_phone, item_id, item_name, item_types, item_categories, item_likes, item_dislikes, item_rating, item_photos, item_text, item_url, google_id, discovered_by) VALUES(‘”.$xml2->result->website.”‘, ‘”.$address.”‘,’”.$xml2->result->formatted_phone_number.”‘, NULL, ‘”.mysql_escape_string($name).”‘,’DIR’,’”.$types.”‘,’0′,’0′,’”.$rating.”‘,”,’”.$name.”
        “.$address.”
        “.$xml2->result->formatted_phone_number.”
        “.$xml2->result->website.”‘,’”.seoURL($name).”‘,’”.$googleid.”‘,’GOOGLE_API’)”);

        }
        }

        }

        else {

        echo “FAILED TO PARSE -> ” . $xml->status;

        }

        mysql_close();
        ?>

        • the xml is shown right, save the data in my database, but when the api key reaches its limit, not above shows the results from my database. Show echo “FAILED TO PARSE -> ” . $xml->status;

  4. At first I got the same “Parse error: syntax error, unexpected ‘SELECT’ (T_STRING)” error but resolved this easily.

    Now however I am getting the following:

    Warning: SimpleXMLElement::__construct() [simplexmlelement.--construct]: Entity: line 1: parser error : Start tag expected, ‘<' not found in /home/Dir/Code.php on line 40

    Here's that specific line:

    $xml = new SimpleXMLElement("https://maps.googleapis.com/maps/api/place/textsearch/xml?query=&quot;.$query."&sensor=false&key=".$key.", null, true");

    Any thoughts…?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>