Tables cannot be created dynamically in MySQL

Question

I'm following Model View Control method to create an application.

I'm trying to dynamically create tables upon a page is loaded. Here is the code for that.

In dashboard.php

<?php
  require_once ("controller/db-config.php"); 
  require_once  ("controller/connectDB.php");
?>

In db-config.php

<?php
    define('DB_NAME', 'learningcamp');
    define('DB_USER', 'root');
    define('DB_PASSWORD', ''); //Put your MySQL password here
    define('DB_HOST', 'localhost');
?>

In connectDB.php

<?php
    require_once ("controller/db-config.php");     
    require_once ("model/database.php");


    // Create database connection
    $databaseConnection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    if ($databaseConnection->connect_errno)
    {
        die("Database selection failed: " . $databaseConnection->connect_error);
    }

    // Create tables if needed.
    prep_DB_content();
?>

In database.php

<?php
    require_once ("controller/db-config.php"); 

    function prep_DB_content (){
        global $databaseConnection;
        create_tables($databaseConnection);
    }

    function create_tables($databaseConnection){    
        $query_fo_pages = "CREATE TABLE IF NOT EXISTS frontoffice (sl INT NOT NULL AUTO_INCREMENT, field VARCHAR(32) NOT NULL, description VARCHAR(800), flag INT NOT NULL)";
        $databaseConnection->query($query_fo_pages);

    }
?>

But somehow the tables are not created when I refresh the page dashboard.php. Where did I go wrong?

Put this at the top of dashboard.php and refresh the page: error_reporting(E_ALL); ini_set('display_errors', '1'); — I wrestled a bear once.
– I wrestled a bear once., Commented Oct 13, 2014 at 17:31
No but auto increment needs primary key CREATE TABLE IF NOT EXISTS frontoffice (sl INT NOT NULL AUTO_INCREMENT, field VARCHAR(32) NOT NULL, description VARCHAR(800), flag INT NOT NULL, PRIMARY KEY(sl)) — Mihai
– Mihai, Commented Oct 13, 2014 at 17:36

AdamMc331 · Accepted Answer · 2014-10-13 17:37:09Z

3

The problem is that you defined an AUTO_INCREMENT column but not a primary key.

If you define any columns as auto increment, you must also make them key. Try this:

CREATE TABLE IF NOT EXISTS frontoffice(
    sl INT NOT NULL AUTO_INCREMENT, 
    field VARCHAR(32) NOT NULL, 
    description VARCHAR(800), 
    flag INT NOT NULL,
    PRIMARY KEY(sl))

SQL Fiddle.

EDIT

To answer your question in the comments, you would not have been required to declare a primary key if you had not used an auto_increment column.

Fiddle without auto increment and key.

answered Oct 13, 2014 at 17:37

AdamMc331

16.7k11 gold badges78 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ivan Cachicatari · Accepted Answer · 2014-10-13 17:37:36Z

3

You have an error in your SQL creation:

    $query_fo_pages = "CREATE TABLE IF NOT EXISTS frontoffice (sl INT NOT NULL AUTO_INCREMENT PRIMARY KEY, field VARCHAR(32) NOT NULL, description VARCHAR(800), flag INT NOT NULL)";

Add PRIMARY KEY on auto_increment column.

answered Oct 13, 2014 at 17:37

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

Collectives™ on Stack Overflow

Tables cannot be created dynamically in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related