0

I'm trying to save my Foursquare checkins into a MySQL database with PDO. For some reason, this PDO insert code does not work in this loop. I get no errors and no rows added to the table ...

Let's say I have an object that I grabbed from the Foursquare API:

$checkins = $users->response->checkins->items;

And I'm trying to do this with it:

try {

    $dbh = new PDO('mysql:host=localhost;dbname=nick', 'root', 'root'); 

    foreach( array_reverse($checkins) as $checkin ) {

        $sql = 'INSERT INTO foursquare (foursquare_id, checkin_date, venue_id, venue_name, venue_address, venue_city, venue_state, 
        venue_country, venue_zip, venue_lat, venue_lng, venue_category, icon, venue_url, been_here, shout) 

        VALUES (:foursquare_id, :checkin_date, :venue_id, :venue_name, :venue_address, :venue_city, :venue_state, 
        :venue_country, :venue_zip, :venue_lat, :venue_lng, :venue_category, :icon, :venue_url, :been_here, :shout)';

        $stmt = $dbh->prepare($sql);

        $stmt->execute(array(
            ':foursquare_id' => $checkin->id,
            ':checkin_date' => $checkin->createdAt,
            ':venue_id' => $checkin->venue->id,
            ':venue_name' => $checkin->venue->name,
            ':venue_address' => $checkin->venue->location->address,
            ':venue_city' => $checkin->venue->location->city,
            ':venue_state' => $checkin->location->state,
            ':venue_country' => $checkin->venue->country,
            ':venue_zip' => $checkin->venue->postalCode,
            ':venue_lat' => $checkin->venue->lat,
            ':venue_lng' => $checkin->venue->lng,
            ':venue_category' => $checkin->venue->categories[0]->name,
            ':icon' => $checkin->venue->categories[0]->icon->prefix.'32'.$checkin->venue->categories[0]->icon->suffix,
            ':venue_url' => $checkin->venue->url,
            ':been_here' => $checkin->venue->beenHere->count,
            ':shout' => $checkin->shout
            )
        );

        echo "<ul>";
        echo "<li>". $checkin->id . "</li>";
        echo "<li>". $checkin->createdAt . "</li>";
        echo "<li>". $checkin->venue->id . "</li>";
        echo "<li>". $checkin->venue->name . "</li>";
        echo "<li>". $checkin->venue->location->address . "</li>";
        echo "<li>". $checkin->venue->location->city . "</li>";
        echo "<li>". $checkin->venue->location->state . "</li>";
        echo "<li>". $checkin->venue->location->country . "</li>";
        echo "<li>". $checkin->venue->location->postalCode . "</li>";
        echo "<li>". $checkin->venue->location->lat . "</li>";
        echo "<li>". $checkin->venue->location->lng . "</li>";
        echo "<li>". $checkin->venue->categories[0]->name . "</li>";
        echo "<li>". $checkin->venue->categories[0]->icon->prefix.'32'.$checkin->venue->categories[0]->icon->suffix ."</li>";
        echo "<li>". $checkin->venue->url . "</li>";
        echo "<li>". $checkin->venue->beenHere->count . "</li>";
        echo "<li>". $checkin->shout . "</li>";
        echo "</ul><hr/>";

        echo $db->lastInsertId();

    }

} catch(PDOException $e) {

    echo $e->getMessage();

}

All of the values echo out fine in the unordered list. And what I don't understand is that if I try using the same code by itself, not in a loop, it inserts the values fine. Like this:

$dbh = new PDO('mysql:host=localhost;dbname=nick', 'root', 'root');

$sql = 'INSERT INTO foursquare (foursquare_id, checkin_date, venue_id, venue_name, venue_address, venue_city, venue_state, 
venue_country, venue_zip, venue_lat, venue_lng, venue_category, icon, venue_url, been_here, shout) 

VALUES (:foursquare_id, :datetime, :venue_id, :venue_name, :venue_address, :venue_city, :venue_state, 
:venue_country, :venue_zip, :venue_lat, :venue_lng, :venue_category, :icon, :venue_url, :been_here, :shout)';

$stmt->execute(array(
    ':foursquare_id' => '537834c8498e6ddd6b5cd649',
    ':datetime' => '1400386760',
    ':venue_id' => '50b55017e4b057a50b302ec0',
    ':venue_name' => 'ViewHouse Eatery, Bar & Rooftop',
    ':venue_address' => '2015 Market St',
    ':venue_city' => 'Denver',
    ':venue_state' => 'CO',
    ':venue_country' => 'United States',
    ':venue_zip' => '80205',
    ':venue_lat' => '39.7540584831',
    ':venue_lng' => '-104.993300193',
    ':venue_category' => 'American Restaurant',
    ':icon' => 'https://ss1.4sqi.net/img/categories_v2/food/default_32.png',
    ':venue_url' => 'http://www.viewhouse.com',
    ':been_here' => '1',
    ':shout' => ''
    )
);

Any clues to what I'm doing wrong here?

Thanks!

Nick
  • 417
  • 1
  • 4
  • 14
  • 1
    You're possibly catching connection errors in PDO, but by default it won't throw meaningful errors in regular usage after connecting. See [How to squeeze an error message out of PDO](http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo) and use `$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` – Michael Berkowski May 20 '14 at 23:43
  • If a runtime error is encountered after connecting, PDO will then throw an exception which you can inspect. – Michael Berkowski May 20 '14 at 23:43
  • 1
    I also see you've used `$db->lastInsertId()` but your variable is `$dbh` not `$db`. Turn on error reporting and display errors on screen. `error_reporting(E_ALL); ini_set('display_errors', 1);` -- you'll see an undefined variable notice. – Michael Berkowski May 20 '14 at 23:46
  • 1
    First of all statements made to be reusable. You should prepare statement once and then just bind your data into variables. So try to make for loop only for execute statement. And +1 for $db typo – Nagh May 20 '14 at 23:59
  • Thank you! Good call on display the errors. I was able to find an a couple errors in the object references I was using (e.g., $checkin->location->state was supposed to be $checkin->venue->location->state. I also moved the SQL and Prepare outside of the loop and had to allow NULL in some of the columns. Thanks! – Nick May 21 '14 at 00:22

0 Answers0