CODEWITHSUNDEEP
phpmysqldatabase
James Oliver
James Oliver

Reputation: 33

Problems automatically creating a mysql database so it can be used on a fresh computer using PHP

I am trying to automatically create the database and table so the website i am creating can be used on a fresh version of XXAMP. At the moment I used PHP myadmin to create the table. however when it loads on a fresh version of XXAMP the database will not be saved on the fresh computer. Therefore im trying to create PHP to automatically create the database and table so content can be added. This is my attempt at the moment but it doesn't seem to be working. Can anyone push me in the right direction?

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "contentdatabase";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // sql to create table
    $sql = "CREATE TABLE items
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    itemName text NULL,
    itemDescription text NULL,
    itemPrice float NULL,
    itemStock smallint(6) NULL,
    itemImage VARCHAR(100)  NULL,
    )";

    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Table items created successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

Upvotes: 0

Views: 72

Answers (3)

James Oliver
James Oliver

Reputation: 33

Thank you for your help! I have got this semi working now thankfully! I can now create a new database and table. However I am now getting this error but i guess its some sort of if statement i need to add.

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'item' already existsCREATE DATABASE contentdatabase1 SQLSTATE[HY000]: General error: 1007 Can't create database 'contentdatabase1'; database exists

Upvotes: 0

Daniel Aranda
Daniel Aranda

Reputation: 6552

What you want to do is known as database migration, and there are some frameworks available for it, please take a look at this which is a framework focused in DB migrations.

Upvotes: 1

NaijaProgrammer
NaijaProgrammer

Reputation: 2957

Replace the corresponding part of your code with the below. I have added comments in the relevant places. Remove the comments before running the code

// sql to create table
$sql = "CREATE TABLE items ( //yours lacks this opening parentheses
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
itemName text NULL,
itemDescription text NULL,
itemPrice float NULL,
itemStock smallint(6) NULL,
itemImage VARCHAR(100)  NULL //I have removed the last comma (,)
)";

Upvotes: 0

Related Questions