PHP - SPLessons

PHP Insert Multiple Records Into MySQL

Chapter 36

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PHP Insert Multiple Records Into MySQL

PHP Insert Multiple Records Into MySQL

shape Description

Insert is the process of inserting data into the table. INSERT statement adds new rows into already present table. The present chapter “PHP Insert Multiple Records Into MySQL” shows how to insert various records into the Database.

Multiple SQL statements uses mysqli_multi_query() function to execute.

shape Syntax

INSERT INTO table_name(column1,column2,…columnN) VALUES(value1,value2,…valueN);

shape Example

MySQLi (Object-oriented)

<!DOCTYPE html>
<html>
    <body>
        <?php 
            $HostName = "localhost"; 
            $UserName = "root"; 
            $Password = "password"; 
            $dbname="accounts"; 
             
            // Create connection 
            $connection = new mysqli($HostName, $UserName, $Password, $dbname); 
             
            // Check connection 
            if ($connection->connect_error) 
            {
                die("Connection failed: " . $connection->connect_error);
            } 
 
            //Inserting records into the database
            $sql = "INSERT INTO users2 (Username, Password, First, Last)
            VALUES ('Adam', 'password1', 'Adam', 'Gilchrist'),
             ('John', 'password2', 'John', 'Kennedy'),
            ('Michael', 'password3', 'Michael', 'Jackson')";
 
            if ($connection->multi_query($sql) === TRUE) 
            {
                echo "New records created successfully";
            } 
            else
            {
                echo "Error: " . $sql . "<br>" . $connection->error;
            }
 
            $connection->close();
        ?>
    </body>
</html>

The output will look like below in phpmyadmin.

MySQLi Procedural

<!DOCTYPE html>
<html>
	<body>
		<?php
			$servername = "localhost";
			$username = "root";
			$password = "password";
			$dbname = "Student";

			// Create connection
			$connection = mysqli_connect($servername, $username, $password, $dbname);
			
			// Check connection
			if (!$connection) {
				die("Connection failed: " . mysqli_connect_error());
			}
			
				//Inserting records into the database
				$sql = "INSERT INTO users2 (Username, Password, First, Last)
				VALUES ('Adam', 'password1', 'Adam', 'Gilchrist'),
				 ('John', 'password2', 'John', 'Kennedy'),
				('Michael', 'password3', 'Michael', 'Jackson')";

			if (mysqli_multi_query($connection, $sql)) {
				echo "New records created successfully";
			} else {
				echo "Error: " . $sql . "<br>" . mysqli_error($connection);
			}

			mysqli_close($connection);
		?>
	</body>
</html>

Summary

shape Key Points

PHP Insert Multiple Records Into MySQL illustrates following important points.

  • mysqli_multi_query() function inserts multiple records into the table.
  • When adding a new row, the datatype of the value and the column should match.