Creating, retrieving, updating & deleting records in PHP @ GAE

Google uses BigTable as the database in Google App Engine (GAE). This article uses the JDO layer with the Datastore Java API to create, retrieve, update & delete Database records in PHP at Google App Engine.

The starting point for this example is an GAE project in Eclipse with PHP, see this blog posting to set that up.

First create a java class for setting up the database connection, save below file as src/database/connection.java in your Eclipse GAE project.

package database;

import javax.jdo.JDOHelper;
import javax.jdo.PersistenceManagerFactory;

public final class connection {
    private static final PersistenceManagerFactory pmfInstance =
        JDOHelper.getPersistenceManagerFactory("transactions-optional");

    private connection() {}

    public static PersistenceManagerFactory get() {
        return pmfInstance;
    }
}  

Create a Java class with JDO annotations for every database table you want to use, below the one as used in this article, save it as src/database/person.java in your Eclipse GAE project.

package database;

import javax.jdo.annotations.IdGeneratorStrategy;
import javax.jdo.annotations.IdentityType;
import javax.jdo.annotations.PersistenceCapable;
import javax.jdo.annotations.Persistent;
import javax.jdo.annotations.PrimaryKey;
 
@PersistenceCapable(identityType = IdentityType.APPLICATION)
public class person {
   
  @PrimaryKey
  @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
  private Long person;
    
  @Persistent
  private String firstName;
  
  @Persistent
  private String lastName;
  
  public Long getPerson() { 
   return person;         
  }

  public String getFirstName() { 
   return firstName;    
  }
  
  public String getLastName() { 
   return lastName;     
  }

  public void setFirstName(String in) { 
   this.firstName = in;
  } 

  public void setLastName(String in) { 
   this.lastName = in;  
  }

}

Above Java code has the same functionality as below SQL code (please make no nasty comments about the java folks)

CREATE TABLE person (
 person BIGINT AUTO_INCREMENT ,
 firstName TEXT,
 lastName TEXT,
 PRIMARY KEY (person)
);

Now we are ready to create PHP scripts that uses GAE BigTable as database! Below the PHP source that will be used as a template for every example.

<?php

 echo "<pre>";

 // Import the java code for setting up a database connection.
 import database.connection;

 // Import the table definition class
 import database.person;

 // Setup a database connection.
 $connection = connection::get()->getPersistenceManager();

 ..... example code goes here ..... 

 // Close the database connection.
 $connection->close();

 echo "</pre>";

?>

Creating records in the database can be done with below PHP code.

$record = new person;

$record->setFirstName('John');
$record->setLastName('Doe');

$connection->makePersistent($record);

$key = $record->getPerson(); // Not the record but the field person that is primary key

echo "Added 'John Doe' with number $key in the database \n";

$record = new person;

$record->setFirstName('Jane');
$record->setLastName('Doe');

$connection->makePersistent($record);

$key = $record->getPerson(); // Not the record but the field person that is primary key

echo "Added 'Jane Doe' with number $key in the database \n";

Retrieving records from the database, that can be done with below PHP script.

$query = $connection->newQuery("select from database.person");
$results = $query->execute();

foreach($results as $record) {
  echo "Key: " . $record->getPerson();
  echo " Name: " . $record->getFirstName() . " " . $record->getLastName() . "\n";
}

Above examples prints all records from the table, of course you can change that, use something like below

$query = $connection->newQuery("select from person where person == 5");
$query = $connection->newQuery('select from person where lastName <> "Doe"');

A Update example below.

$query = $connection->newQuery('select from database.person where firstName == "John"');
$results = $query->execute();

$record = $results[0];
  
$record->setFirstName("Lucky");
$record->setLastName("Luke");

$connection->flush{};

The delete example:

$query = $connection->newQuery('select from database.person where firstName == "Jane"');
$results = $query->execute();

$connection->deletePersistentAll($results);

See here for a proof of concept how you can use 'normal' SQL in PHP at GAE

Sources from this article