Home › MySQL Tutorial

Create MySQL Table in PHP

Create MySQL Table: mysql_query()

This section is just for the completeness of the tutorial. In actual scenario, the use of phpmyadmin is recommended. Phpmyadmin frees you from the burden of writing scripts for creating a mysql table.

Following the Common Steps to execute any SQL statements in PHP, we define a string variable “$sql_statement” which contains the actual SQL statement to create a table. This is explained as below.

$sql_statement = “Create table userinfo(
userid varchar(10) not null,
password varchar(41) not null
)”;
After this, we execute this query using the PHP inbuilt function mysql_query () as explained below.

mysql_query ($sql_statement, $conn);

As explained earlier, $conn is the database connection string retrieved using the mysql_connect() function.

Hence the complete code to create a mysql table would be as below.

$conn = mysql_connect ("host","userid", "password");
mysql_select_db("user")
or die("Could not connect to database");

$sql_statement = “Create table userinfo(
userid varchar(10) not null,
password varchar(41) not null
)”;

mysql_query($sql_statement,$conn)
or die(“Could not create table”);

After the execution of this statement, a mysql table will be created. This table will have two fields “userid” and “password” of data type varchar. The lengths of these fields will be 10 and 41 respectively.

Example to create a MySQL Table

This source code creates a webform. The user is allowed to enter the name of the database where the table is to be created and the name of the table he wishes to create. This web form is processed and the table is created in the sepecified database. Error is thrown if anything goes wrong. The name of this file is "create_table.php" Try it yourself!

<?php
//Retrieve the database and table name
$database = $_GET["db"];
$table = $_GET["table"];

$fld1 = $_GET["fld1"];
$fld1_type = $_GET["fld1_type"];
$fld1_length = $_GET["fld1_length"];

$fld2 = $_GET["fld2"];
$fld2_type = $_GET["fld2_type"];
$fld2_length = $_GET["fld2_length"];

//Continue if database name is non blank
if($database!="" && $table!="")
{
//Prepare the SQL Statement
$sql_statement = "Create table ".$table."(".
$fld1." ". $fld1_type."(". $fld1_length.")" not null,
$fld2." ". $fld2_type."(". $fld2_length.")" not null
)";

//Connect to the MySQL Database
$conn = mysql_connect ("host","userid", "password");;

//Select the database
mysql_select_db($database,$conn);
//Execute query
mysql_query($sql_statement,$conn) or die("Could not create table");
echo "Table ".$table. " created successfully in database ". $database;
}
?>
<html>
<head>
<title>Create A Database</title>
</head>
<body>
<form action="create_table.php">
<table align=center>
<tr><td>Database Name:</td><td><input type=text name=db></td></tr>
<tr><td>Table Name:</td><td><input type=text name=table></td></tr>
<tr><td>Field Name:</td><td><input type=text name=fld1></td></tr>
<tr><td>Data Type:</td><td><input type=text name=fld1_type></td></tr>
<tr><td>Field Length:</td><td><input type=text name=fld1_length></td></tr>
<tr><td>Field Name:</td><td><input type=text name=fld2></td></tr>
<tr><td>Data Type:</td><td><input type=text name=fld2_type></td></tr>
<tr><td>Field Length:</td><td><input type=text name=fld2_length></td></tr>
</form>
</body>
</html>

  • 792 reads