How to create a MySQL table

Content:

What is a MySQL table?
How to add a new table using PHPMyAdmin
How to create a table via the SQL command line
How to create a MySQL table using a PHP script
Resources

What is a MySQL table?

The secret of storing information on websites in such an orderly fashion is that SQL databases, and the most popular of them - the MySQL databases, in particular, are made up of tables. Each MySQL table consists of columns and rows - just like the graphic table we know very well. The columns specify the separate categories of the table entries, while the rows contain the actual information about the objects that need to be made an inventory of.

How to add a new table using PHPMyAdmin

The MySQL table creation process is now easier than ever with the help of the phpMyAdmin interface. With any of the hosting plans offered by NTC Hosting you get this MySQL management tool readily integrated into your web hosting Control Panel. To access the tool - simply log into your Control Panel, go to the MySQL Databases section, select the database you wish to create a table for and click on its corresponding phpMyAdmin icon. Now you can start the 'Create new table on database' wizard by entering your MySQL table name and the number of fields for this particular table and hitting the 'Go' button. This will take you to the field set-up step, where you should name all fields one by one in the 'Field' column, select their types from the 'Type' drop-down menu and enter a value for the length of each field. Then you should assign 'Attributes' to each field, select a null option from the 'Null' drop-down list and define whether the field is a primary key or not. The last actions required from you will be to select the table type and collation method and then hit the 'Save' button to complete your table creation. For more details please check the video tutorial below:

A video tutorial on how to create a table using the Control Panel and PHPMyAdmin

How to create a table via the SQL command line

Unfortunately, there are web hosting provides which do not allow connections to their database servers through the PHPMyAdmin tool. In such cases the developers need to establish a connection via SSH and to use the SQL command line to create a table. For example, we will create a table 'recipes' which will have three fields for 'ingredient', 'quantity' and 'mtype' (for measurement type).

Note: For a detailed tutorial on how to establish an SSH connection to a MySQL host and how to log in a database, please check the 'MySQL' and ‘MySQL create database' articles

An example of How to Create Table in MySQL

CREATE TABLE recipes(ingredient VARCHAR(32), quantity VARCHAR(32), mtype VARCHAR(32), address VARCHAR(64));

Once your database is created it is a good idea to add some data in it. To do so you need to use the INSERT statement and insert the data into the 'recipes' table (more details in the MySQL Insert article).

An example of how to Insert data into MySQL table

INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America'); (inserts data into phonebook table)

When we need to call the data from our table we can use the SELECT statement. The following example will display all the data from the ingredient field

An example of how to Select data from MySQL table (1)

SELECT * FROM recipes ORDER BY ingredient;

To make our example more complex we will call the data which contains 'chicken' in the ‘ingredient’ field:

An example of how to Select data from MySQL table (2)

SELECT * FROM recipes WHERE ingredient = 'chicken';

If we need to call all the data which contains '350' as 'quantity' and 'grams' as 'measurement type', then our query will look like this:

An example of how to Select data from MySQL table (3)

SELECT * FROM recipes WHERE quantity = '350' AND mtype = 'grams';

If we decide to update our recipe data we need to run a simple query by using the UPDATE statement

An example of how to Update data in MySQL database

UPDATE recipes SET ingredient = 'fish' WHERE quantity = '350' AND mtype = 'grams';

In order to remove an ingredient from our recipe we will delete all records which contain 'chicken' and 'grams' in the 'ingredient' and 'mtype' fields.

An example of how to Delete data from MySQL database

An example of how to Delete data from a MySQL database DELETE FROM recipes WHERE ingredient = 'chicken' AND mtype = 'grams';

How to create a MySQL table using a PHP script

If you are trying to create a PHP website, the question - How to connect a MySQL database to a PHP script - arises. As we know, PHP5 and MySQL5 work together quite well, so, we will do a quick overview of the most basic interactions of these popular open source projects.

First, you need to have a MySQL database. To create a database you can use an SQL command line or the PHPMyAdmin tool in your web hosting account. If you are using NTC Hosting's services, you can create a database even easier - by using the 'MySQL Databases' management tool in your web hosting Control Panel.

For our example's purposes we will create a database 'mydb_name' and a user 'db_user' with a password 'db_passwd'. Then we will create a table 'phonebook' in the database using the PHPMyAdmin tool or the SQL command line. The 'phonebook' MySQL table will have fields for 'firstname', 'lastname', 'phone' and 'address'. If you need more information on how to create a database, a user, or on how to set a password, please visit the MySQL create database and MySQL create user articles.

Once we've created the database we need to establish a connection between our PHP script and the MySQL database. We do that using this code:

An example of how to connect a PHP script to a MySQL Database

<?php
// Connects to your Database
mysql_connect("mysql.hostname.com", "db_user", "db_passwd") or die(mysql_error());
mysql_select_db("mydb_name") or die(mysql_error());
?>

Once connected to the database let’s try to retrieve some information from the ‘phonebook’ MySQL table we have previously created

An example of how to SELECT data from a MySQL Table

// Collects data from "friends" table
$data = mysql_query("SELECT * FROM phonebook")
or die(mysql_error());

Then we will put this information into an array so that it can be displayed in our PHP application:

An example of how to fetch MySQL data to an array in PHP

// puts the "phonebook" info into the $info array
$info = mysql_fetch_array( $data );

Then we can print the data so that it can be displayed in our PHP application:

An example of how to Print MySQL data from an array in PHP

// Print out the contents of the entry
Print "First Name: ".$info['firstname'] . " ";
Print "Last Name: ".$info['lastname'] . " ";
Print "Phone Number: ".$info['phone'] . " ";
Print "Address: ".$info['address'] . " ";

However, this will only give us the first entry in our database. In order to retrieve all the information, we need to make this a loop. Here is an example:

An example of how to Print MySQL data from an array in PHP(2)

while($info = mysql_fetch_array( $data ))
{
Print "First Name: ".$info['firstname'] . " ";
Print "Last Name: ".$info['lastname'] . " ";
Print "Phone Number: ".$info['phone'] . " ";
Print "Address: ".$info['address'] . " ";
}

Now let's create a complete script which connects to the database and fetches and prints the data from the 'phonebook' table:

An example of how to connect PHP and MySQL and print the data in the table

<?php
// Connects to your Database
mysql_connect("mysql.hostname.com", "db_user", "db_passwd") or die(mysql_error());
mysql_select_db("mydb_name") or die(mysql_error());
$data = mysql_query("SELECT * FROM phonebook")
or die(mysql_error());
Print "<table border cellpadding=4>";
while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<th>First Name:</th> <td> ".$info['firstname'] . "</td> ";
Print "<th>Last Name:</thb> <td>".$info['lastname'] . " </td>";
Print "<th>Phone Number:</th> <td>".$info['phone'] . " </td>";
Print "<th>Address:</th> <td>".$info['address'] . " </td>";
}
}
Print "</table>";
?>

Now we know how to connect a PHP script to our database. Also, we’ve learned how to display data from a MySQL table in a PHP application. Now it's time to get things more complex and learn how to create a MySQL table using the PHP script, and then how to use PHP to insert data in a MySQL table. To create a table in MySQL by using a PHP script, we will follow the same structure as before. First, we will connect to a database, and then we will execute a MySQL query to create a new table. At the end we will print a message. In our example we will use a PHP script to create a table 'moredata' which will contain info about 'gender', 'age' and 'country' in the corresponding fields.

An example of how to create a MySQL table using a PHP script:

<?php
// Connects to your Database
mysql_connect("mysql.hostname.com", "db_user", "db_passwd") or die(mysql_error());
mysql_select_db("mydb_name") or die(mysql_error());
mysql_query("CREATE TABLE moreinfo ( gender VARCHAR(30),
age INT, country VARCHAR(30))");
Print "The table has been created";
?>

At the end of our PHP / MySQL interaction tutorial we will use an INSERT MySQL query with a PHP script to input some data in our newly created database table.

<?php
// Connects to your Database
mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error()); mysql_select_db("Database_Name") or die(mysql_error());
mysql_query("INSERT INTO tablename VALUES ( 'Bill', 29, 'Ford' ), ( 'Mike', 16, 'Beetle' ), ( 'Alisa', 36, 'Van' )");
Print "Your table has been populated";
?>

Resources:

MySQL create table