Creating Database Tables in a MySQL Database Using PDO in PHP

The following code example shows Creating Database Tables in a MySQL Database Using PDO in PHP.

Before any other statement, we need to create a connection with the database. So here we are using the require() method that includes the connecting_database.php file. For details of creating connection, click here. This PHP file contains the code to create a connection with the MySQL database using the PDO class. As a result, we get the object of PDO class – $con. Because we wish to create three tables in the database, we create a string variable $sql that contains three create table commands separated by semicolons. In order to execute these commands contained in the string, here we call the exec() method with the object of PDO. In fact, the exec() method runs the SQL commands and returns an integer indicating the number of affected rows.

Since we have already set error reporting to exception so any error will cause an exception to be thrown.

Program for Creating Database Tables in a MySQL Database Using PDO in PHP

<?php
 require('connecting_database.php');
 try{
   $sql='create table customer(cust_id integer primary key, cust_name varchar(30), cust_city varchar(20));create table product(prod_id integer primary key, pname varchar(30), price integer); create table Orders(cust_id integer, product_id integer, quantity integer, primary key(cust_id, product_id));';
   $con->exec($sql) or die(print_r($con->errorInfo(), true));

   echo 'Tables Created!';
 }
 catch(PDOException $ex)
 {
     die('Error Creating Table: '.$ex->getMessage());
 }
?>

Output

As a result of executing the above PHP script, the three tables are created as shown below.

**The Result of Creating Database Tables in a MySQL Database Using PDO in PHP**

However, we also need to set the referential integrity constraint that forms the primary key and foreign key relationship. This need to be done for ,cust_id attribute of the customer and orders table. Similarly, it should be done for the prod_id and product_id attributes of the product and orders table respectively. For this you just need to run the ALTER TABLE command through the exec() method of the PDO object.

Program for Creating Database Tables in a MySQL Database Using PDO in PHP

Further Reading