PHP MySQL can't create table

Question

Here is my code. But it is showing error while creating table tblStudents every time. Why is that? Need a solution as soon as possible.

<?php

$con=mysqli_connect("localhost","root","noor.xbyte","fathis_quran_class");
if (mysqli_connect_errno()) {
  echo '&lt;h1>Error Connecting to the database!&lt;/h1>';
} else {
  $sql = "CREATE TABLE tblStudents
  (
  index INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(index),
  fullName CHAR(30) NOT NULL,
  dateOfBirth DATE NOT NULL,
  SID TINYINT NOT NULL,
  address CHAR(30) NOT NULL,
  level TINYINT NOT NULL,)";
  if (mysqli_query($con,$sql)) {
    echo 'Table "tblStudents" created successfully!';
  } else {
    echo 'Error creating table "tblStudents"';
  }
}

?>;

`CREATE TABLE tblStudents IF NOT EXISTS`. http://dev.mysql.com/doc/refman/5.1/en/create-table.html — Anthony Sterling, Feb 26 '14 at 12:28
That is the better solution, saves you spending years writing queries that fail first time because you forgot to use back tics again! — Kickstart, Feb 26 '14 at 12:50

score 5 · Accepted Answer · edited Feb 26 '14 at 13:38

You have a few mistakes, here is the correct statement

CREATE TABLE `table_name`
(
  `index` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(`index`),
  fullName CHAR(30) NOT NULL,
  dateOfBirth DATE NOT NULL,
  SID TINYINT NOT NULL,
  address CHAR(30) NOT NULL,
  level TINYINT NOT NULL
)

The mistakes you had were:

index is a reserved key word so use backticks `index` for column name
there was an extra comma (,) after "level TINYINT NOT NULL".

score 2 · Answer 2 · answered Feb 26 '14 at 12:31

2

Give another name for the field 'index'. You can't use it as it is reserved word.

answered Feb 26 '14 at 12:31

Jenz

8,280
7
44
77

score 1 · Answer 3 · answered Feb 26 '14 at 12:32

The index is a reserved keyword here.

So, you need to add a tilt (`) to index.

Corrected code:

CREATE TABLE tblStudents
(
  `index` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(`index`),
  fullName CHAR(30) NOT NULL,
  dateOfBirth DATE NOT NULL,
  SID TINYINT NOT NULL,
  address CHAR(30) NOT NULL,
  level TINYINT NOT NULL
)

score 0 · Answer 4 · 2014-02-26T12:57:31.760

**Try below code,I think it will work fine..** 




<?php

    $con=mysqli_connect("localhost","root","noor.xbyte","fathis_quran_class");
    if (mysqli_connect_errno()) {
      echo '&lt;h1>Error Connecting to the database!&lt;/h1>';
    } else {
      $sql = "CREATE TABLE tblStudents(

      index int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY (`index`),
      fullName varchar(30) NOT NULL,
      dateofbirth date NOT NULL,
      SID tinyint(4) NOT NULL,
      address varchar(30) NOT NULL,
      level tinyint(4) NOT NULL);
      if (mysqli_query($con,$sql)) {
        echo 'Table "tblStudents" created successfully!';
      } else {
        echo 'Error creating table "tblStudents"';
      }
    }

    ?>;

PHP MySQL can't create table

4 Answers4