ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

08-MySQL Create Table

In this tutorial, we’re going to focus on how to create a table in the database. As we all known,Table is compose of columns and rows. Before we proceed, let’s put in mind that creating a table should require the following:

  • Name of the Table
  • Fields
  • and Definition for each field

Tips: A good programming habit should use proper naming convention. For Example if we’re going to create table for people we will add a prefix ‘tbl‘ to make it ‘tblpeople’.

 

Generic SQL Syntax for creating Table:

CREATE TABLE table_name (column_name column_type);

Create a Table using MySQLi and PDO

The CREATE TABLE  statement is used to create table in MySQL

CREATE TABLE  `tblpeople` (

`ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`LNAME` VARCHAR( 30 ) NOT NULL ,
`FNAME` VARCHAR( 30 ) NOT NULL ,
`ADDRESS` VARCHAR( 50 ) NOT NULL

) ENGINE = INNODB;

We just created a table named ‘tblpeople‘ and it has four(4) fields, and these are ID, LNAME, FNAME and ADDRESS.

 

Here are few Items that needs and Explanation:

  • Field Attribute NOT NULL – we use this because we do not want this field to be NULL. Meaning it will require the user to put some values to it as long as it will not be leave as blank.
  • Field Attribute AUTO_INCREMENT – It means that MySQL will automatically add the value of the field every time a new record has been inserted.
  • Keyword PRIMARY KEY – this will be used to identify the field that it is unique in the table.

 

Please take note that in every table there should be a Primary Key, in our case ‘ID’.

 

Here’s the example on how to create a table in PHP:

 

Example Using MySQLi (Object-Oriented):

  1. <?php
  2. $server = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $database = "mysqltutorial";
  6.  
  7. // Create connection
  8. $conn = new mysqli($server, $username, $password, $database);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection failed: " . $conn->connect_error);
  12. }
  13.  
  14. // sql to create table
  15. $sql = "CREATE TABLE `tblpeople` (
  16. `ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  17. `LNAME` VARCHAR( 30 ) NOT NULL ,
  18. `FNAME` VARCHAR( 30 ) NOT NULL ,
  19. `ADDRESS` VARCHAR( 50 ) NOT NULL
  20. ) ENGINE = INNODB;";
  21.  
  22. if ($conn->query($sql) === TRUE) {
  23. echo "Table tblpeople created successfully";
  24. } else {
  25. echo "Error creating table: " . $conn->error;
  26. }
  27.  
  28. $conn->close();
  29. ?>

Example Using MySQLi (Procedural):

  1. <?php
  2. $server = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $database = "mysqltutorial";
  6.  
  7. // Create connection
  8. $conn = mysqli_connect($server, $username, $password, $database);
  9. // Check connection
  10. if (!$conn) {
  11. die("Connection failed: " . mysqli_connect_error());
  12. }
  13.  
  14. // sql to create table
  15. $sql = "CREATE TABLE `tblpeople` (
  16. `ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  17. `LNAME` VARCHAR( 30 ) NOT NULL ,
  18. `FNAME` VARCHAR( 30 ) NOT NULL ,
  19. `ADDRESS` VARCHAR( 50 ) NOT NULL
  20. ) ENGINE = INNODB;";
  21.  
  22. if (mysqli_query($conn, $sql)) {
  23. echo "Table tblpeople created successfully";
  24. } else {
  25. echo "Error creating table: " . mysqli_error($conn);
  26. }
  27.  
  28. mysqli_close($conn);
  29. ?>

Example Using PDO:

  1. <?php
  2. $server = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $database = "mysqltutorial";
  6.  
  7. try {
  8. $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);
  9. // set the PDO error mode to exception
  10. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  11.  
  12. // sql to create table
  13. $sql = "CREATE TABLE `tblpeople` (
  14. `ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  15. `LNAME` VARCHAR( 30 ) NOT NULL ,
  16. `FNAME` VARCHAR( 30 ) NOT NULL ,
  17. `ADDRESS` VARCHAR( 50 ) NOT NULL
  18. ) ENGINE = INNODB;";
  19.  
  20. // use exec() because no results are returned
  21. $conn->exec($sql);
  22. echo "Table tblpeople created successfully";
  23. }
  24. catch(PDOException $e)
  25. {
  26. echo $sql . "<br>" . $e->getMessage();
  27. }
  28.  
  29. $conn = null;
  30. ?>

 

Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!