Skip to main content

PHP Creating MySQL Tables


PHP Creating MySQL Tables

Before, I created a tutorial for PHP MySQL Database. For the follow-up tutorial, we are going to learn on How To Create MySQL Table In The Database. In the database, it will consist one or more tables. So, let’s start to create MySQL Table.

Note: All database table has a unique name.

Using MySQLi and PDO To Create MySQL Table.

We are going to use CREATE TABLE statement to make a table in MySQL.

  1. -- --------------------------------------------------------
  2.  
  3. --
  4. -- Table structure for table `tbl_registration`
  5. --
  6.  
  7. CREATE TABLE `tbl_registration` (
  8. `tbl_registration_id` INT(11) NOT NULL,
  9. `first_name` VARCHAR(100) NOT NULL,
  10. `middle_name` VARCHAR(100) NOT NULL,
  11. `last_name` VARCHAR(100) NOT NULL,
  12. `email` VARCHAR(100) NOT NULL,
  13. `contact_number` VARCHAR(100) NOT NULL
  14. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So, we are going to create our table, name as “tbl_registration” and contain with “tbl_registration_id”, “first_name”, “middle_name”, “last_name”, “email”, “contact_number”.

Note: In each database table has an individual primary key. In the example table above, our primary key is “tbl_registration_id”. And it's value is unique.

Creating Database Table Using MySQLi (Object-Oriented)

  1. <?php
  2. $servername = "localhost";
  3. $username = "username";
  4. $password = "password";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = new mysqli($servername, $username, $password, $dbname);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection Error. Check your syntax." . $conn->connect_error);
  12. }
  13.  
  14. // sql to create table
  15. $sql = "CREATE TABLE `tbl_registration` (
  16. `tbl_registration_id` int(11) NOT NULL,
  17. `first_name` varchar(100) NOT NULL,
  18. `middle_name` varchar(100) NOT NULL,
  19. `last_name` varchar(100) NOT NULL,
  20. `email` varchar(100) NOT NULL,
  21. `contact_number` varchar(100) NOT NULL
  22. )";
  23.  
  24. if ($conn->query($sql) === TRUE) {
  25. echo "Your Table tbl_registration Successfully Created!!!";
  26. } else {
  27. echo "Error creating table. Check the Syntax." . $conn->error;
  28. }
  29.  
  30. $conn->close();
  31. ?>

Creating Database Table Using MySQLi (Procedural)

  1. <?php
  2. $servername = "localhost";
  3. $username = "username";
  4. $password = "password";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = mysqli_connect($servername, $username, $password, $dbname);
  9. // Check connection
  10. if (!$conn) {
  11. die("Connection Error. Check your syntax." . mysqli_connect_error());
  12. }
  13.  
  14. // sql to create table
  15. $sql = "CREATE TABLE `tbl_registration` (
  16. `tbl_registration_id` int(11) NOT NULL,
  17. `first_name` varchar(100) NOT NULL,
  18. `middle_name` varchar(100) NOT NULL,
  19. `last_name` varchar(100) NOT NULL,
  20. `email` varchar(100) NOT NULL,
  21. `contact_number` varchar(100) NOT NULL
  22. )";
  23.  
  24. if (mysqli_query($conn, $sql)) {
  25. echo "Your Table tbl_registration Successfully Created!!!";
  26. } else {
  27. echo "Error creating table. Check the Syntax." . mysqli_error($conn);
  28. }
  29.  
  30. mysqli_close($conn);
  31. ?>

Creating Database Table Using PDO (PHP Data Objects)

  1. <?php
  2. $servername = "localhost";
  3. $username = "username";
  4. $password = "password";
  5. $dbname = "add_query_pdo";
  6.  
  7. try {
  8. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $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 `tbl_registration` (
  14. `tbl_registration_id` int(11) NOT NULL,
  15. `first_name` varchar(100) NOT NULL,
  16. `middle_name` varchar(100) NOT NULL,
  17. `last_name` varchar(100) NOT NULL,
  18. `email` varchar(100) NOT NULL,
  19. `contact_number` varchar(100) NOT NULL
  20. )";
  21.  
  22. // use exec() because no results are returned
  23. $conn->exec($sql);
  24. echo "Your Table tbl_registration Successfully Created!!!";
  25. }
  26. catch(PDOException $e)
  27. {
  28. echo $sql . "<br>" . $e->getMessage();
  29. }
  30.  
  31. $conn = null;
  32. ?>

Output:

Result

So, this is it. You have one table in your database. And you can create more tables in your database. For my next tutorial, we are going to learn on PHP Insert Data In MySQL.

Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.