The table creation order requires −
- Name of the table
- Names of fields
- Definitions for each field
Syntax
Here is conventional SQL sentence structure to make a MySQLi table −
CREATE TABLE table_name (column_name column_type);
Presently, we will make following table in TUTORIALS data set.
CREATE TABLE tutorials_inf(
id INT AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
primary key (id),
);
Here couple of things need clarification −
- Field Attribute NOT NULL is being utilized on the grounds that we don't need this field to be NULL. So on the off chance that client will attempt to make a record with NULL worth, at that point MySQLi will raise a blunder.
- Field Attribute AUTO_INCREMENT advises MySQLi to feel free to add the following accessible number to the id field.
- Watchword PRIMARY KEY is utilized to characterize a section as essential key. You can utilize various segments isolated by comma to characterize an essential key.
Creating Tables from Command Prompt
This is not difficult to make a MySQLi table from mysql> brief. You will utilize SQL order CREATE TABLE to make a table.
Example
Here is a model, which makes tutorials_tbl −
root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_inf(
id INT AUTO_INCREMENT,name VARCHAR(20) NOT NULL,primary key (id));
Query OK, 0 rows affected (0.16 sec)
mysql>
NOTE − MySQLi doesn't end an order until you give a semicolon (;) toward the finish of SQL order.
Creating Tables Using PHP Script
To make new table in any current information base you would have to utilize PHP work mysqli_query(). You will pass its second contention with appropriate SQL order to make a table.
Example
Here is a guide to make a table utilizing PHP content −
<html>
<head>
<title>Creating MySQLi Tables</title>
</head>
<body>
<?php
$host = 'localhost:3306';
$user = 'root';
$pass = '<Password Here>';
$dbname = 'TUTORIALS';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(!$conn){
die('Could not connect: '.mysqli_connect_error());
}
echo 'Connected successfully<br/>';
$sql = "create table tutorials_inf(
id INT AUTO_INCREMENT,name VARCHAR(20) NOT NULL,primary key (id))";
if(mysqli_query($conn, $sql)){
echo "Table created successfully";
} else {
echo "Table is not created successfully ";
}
mysqli_close($conn);
?>
</body>
</html>
The example yield ought to be this way −
Connected successfully
Table created successfully