MySQLi Tutorial on MySQLi Create Table
Back to Course

to begin with, the table creation command requires the following details −

  • name of the table
  • name of the fields
  • definitions for each field

syntax

here is a generic sql syntax to create a mysql table −

create table table_name (column_name column_type);

now, we will create the following table in the tutorials database.

create table tutorials_tbl(
   tutorial_id int not null auto_increment,
   tutorial_title varchar(100) not null,
   tutorial_author varchar(40) not null,
   submission_date date,
   primary key ( tutorial_id )
);

here, a few items need explanation −

  • field attribute not null is being used because we do not want this field to be null. so, if a user will try to create a record with a null value, then mysql will raise an error.

  • field attribute auto_increment tells mysql to go ahead and add the next available number to the id field.

  • keyword primary key is used to define a column as a primary key. you can use multiple columns separated by a comma to define a primary key.

creating tables from command prompt

it is easy to create a mysql table from the mysql> prompt. you will use the sql command create table to create a table.

example

here is an example, which will create tutorials_tbl −

root@host# mysql -u root -p
enter password:*******
mysql> use tutorials;
database changed
mysql> create table tutorials_tbl(
   → tutorial_id int not null auto_increment,
   → tutorial_title varchar(100) not null,
   → tutorial_author varchar(40) not null,
   → submission_date date,
   → primary key ( tutorial_id )
   → );
query ok, 0 rows affected (0.16 sec)
mysql>

note − mysql does not terminate a command until you give a semicolon (;) at the end of sql command.

creating tables using php script

php uses mysqli query() or mysql_query() function to create a mysql table. this function takes two parameters and returns true on success or false on failure.

syntax

$mysqli→query($sql,$resultmode)

sr.no. parameter & description
1

$sql

required - sql query to create a mysql table.
2

$resultmode

optional - either the constant mysqli_use_result or mysqli_store_result depending on the desired behavior. by default, mysqli_store_result is used.

example

try the following example to create a table −

copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>creating mysql table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'tutorials';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('connected successfully.<br />');
   
         $sql = "create table tutorials_tbl( ".
            "tutorial_id int not null auto_increment, ".
            "tutorial_title varchar(100) not null, ".
            "tutorial_author varchar(40) not null, ".
            "submission_date date, ".
            "primary key ( tutorial_id )); ";
         if ($mysqli→query($sql)) {
            printf("table tutorials_tbl created successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("could not create table: %s<br />", $mysqli→error);
         }
         $mysqli→close();
      ?>
   </body>
</html>

output

access the mysql_example.php deployed on apache web server and verify the output.

connected successfully.
table tutorials_tbl created successfully.
Previous Lesson