Creating tables by query

We can create new table by using Query. You can seen how to execute a query and get the records or modify the records in MySQL. Same way we can run one sql command to create tables.

$q="CREATE TABLE `sample_tb` (`empno` VARCHAR( 6 ) NOT NULL) ";

The above query will create a table sample_tb and add one column empno to it. But note that we have to execute the above code. After execution we can find out whether the query has successfully executed or not by using one if condition. If the query is not executed successfully then we will print the error message. If table create process is successful then we will display a success message. Here is the code

$query="CREATE TABLE `sample_tb` (`empno` VARCHAR( 6 ) NOT NULL) ";
if ($connection->query($query)) {
echo "created table sample_tb....";
}else{
echo $connection->error;
}

$connection stores mysqli connection details, declared at config file

As you can see we have created the table, with one column to store the data. Now what happens if the table sample_tb already exists and we will try to create again? The system will generate an error message.

Table 'sample_tb' already exists

So before trying to create the table we will delete the table and then create again. ( Note: do this if your requirement is there ). We also can't delete the table without checking the table is there or not. So we will use one if exists command like this.

$query="DROP table  if exists sample_tb"; 
if ($connection->query($query)) {
echo "Table sample_tb deleted  ....";
}else{
echo $connection->error;
}

We can easily execute the create table query to generate the table. Note that this combination is used inside many scripts where temporary tables are create and deleted at the end.

How to check if table is created or not ?

We can use SHOW TABLE query to list the tables in database.
(Our database name is sql_tutorial )

if($stmt = $connection->query("SHOW TABLES LIKE 'sample_tb' ")){
 if($stmt->num_rows ==1 ){
	echo "table is created ";
  }else{
	echo $connection->error;
}
}

This article is written by plus2net.com team.

plus2net.com

More on Alter or coy table , add update or delete records in SQL

Adding records to a table by insert command
Adding records with set option
Changing structure of the table by alter query
Create table query with if exists sql with php script
Creating a new table by using data from one table
Change the name of a table
Copying data from one table to another table
Delete records from a table with conditions
Delete table by using DROP sql
Update SQL commands
Inserting SUM, AVG data from one table column to other using group by command
Listing all tables and Database present in MySQL
Table Records are downloaded as .csv file

mobo	03-12-2009
can you please tell me how can i copy a table from one user to another in mysqlplus. Many thanks

dan	02-06-2012
Thank you very very much!

Queries INSERT UPDATE

Aggregate Queries AVG ( Average ) SUM MIN MAX

SQL Video Tutorials

Creating tables by query

How to check if table is created or not ?

Subscribe