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

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.

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:

<?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();  
?>

<?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);
  }
?>

<?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.