Code has been added to clipboard!

MySQL Insert Into Functions: When, Why and How to Use Them

Reading time 2 min
Published Aug 8, 2017
Updated Oct 15, 2019

You already know how to free up your database space by deleting rows from your tables that you no longer plan to use. Still, that space won't stay vacant for long. Soon you will have new data to insert into MySQL tables you have.

In this tutorial, we will explain to you how to insert new data into your database tables using MySQL insert into statement. We will also cover correct usage of syntax to insert MySQL table rows. You will also find three code examples, just so you are covered no matter which type of MySQL connection you are using.

MySQL Insert Into: Main Tips

  • A database and at least one table must be created before we can insert MySQL data there.
  • To add new data pieces into the database, we use MySQL insert into statement.
  • The queries of SQL must be quoted in PHP code.
  • Text strings in SQL queries must be quoted. Numbers and NULL values must not be quoted (otherwise, they will be treated as text).

Correct Syntax for PHP MySQL Inserts

Take a look at how the correct syntax of MySQL insert into function should look:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

If a particular column is auto increment or timestamp, you should not include it in the SQL query. MySQL will add the value automatically.

Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid certificates of completion
Coursera
Pros
  • Professional service
  • Flexible timetables
  • A variety of features to choose from
Main Features
  • Professional certificates of completion
  • University-level courses
  • Multiple Online degree programs
Datacamp
Pros
  • Great user experience
  • Offers quality content
  • Very transparent with their pricing
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable

Insert MySQL Rows: Examples

Looking at the examples below, you can see how to use MySQL insert into function using PHP. The difference among these scripts is the MySQL connection type you chose: they are meant for MySQLi object oriented, MySQLi procedural and PDO connections, respectivelly. All of them let you easily execute PHP MySQL inserts:

Example
<?php
  $servername = 'localhost';
  $username = 'name_of_the_user';
  $password = 'users_password';
  $dbname = 'myDatabase';

  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);
  // Check connection
  if ($conn->connect_error) {
    die("Connection went wrong: " . $conn->connect_error); 
  } 
  $sql = "INSERT INTO MovieList (title, rating, price) VALUES ('random_movie', 'good', '5')";
  if ($conn->query($sql) === TRUE) {    
    echo "Inserted successfully";
  } else {   
    echo "Error: " . $sql . "<br>" . $conn->error;
  }
  $conn->close();  
?>
Example
<?php
  $servername = 'localhost';
  $username = 'name_of_the_user';
  $password = 'users_password';
  $dbname = 'myDatabase';

  // Create connection  
  $conn = mysqli_connect($servername, $username, $password, $dbname); 

  // Check connection  	
  if (!$conn) {      
     die("Connection went wrong: " . mysqli_connect_error());
  }

  $sql = "INSERT INTO MovieList (title, rating, price) VALUES ('random_movie', 'good', '5')";
  if (mysqli_query($conn, $sql)) {
    echo "Inserted successfully";
  } else {
    echo "Error: ". $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);
  }
?>
Example
<?php
  $servername = 'localhost';
  $username = 'name_of_the_user';  
  $password = 'users_password';
  $dbname =   'myDatabase';

  try {    
    $conn = new PDO("mysql:host=$servername; dbname=$dbname", $username, $password); 
    // set the PDO error mode to exception    
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO MovieList (title, rating, price) VALUES ('random_movie', 'good', '5')"; 
 	  // use exec() because no results are returned
    $conn->exec($sql); 
    echo "Inserted successfully";
  } catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();  
  }  	
  $conn = null;  
?>

MySQL Insert Into: Summary

  • If you wish to insert data into your database, you must have at least one table already up and ready to use.
  • New data is inserted using MySQL insert into statement.
  • Quoted values in SQL queries get treated as text, so be careful to only quote strings and not numerical entires or null values.

Latest Udacity Coupon Found:

Verified STAFF PICK

75% OFF COURSES

Udacity Black Friday Offer

The best time to save on Udacity courses is now - follow this coupon to access a 75% Udacity Black Friday discount & enjoy learning at a very low cost!

Expiration date: 30/01/2021
3176 People Used
Only 97 Left
Rating
5.0