PHP and MySQLi Create Table

Here are the two ways that you can go for, to create MySQL table:

Create MySQL table manually
Create MySQL table using PHP with MySQLi

Let's learn about both the above ways used in creating PHP and MySQLi table.

Before going to learn about creating table, be sure that you have learned the previous tutorial of creating database or you have created a database named codescrackerTwo.

Create MySQL Table Manually

To create MySQL table, follow the steps given below:

Open XAMPP
Start Apache and MySQL Module
Click on Admin button of MySQL module
Click on codescrackerTwo in the left pannel of phpMyAdmin Window
Enter name of table say codescrackerTable
Choose the total number of column
Click on Go button
Fill all the four column's detail
Click on Save button

After performing all the above action, your table will be created.

Here is the screenshot of the appeared window after performing 4^th action

And here is the demo screenshot after performing the 8^th action

When filling the column's detail, if you select Primary from the Index dropdown of id, there will appear a pop-up window, at there you have to click on the OK button. Tick the A_I's checkbox of id. Here A_I stands for auto increment. You id will be automatically incremented when a new row inserted.

Now, here is the screenshot after performing the 9^th (last) action

After doing all the above things, you have successfully created a table manually named codescrackerTable with 4 columns, that is

id
datetime
userid
usercomment

Create MySQL Table using PHP with MySQLi

Now let's create a MySQL table using PHP with MySQLi. Here is an example demonstrates how to create a MySQL table using PHP.

<?php 
	$serverName = "localhost";
	$serverUser = "root";
	$serverPassword = "";
	$databaseName = "codescrackerTwo";
	// as the default server name is localhost 
	// and the default username is root 
	// and the default password is blank
	$conn = mysqli_connect($serverName, $serverUser, $serverPassword, $databaseName);
	if(mysqli_connect_error())
	{
		echo "<p>There is something wrong happend when connecting to the database.</p>";
		echo "<p>Please try again after sometime.</p>";
		echo "<p>Exiting....</p>";
		exit();
	}
?>
<html>
<head>
	<title>PHP and MySQLi Create Table Example</title>
</head>
<body>
<?php
	$sqlQuery = "create table codescrackerTableTwo(
					id INT(10) NOT NULL AUTO_INCREMENT,
					datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
					userid VARCHAR(40) NOT NULL,
					usercomment VARCHAR(400) NOT NULL,
					PRIMARY KEY (id)
				);";
	$returnResult = $conn->query($sqlQuery);
	if($returnResult)
	{
		echo "<p>Table created successfully.</p>";
	}
	else 
	{
		echo "<p>Error occurred while creating the table.</p>";
		echo "<p>Exiting...</p>";
		exit();
	}
?>
</body>
</html>

Type and save the above PHP and MySQLi creating table example code in the directory C:\xampp\htdocs\ with name codescracker.php, then open browser and type localhost/codescracker.php. Here is the output produced by the above example code:

After doing all the above things, you have created a MySQL table using PHP with MySQLi.

Now, let's create another table named users in database named codescracker. This table will use in signup and login page tutorial.

<?php 
	$conn = mysqli_connect('localhost', 'root', '', 'codescracker');
	$qry = "CREATE TABLE users (
				id int(10) NOT NULL AUTO_INCREMENT,
				datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
				username varchar(40) NOT NULL,
				password varchar(40) NOT NULL,
				emailid varchar(40) NOT NULL,
				fullname varchar(40) NOT NULL,
				sex varchar(6) NOT NULL,
				dob date NOT NULL,
				PRIMARY KEY	(id)
			);";
	$res = $conn->query($qry);
	if($res)
	{
		echo "table created successfully";
	}
	else 
	{
		echo "error occurred";
	}
?>

PHP Online Test

« Previous Tutorial Next Tutorial »