PlanetScale - Creating a table and adding information using PHP

I previously made a post about connecting to a PlanetScale database, you can find it here.

Creating a table

You can create a table with PlanetScale's UI or programmatically. I'll be using PHP, but you can use any language you prefer, once you've connected to your instance.

PlanetScale UI

There is a web console you can access from your PlanetScale branch that allows you to run SQL code directly.

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` varchar(255) NOT NULL,
  `first_name` varchar(255),
  `last_name` varchar(255)
);

Programmatically creating a table

I use PHP's PDO class, so assuming you have a PDO object $db created.

function createTable() {
    global $db;
    $query = 'CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` varchar(255) NOT NULL,
  `first_name` varchar(255),
  `last_name` varchar(255)
)';
    $statement = $db->prepare($query);
    $statement->execute();
    $statement->closeCursor();
}

*Note: This will not work if your branch is running in "production" mode. The point of PlaneScale is that you can make schema updates without breaking your database, so they don't let you make modifications directly to that branch. If you already have your main branch in production mode, create a new branch. I called my second branch 'dev' and setup a PDO to the second branch so I can modify the schema there if needed. Then once you are ready you can "deploy request" your branch to the main.

Inserting data into PlanetScale Database

You can now execute SQL statements as you normally would.

function addRow($userEmail, $fname, $lname) {
    global $db;
    //add user to group
    $query2 = "INSERT INTO users (email, first_name, last_name) VALUES (:userEmail,:first_name, :last_name)";
    $statement = $db->prepare($query2);
    $statement->bindValue(":userEmail", $userEmail);
    $statement->bindValue(":first_name", $fname);
    $statement->bindValue(":last_name", $lname);
    $statement->execute();
    $statement->closeCursor();
}

addRow("[e-mail]", "TestUser1", "TestLastName");

If you are using PHPStorm, I would highly recommend connecting your PlanetScale database to your IDE through DataGrip. Here is an article on how to do that. This will give you things like autocomplete and make it easier to catch bugs/typos in your SQL statements.

If you found this helpful or have questions, drop a comment below or ping me on Twitter @helloLuisJ!