MySQL Insert

What is MySQL insert?

MySQL Insert is a process of inserting information into a MySQL table. To do so, you have to first create the respective table. It must have column(s) of the same type as that of the content you'd like to insert. Thus, you can't insert a 300-word article in a column which accepts only integers.

How to Add Data in a MySQL table

The MySQL Insert process can be carried out in several ways: through a direct command from the command line of the MySQL console, via the web-based graphic interface PHPMyAdmin, and by using a PHP script (inserting data and scripts written in other languages - Perl, Python, etc., is possible as well). All MySQL insert methods are characterized with the following elements:

1. A connection with the MySQL server is established;
2. A connection with the MySQL Database is established with administrator rights enabled;
3. A MySQL query is executed, which shows what data is to be inserted in which columns and in which table.

Now let's for example insert a name and a phone in a phonebook table using the SQL command line:

Insert data in MySQL using the SQL command line

As described above, we need to accomplish three steps to insert new data in a MySQL table. First, let’s log into the MySQl server. To do so, we need to establish an SSH connection to the MySQL server and to log in using the following command:

An example of how to log in to a MySQL server

$mysqladmin -u db_user password db_passwd

You need to replace db_user and db_passwd with your username and password settings. Also, you can use a single line to connect to both the MySQL server and the database. Here is the example:

An example of how to log in to a MySQL database

$mysqladmin -u db_user password db_passwd db_name

Again you need to replace db_user, db_passwd and db_name with your username, password and database name

Now we need to run a MySQL query to insert the new data in the corresponding columns. You can see the syntax of the query above:

An example of MySQL Insert query's syntax:

INSERT INTO tablename (col1, col2) VALUES('data1', 'data2' )

Now let's see the INSERT statement in action with a real, practical example

An example of how to INSERT data in a MySQL table:

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

This query will insert the phone, name and address data from the values section into the corresponding columns of the phonebook MySQL table.

Insert data using our Control Panel-inbuilt PHPMyAdmin tool

To facilitate its clients to the maximum, NTC Hosting offers a MySQL hosting service, which comes with a control panel-integrated MySQL Database manager via which all our clients can automatically log in their MySQL databases and insert the respective information or import a MySQL dump file using the popular PHPMyAdmin interface. To see how easy it is to insert data in MySQL using our Control Panel and PHPMyAdmin, please check the video below:

An example of how to insert data using PHPMyAdmin (video)

Insert data in MySQL using a PHP script

PHP is a server-side scripting language, which enables the developers to create dynamic web pages, insert PHP code in an HTML code, and easily connect their PHP-based web applications to a MySQL database. Both PHP and MySQL are open-source projects, which makes them widely used as a base for other applications with an open license. Also, both projects are a part of the most commonly used web hosting platform - LAMP (Linux-Apache-MySQL-PHP).

To insert data using a PHP script we need to use the following 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("db_name") or die(mysql_error());
?>

The code in the example above will connect your script to a MySQL server (mysql.hostname.com) using the login credentials of user 'db_user'. Then it will select the database (db_name). Once connected to the database, let’s try to insert some information in the MySQL table:

An example of how to INSERT data in a MySQL Table using PHP

<?php
// Connects to your Database
mysql_connect("mysql.hostname.com", "db_user", "db_passwd") or die(mysql_error());
mysql_select_db("db_name") or die(mysql_error());
mysql_query("INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America')");
Print "Your table has been populated";
?>

Resources:

• MySQL insert