Add Record Form

PHP MySQL INSERT Query

In this tutorial you will learn how to insert records in a MySQL table using PHP.

Inserting Data into a MySQL Database Table

Now that you’ve understood how to create database and tables in MySQL. In this tutorial you will learn how to execute SQL query to insert records into a table.

The INSERT INTO statement is used to insert new rows in a database table.

Let’s make a SQL query using the INSERT INTO statement with appropriate values, after that we will execute this insert query through passing it to the PHP mysqli_query() function to insert data in table. Here’s an example, which insert a new row to the persons table by specifying values for the first_name, last_name and email fields.

Example

 // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')"; if(mysqli_query($link, $sql)) < echo "Records inserted successfully."; >else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>

connect_error); > // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')"; if($mysqli->query($sql) === true) < echo "Records inserted successfully."; >else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt insert query execution try< $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')"; $pdo->exec($sql); echo "Records inserted successfully."; > catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>

If you remember from the preceding chapter, the id field was marked with the AUTO_INCREMENT flag. This modifier tells the MySQL to automatically assign a value to this field if it is left unspecified, by incrementing the previous value by 1.

Inserting Multiple Rows into a Table

You can also insert multiple rows into a table with a single insert query at once. To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row must be enclosed within parentheses and separated by a comma.

Let’s insert few more rows into the persons table, like this:

Example

 // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('John', 'Rambo', 'johnrambo@mail.com'), ('Clark', 'Kent', 'clarkkent@mail.com'), ('John', 'Carter', 'johncarter@mail.com'), ('Harry', 'Potter', 'harrypotter@mail.com')"; if(mysqli_query($link, $sql)) < echo "Records added successfully."; >else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>

connect_error); > // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('John', 'Rambo', 'johnrambo@mail.com'), ('Clark', 'Kent', 'clarkkent@mail.com'), ('John', 'Carter', 'johncarter@mail.com'), ('Harry', 'Potter', 'harrypotter@mail.com')"; if($mysqli->query($sql) === true) < echo "Records inserted successfully."; >else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt insert query execution try< $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('John', 'Rambo', 'johnrambo@mail.com'), ('Clark', 'Kent', 'clarkkent@mail.com'), ('John', 'Carter', 'johncarter@mail.com'), ('Harry', 'Potter', 'harrypotter@mail.com')"; $pdo->exec($sql); echo "Records inserted successfully."; > catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>

Now, go to phpMyAdmin ( http://localhost/phpmyadmin/ ) and check out the persons table data inside demo database. You will find the value for the id column is assigned automatically by incrementing the value of previous id by 1.

Note: Any number of line breaks may occur within a SQL statement, provided that any line break does not break off keywords, values, expression, etc.

Insert Data into a Database from an HTML Form

In the previous section, we have learned how to insert data into database from a PHP script. Now, we’ll see how we can insert data into database obtained from an HTML form. Let’s create an HTML form that can be used to insert new records to persons table.

Step 1: Creating the HTML Form

Here’s a simple HTML form that has three text fields and a submit button.

Example

        
First Name: 
Last Name: 
Email Address:  

Step 2: Retrieving and Inserting the Form Data

When a user clicks the submit button of the add record HTML form, in the example above, the form data is sent to ‘insert.php’ file. The ‘insert.php’ file connects to the MySQL database server, retrieves forms fields using the PHP $_REQUEST variables and finally execute the insert query to add the records. Here is the complete code of our ‘insert.php’ file:

Example

 // Escape user inputs for security $first_name = mysqli_real_escape_string($link, $_REQUEST['first_name']); $last_name = mysqli_real_escape_string($link, $_REQUEST['last_name']); $email = mysqli_real_escape_string($link, $_REQUEST['email']); // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')"; if(mysqli_query($link, $sql)) < echo "Records added successfully."; >else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>

connect_error); > // Escape user inputs for security $first_name = $mysqli->real_escape_string($_REQUEST['first_name']); $last_name = $mysqli->real_escape_string($_REQUEST['last_name']); $email = $mysqli->real_escape_string($_REQUEST['email']); // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')"; if($mysqli->query($sql) === true) < echo "Records inserted successfully."; >else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt insert query execution try< // Create prepared statement $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)"; $stmt = $pdo->prepare($sql); // Bind parameters to statement $stmt->bindParam(':first_name', $_REQUEST['first_name']); $stmt->bindParam(':last_name', $_REQUEST['last_name']); $stmt->bindParam(':email', $_REQUEST['email']); // Execute the prepared statement $stmt->execute(); echo "Records inserted successfully."; > catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>

In the next chapter we will extend this insert query example and take it one step further by implementing the prepared statement for better security and performance.

Note: The mysqli_real_escape_string() function escapes special characters in a string and create a legal SQL string to provide security against SQL injection.

This is very basic example of inserting the form data in a MySQL database table. You can extend this example and make it more interactive by adding validations to the user inputs before inserting it to the database tables. Please check out the tutorial on PHP form validation to learn more about sanitizing and validating user inputs using PHP.

Источник

Sql php insert post

В прошлой теме мы добавили в базу данных таблицу Users с тремя столбцами id, name, age со следующим определением:

CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER)

Теперь добавим в нее данные. Для добавления данных в MySQL применяется команда INSERT :

INSERT INTO название_таблицы (столбец1, столбец2, столбецN) VALUES ( значение1, значение2, значениеN)

Объектно-ориентированный подход

connect_error)< die("Ошибка: " . $conn->connect_error); > $sql = "INSERT INTO Users (name, age) VALUES ('Tom', 37)"; if($conn->query($sql)) < echo "Данные успешно добавлены"; >else< echo "Ошибка: " . $conn->error; > $conn->close(); ?>

connect_error)< die("Ошибка: " . $conn->connect_error); > $sql = "INSERT INTO Users (name, age) VALUES ('Sam', 41), ('Bob', 29), ('Alice', 32)"; if($conn->query($sql)) < echo "Данные успешно добавлены"; >else< echo "Ошибка: " . $conn->error; > $conn->close(); ?>

Процедурный подход

 $sql = "INSERT INTO Users (name, age) VALUES ('Tom', 37)"; if(mysqli_query($conn, $sql)) < echo "Данные успешно добавлены"; >else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); ?>

 $sql = "INSERT INTO Users (name, age) VALUES ('Sam', 41), ('Bob', 29), ('Alice', 32)"; if(mysqli_query($conn, $sql)) < echo "Данные успешно добавлены"; >else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); ?>

Добавление данных из формы HTML

В большинстве случаев добавляемые данные будут приходить из вне, например, присылаться в запросе пользователя. Рассмотрим добавление данных, отправленных из формы HTML. Для этого определим веб-страницу form.html , на которой определим следующий код:

     
Добавление пользователя
 
Имя: 
 
Возраст: 
   

Здесь определены два поля ввода. И по нажатию на кнопку их данные в запросе POST будут уходить скрипту create.php . Теперь определим сам скрипт create.php .

Объектно-ориентированный подход

connect_error)< die("Ошибка: " . $conn->connect_error); > $name = $conn->real_escape_string($_POST["username"]); $age = $conn->real_escape_string($_POST["userage"]); $sql = "INSERT INTO Users (name, age) VALUES ('$name', $age)"; if($conn->query($sql)) < echo "Данные успешно добавлены"; >else< echo "Ошибка: " . $conn->error; > $conn->close(); > ?>

Здесь мы проверяем, пришли ли с сервера данные в POST-запросе, которые имеют ключи «username» и «userage»:

if (isset($_POST["username"]) && isset($_POST["userage"])) 
Если эти данные имеются, то есть был отправлен post-запрос с данными на добавление, то мы получаем эти данные в переменные и добавляем их в бд. Но перед добавлением к этим данным применяется метод $conn->real_escape_string() , которая принимает сохраняемое значение и экранирует в нем спецсимволы, что позволяет защитить от SQL-инъекций.
В итоге после ввода данных и нажатия на кнопку данные в запросе POST уйдут скрипту create.php , который сохранит их в базу данных.
 
Процедурный подход
 $name = mysqli_real_escape_string($conn, $_POST["username"]); $age = mysqli_real_escape_string($conn, $_POST["userage"]); $sql = "INSERT INTO Users (name, age) VALUES ('$name', $age)"; if(mysqli_query($conn, $sql)) < echo "Данные успешно добавлены"; >else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); > ?>
 
Здесь применяется функция mysqli_real_escape_string() . Она служит для экранизации символов в строке, которая потом используется в запросе SQL. В качестве параметров она принимает объект подключения и строку, которую надо экранировать.
 
Источник
 
Sql php mysql insert post data code example
 
Solution 3: For multiple query execution you can use mysqli_multi_query() For multi insertion to a table with single query is like this multi insert:- So you can try like You can not use multiple INSERT into single for this action, you can use for executing multiple queries.
 
INSERT INTO with PHP $_POST
 
First change your form.. 
Look at your inputs; take this one for example: 
 This is missing the name attribute therefore, it won't be correctly set when you submit the form; it must be like so: 
 
Secondly, if you want to concatenate a string, you don't use echo , echo is used for outputting text. To attach strings together in PHP you should read the manual page on string operators
 
Thirdly and most importantly;
 
Use prepare to input your data. Never pass $_POST directly into your query.
 
$conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) < die("Connection failed: " . $conn->connect_error); > $sql = 'INSERT INTO test_tb (firstname, lastname, email) VALUES ( ?, ?, ? )'; //use ->prepare in favour of ->query if ($stmt = $conn->prepare($sql)) < //bind your inputs $stmt->bind_param('sss',$_POST['fname'],$_POST['lname'],$_POST['email']); //execute the prepared query if($stmt->execute()) < echo "New record created successfully"; >//You had a random ';' here that I've commented out?? //; > else < echo "Error: " . $sql . "
" . $conn->error; echo "
Stmt error: ".$stmt->error(); > 
 
PHP MySQL Insert Data, Insert Data Into MySQL Using MySQLi and PDO. After a database and a table have been created, we can start adding data in them. Here are some syntax rules to follow: The SQL query must be quoted in PHP. String values inside the SQL query must be quoted. Numeric values must not be quoted. Code sample$password = "password";$dbname = "myDB";$conn = new mysqli($servername, $username, $password, $dbname);if ($conn->connect_error) connect_error);Feedback
 
How to post data to mySQL database through PHP form?
 
You are trying to retrieve variables with the wrong way.
 
Источник