PHP MySQL tutorial

Create Table in MySQL via PHP Script

Once you have created and selected a database, you can perform all sorts of queries on it. This article focuses on creating a Table in MySQL database. A table holds record about a particular entity in the form of rows and columns. For instance, a Hospital database which stores data related to hospital might have a table called patient, another table called department and report etc.

Each column in the table corresponds to specific attribute. For instance, a Patient table might have a column named Patient_Name, Patient_Age etc. A row in a table corresponds to one complete record. To create a table in MySQL, following query is used.

CREATE TABLE table (column type);

Create Table via Object Oriented MySQLi

To create a table via PHP is a straight forward process. Pass the query for creating table to the query function of the mysqli object and PHP will create your table in the selected database. Take a look at the following example.




Using MySQLi for creating MySQL Table



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 

$query = "Use Hospital";
$connection->query($query);
    
// Creating Table
$query = "CREATE TABLE Patient (
patient_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
patient_fname VARCHAR(50) NOT NULL,
patient_lname VARCHAR(50) NOT NULL,
patient_age INT(10)
);";
if ($connection->query($query) === TRUE) {
    echo "Successful table creation";
} else {
    echo "Unable to create table" . $connection->error;
}

$connection->close();
?>
        



Download the code

The above code will create a Table named Patient in the Hospital database. The table will have four columns: patient_id, patient_fname, patient_lname and patient_age. Notice how columns are added in the table. The name of the column, followed by the type of data that will be stored in the column e.g. VARCHAR(50) which stores variable character type data of maximum size 50. The NOT NULL property specifies that this column cannot be empty.

Notice that the patient_id column has been marked AUTO_INCREMENT which means that whenever a record is entered in this table, this value will be automatically added and incremented. Finally the PRIMARY KEY attribute states that this column will hold unique identity for all the record in the table.

Create Table via Procedural MySQLi

Table creation in Procedural MySQLi is very easy. Just replace mysqli object with mysqli_connect function. Have a look at second example.




Using MySQLi for Table creation



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital";
$connection->query($query);
    
// Creating Table
$query = "CREATE TABLE Patient (
patient_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
patient_fname VARCHAR(50) NOT NULL,
patient_lname VARCHAR(50) NOT NULL,
patient_age INT(10)
);";
    
    
if ($connection->query($query) === TRUE) {
    echo "Successful database creation";
} else {
    echo "Unable to create database " . $connection->error;
}

$connection->close();
?>
      



Download the code

<<< Selecting Mysql Database via PHP ScriptDrop Table in MySQL >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .