Understanding the Concept of Insert Query in PHP

How to Build a Database and Tables in INSERT Query in PHP

You must first understand how to build a database in MySQL before proceeding with this Insert Query in PHP article. 

In PHP, we use the INSERT INTO statement to add new rows to a database table. By passing mysqli query to PHP, we can run the insert query(). We use mysqli multi query to execute multiple queries in a single call because mysqli query cannot execute multiple queries to avoid SQL injections. Before we go any further, please remember the following syntax.

• Embed into a table syntax: VALUES (value1, value2,...valueN) INTO TABLE NAME (column1, column2,... columnN);
• mysqli query() has the following syntax: mysqli query(connection,query,resultmode);
• mysqli multi query() has the following syntax: mysqli multi query(connection,query);

Now that we've covered the fundamentals, let's look at the pointers that will be covered in this article on Insert Query in PHP tutorial.

• Use of insert statement
• How to execute the insert query
• How to insert data into MYSQL using MySQLi Object-oriented Procedure with example
• How to insert data into MYSQL using MySQLi Procedural Procedure with example
• How to insert multiple records into MySQL using MySQLi Object-oriented Procedure with example
• How to insert multiple records int MySQL using MySQLi Procedural Procedure with example

Use of Insert Statement

We saw how to build a database and tables in insert query in PHP. In this section we will learn how to run a SQL query to insert records into a table. 

In a database table, the INSERT INTO statement is used to insert new rows. Let's create a SQL query with acceptable values using the INSERT INTO argument, and then execute it by passing it to the PHP mysql query() function to insert data into the table. 

Here's an example for insert query in PHP in which you assign values for the first name, last name, and email fields to add a new row to the persons table.

• Title – This is your column's name. It will be mirrored on the top of the table.
• Data type - This is the data form you've chosen. You can use int, varchar, string, and many other types of data. We chose varchar, for example, because we need to enter a string style name (which uses letters, not numbers).
• Length/Values – This specifies the maximum length for your entry in this column.
• For our “ID” sector, we used the “Primary” index. It is recommended that a table only have one ID column. It is required when configuring table relationships and is used to enumerate table entries. We also wrote "A I," which stands for "Auto Increment." This will enumerate the entries (1,2,3,4,...) automatically.

Prepare Yourself to Answer All Questions!

Automation Testing Masters ProgramExplore Program

How to Execute the Insert Query

We may begin by adding data to a database and a table after they have been created with insert query in PHP.

Here are several guidelines to obey in terms of syntax:

• In PHP, the SQL query must be quoted
• The SQL query's string values must be quoted
• It is not necessary to quote numerical values
• It is not necessary to quote the word NULL

To Add New Records to a MySQL Table, Use the Insert Into Statement:

• The returned error codes vary depending on whether you're using MySQL Native Driver (mysqlnd) or insert query in PHP Client Library when you pass a statement to mysqli query() that's longer than the server's max_allowed_packet (libmysqlclient). The following is the behavior:
• On Linux, mysqlnd returns an error code of 1153. The error message indicates that a packet was received that was larger than the max_allowed_packet bytes.
• On Windows, mysqlnd returns the error code 2006. The server has gone down, according to this error message.
• The error code 2006 is returned by libmysqlclient on all platforms. The server has gone down, according to this error message.

Explain How to Insert Data Into MySQL Using MySQLi Object-Oriented Procedure With Example

Since it inserts data into the MySQL database, this is the most important line of PHP code. The INSERT INTO statement is used to insert data into a database table. 

In this example, we're populating the table Students with information.

Further, we have the table column names with insert query in PHP listed between the parenthesis to where we want to add the values: (name, lastname, email). The data will be inserted in the order that you specify. Values would be inserted in the wrong order if we wrote (email, lastname, name).

The VALUES statement is the next step. Here we define the values that will be inserted into the columns that were previously specified. As a result, each column represents a distinct value. In our case, the format will be: name = Test, lastname = Testing, email = Testing@testing.com.

Another point worth noting is that we only used PHP code to run a SQL query. Between the quotations, SQL queries must be entered. Anything between the quotes and written after $sql = in our example is a SQL query.

Example

<?php  

$host = 'localhost:3306';  

$user = '';  

$pass = '';  

$dbname = 'test';  

$conn = mysqli_connect($host, $user, $pass,$dbname);  

if(!$conn){  

  die('Could not connect: '.mysqli_connect_error());  

}  

echo 'Connected successfully<br/>';  

$sql = 'INSERT INTO emp4(name,salary) VALUES ("sonoo", 9000)';  

if(mysqli_query($conn, $sql)){  

 echo "Record inserted successfully";  

}else{  

echo "Could not insert record: ". mysqli_error($conn);  

}  

mysqli_close($conn);  

?>  

Output

Explain how to insert data into MYSQL using MySQLi Procedural Procedure with example

Explain how to insert multiple records into MySQL using MySQLi Object-oriented Procedure with example

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program

Explain How to Insert Multiple Records in MySQL Using MySQLi Procedural Procedure With Example

SQL statements, specifically the insert query in PHP command, are used to write data to a database. The INSERT command is simple: it inserts data into the database. When you use phpMyAdmin, you get a graphical user interface to control your database, but it also shows you the MySQL commands it used to complete your tasks. We'll take advantage of this function to locate the correct code. We'll use phpMyAdmin to insert a test statement and then copy the INSERT command it used.

• Click the phpMyAdmin icon in your cPanel after logging in.
• Click your database's name in the left menu, then the table you want to deal with in the right menu. If you follow our lead, you'll first select "_mysite" and then "comments."
• Select “Insert” from the top menu.
• Click GO after typing in a sample comment 

After we've created a sample query, we'll need to tweak it and run it after a user has left a comment. This is an example of code that will accomplish this. Any line that starts with / is a comment in PHP (in case you're not familiar with the language). It's designed for programmers to leave feedback on what their code does so that they or other people working on the code have a better understanding of what it's doing. We've added comments to the example below to illustrate what these pieces of code are doing:

<? if( $_POST ) { $con = mysql_connect("localhost","inmoti6_myuser","mypassword"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("inmoti6_mysite", $con); 

$users_name = $_POST['name']; $users_email = $_POST['email']; $users_website = 

$_POST['website']; $users_comment = $_POST['comment']; $users_name = 

mysql_real_escape_string($users_name); $users_email = 

mysql_real_escape_string($users_email); $users_website = 

mysql_real_escape_string($users_website); $users_comment = 

mysql_real_escape_string($users_comment); $articleid = $_GET['id']; if( ! is_numeric($articleid) 

) die('invalid article id'); $query = " INSERT INTO `inmoti6_mysite`.`comments` (`id`, `name`, `email`, `website`, `comment`, `timestamp`, `articleid`) VALUES (NULL, '$users_name', 

'$users_email', '$users_website', '$users_comment', CURRENT_TIMESTAMP, '$articleid');"; 

mysql_query($query); echo "<h2>Thank you for your Comment!</h2>"; mysql_close($con); } ?>

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Conclusion 

In this tutorial, we learned about insert query in PHP, use of insert statement and how it is executed. We saw how to insert data and multiple records into MYSQL using MySQLi Object-oriented Procedure and Procedural Procedure with examples.

Want to learn more about the concept? You must enroll in the Simplilearn’s Full Stack Web Development course. This course will help you find grip over the concept. And if you want to skill-up your expertise, you can check out Simplilearn’s skill up platform that offers several free online courses that are focused on building strong foundational skills for career growth.

If you have any queries or suggestions for us, please mention them in the comment box and our experts will answer them for you as soon as possible. 

Come be a part of a great learning experience!