codescracker


php

PHP and MySQLi Create Table



« Previous Tutorial Next Tutorial »


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

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:

  1. Open XAMPP
  2. Start Apache and MySQL Module
  3. Click on Admin button of MySQL module
  4. Click on codescrackerTwo in the left pannel of phpMyAdmin Window
  5. Enter name of table say codescrackerTable
  6. Choose the total number of column
  7. Click on Go button
  8. Fill all the four column's detail
  9. 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 4th action

php mysqli create table

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

create mysql table php

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 9th (last) action

create table php mysqli

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

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:

php mysqli create table example

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";
	}
?>

« Previous Tutorial Next Tutorial »








Tools
Calculator

Quick Links
Signup - Login - Give Online Test