DIGITAL4FRONT / CODE BASE

PHP MySQL Database Setup Without Duplicate Seed Data

Make a PHP and MySQL setup script safe to run repeatedly. Create the schema separately, use PDO and a unique SKU, and insert sample products without duplicating rows or overwriting stock.

Share

PHP database setup should be a separate, repeatable installation task, not code that runs every time somebody visits a page. To prevent duplicate sample records, give each product a stable unique identifier and seed it with an insert that does nothing when that identifier already exists. Use a dedicated database account, and keep credentials out of source code.

Why the original script creates duplicates

The example creates the database and products table with IF NOT EXISTS, then runs a plain INSERT containing Carrot, Blueberries and Cabbage. The table check only prevents another table being created; it does not stop those three rows being inserted every time the script runs.

The script also connects as MySQL root without a password and exposes database administration through a web page. Neither is appropriate for a deployed application. Reconnecting after CREATE DATABASE is understandable, but the cleaner design is to provision the database once, then let a separate setup script connect directly to it.

Provision the database once

In your local MySQL administration tool, create the database before running the PHP installer:

sql
CREATE DATABASE IF NOT EXISTS greenfield_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Create a dedicated setup account through your database administration tooling. Grant it only the permissions required for installation, such as CREATE and INSERT on this database, plus the access needed for later migrations. Use a separate, more restricted account for everyday application requests. Never connect the public website as root.

Supply DB_HOST, DB_NAME, DB_USER and DB_PASS through environment configuration. Do not commit those values to Git or place an installer in a publicly accessible directory.

A repeatable PHP setup script

Save the following as scripts/setup.php outside the public web root and run it from the command line. It requires PHP with the pdo_mysql extension and an existing database. The example assumes the setup account has permission to create a table and insert rows.

php
<?php
declare(strict_types=1);

if (PHP_SAPI !== 'cli') {
 http_response_code(403);
 exit("CLI only.\n");
}

$config = [];
foreach (['DB_HOST', 'DB_NAME', 'DB_USER', 'DB_PASS'] as $key) {
 $value = getenv($key);
 if ($value === false || $value === '') {
 throw new RuntimeException("Missing environment variable: $key");
 }
 $config[$key] = $value;
}

$dsn = sprintf(
 'mysql:host=%s;dbname=%s;charset=utf8mb4',
 $config['DB_HOST'],
 $config['DB_NAME']
);

$pdo = new PDO($dsn, $config['DB_USER'], $config['DB_PASS'], [
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
 PDO::ATTR_EMULATE_PREPARES => false,
]);

$pdo->exec(
 'CREATE TABLE IF NOT EXISTS products (
 product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sku VARCHAR(40) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_count INT UNSIGNED NOT NULL DEFAULT 0,
 product_price DECIMAL(10,2) NOT NULL,
 UNIQUE KEY uq_products_sku (sku)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
);

$products = [
 ['CARROT', 'Carrot', 3, '1.50'],
 ['BLUEBERRIES', 'Blueberries', 6, '2.45'],
 ['CABBAGE', 'Cabbage', 2, '1.00'],
];

$insert = $pdo->prepare(
 'INSERT INTO products
 (sku, product_name, product_count, product_price)
 VALUES (:sku, :name, :quantity, :price)
 ON DUPLICATE KEY UPDATE product_id = product_id'
);

$pdo->beginTransaction();
try {
 foreach ($products as [$sku, $name, $quantity, $price]) {
 $insert->bindValue(':sku', $sku);
 $insert->bindValue(':name', $name);
 $insert->bindValue(':quantity', $quantity, PDO::PARAM_INT);
 $insert->bindValue(':price', $price);
 $insert->execute();
 }
 $pdo->commit();
} catch (Throwable $e) {
 if ($pdo->inTransaction()) {
 $pdo->rollBack();
 }
 throw $e;
}

echo "Database setup complete.\n";

Run it from your shell after setting the environment variables through a secure local configuration mechanism:

bash
php scripts/setup.php

Run it a second time. There should still be only three product rows, assuming the table started empty.

Why a unique SKU matters

A product name alone is not always a safe identifier: two products can have the same display name, and names can change. A SKU is a better candidate when the business guarantees it is unique and stable.

The UNIQUE constraint enforces that rule at database level, even if two processes try to insert the same product. The ON DUPLICATE KEY UPDATE clause in the example performs a no-op for existing rows, preserving manually edited quantities and prices. It is a MySQL-specific solution, not a portable SQL feature.

A no-op update may still interact with database triggers, and attempted duplicate inserts can create gaps in AUTO_INCREMENT values. If either matters, use a seed process tailored to those requirements. If you deliberately want new seed values to overwrite old ones, implement an explicit upsert policy rather than accidentally resetting live stock levels.

Schema creation is not schema migration

CREATE TABLE IF NOT EXISTS does not add missing columns or change types in an existing table. If you add a supplier_id column to this script next week, running it again will not alter a previously created products table.

Use numbered, version-controlled migrations for changes after the initial setup. Apply migrations during deployment, record which versions succeeded, and take a database backup before potentially destructive changes. The application itself should simply connect to the expected schema; it should not attempt to repair it on each request.

Testing and next steps

Test the setup on an empty database, then run it repeatedly and confirm that the row count stays constant. Change a product price manually, rerun the script and verify that your chosen policy preserves it. Also test what happens when the setup account lacks a required permission: the process should fail clearly rather than partly updating the application.

Use PHP's PDO documentation for connection and transaction behaviour, and the MySQL INSERT reference for duplicate-key handling.

For a production system, separate provisioning, migrations and seed data from ordinary website traffic. That makes deployment safer and keeps database changes deliberate.

WhatsApp us