mysqli cannot create a table

Question

I have a trouble with my mysql. It connects to database but it doesn't make a table. Here is an example of my code:

  $db = new mysqli('localhost', 'user.name', 'user.pass', 'db');

  if ($db === FALSE) {
     echo "ERRROR";
  }   

  $sql = "CREATE TABLE IF NOT EXISTS db (
      ID INT NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(20) NULL
  )";

  $db->query($sql);

  if (mysqli_query($db, $sql)) {
     echo "TABLE CREATED SUCCESSFULLY";
  } else {
    echo "TABLE CREATED UNSUCCESSFULLY";
  }

I appreciate every answer, thank you for help!

Can you try adding this to the script? It should give us a error message: echo("TABLE CREATED UNSUCCESSFULLY: " . mysqli_error($sql)); — Anders Andersen, Dec 19 '17 at 08:43

score 5 · Accepted Answer · answered Dec 19 '17 at 08:43

5

MySQL only allows auto-increment columns if they are defined as a key. Given that the column is labelled ID, which means you probably want it as a primary key, try this:

  $sql = "CREATE TABLE IF NOT EXISTS db (
      ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      NAME VARCHAR(20) NULL
  )";

answered Dec 19 '17 at 08:43

e_i_pi

4,590
4
27
45

yeah It works!!! thank you so much! – Ondrej L Dec 19 '17 at 08:46
1

Make sure to mark this answer as accepted ig it solves your problem. Just hit the green check-mark next to this answer. – Tobias F. Dec 19 '17 at 08:50
1

sure I have to wait 2 minutes :D – Ondrej L Dec 19 '17 at 08:52

score 1 · Answer 2 · answered Dec 19 '17 at 08:44

Because there is error in MySQL query

  CREATE TABLE IF NOT EXISTS db (
      ID INT NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(20) NULL;

Incorrect table definition; there can be only one auto column and it must be defined as a key

fix :

CREATE TABLE IF NOT EXISTS db (
      ID INT NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(20) NULL,
      key idx_ID(ID)
     );

PRIMARY KEY is not compulsory, normal key can work too.

score 0 · Answer 3 · answered Dec 19 '17 at 08:45

Can you please try this.

 $db = new mysqli('localhost', 'user.name', 'user.pass', 'db');

  if ($db === FALSE) {
     echo "ERRROR";
  }   

  $sql = "CREATE TABLE IF NOT EXISTS db (
      ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      NAME VARCHAR(20) NULL
  )";

  $db->query($sql);

  if (mysqli_query($db, $sql)) {
     echo "TABLE CREATED SUCCESSFULLY";
  } else {
    echo "TABLE CREATED UNSUCCESSFULLY";
  }

mysqli cannot create a table

3 Answers3