In this post we’re going to cover an important and simple SQL operation called transactions and rollbacks. These SQL queries allow you to only perform an operation if the entire series of insertions/edits is successful. So if you’re inserting 50 new users and user #24 has an error, the entire operation will rollback so no new users would be added.

This is nice because if there is an error it can be difficult to track down bugs or manually revert back to the starting point. so it’s best to just have the whole thing succeed or fail.

First, we need a development environment to practice. I’ve setup a Docker environment for us to practice with. Download the SQL Transaction source here. Here’s a brief explanation of the code for those not familiar with Docker:

# Use root/example user/password credentials
version: '3.1'

services:
  php:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - 80:80
    volumes:
      - ./src:/var/www/html/

  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

  # http:

step 1. Set autocommit to false

Step 2. Begin transaction.

Step 3. Run necessary queries While verifying that everything is still OK with a variable. (used ternary)

at the end, run $mysqli->commit(); OR $mysqli->rollback(); to revert.

Set autcommit back to true: $mysqli->autocommit(TRUE);

Published by John Curry

John has a diverse technical skill set. He's a "Full-Stack" developer, and can do anything from using page builders in WordPress to building complex applications with a variety of modern tools. When he's not coding you can find him underwater or in the mountains.

Leave a comment

Your email address will not be published.