DEV Community

Cover image for PlanetScale - Creating a table and adding information using PHP
Luis Juarez
Luis Juarez

Posted on

3 2

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)
);
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

*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("myTestEmail1231@gmail.com", "TestUser1", "TestLastName");
Enter fullscreen mode Exit fullscreen mode

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!

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay