METANIT.COM

How to run an INSERT query using Mysqli

It goes without saying that you must use prepared statements for any SQL query that would contain a PHP variable. Therefore, as usually the INSERT query makes little sense without variables, it should always run through a prepared statement. To do so:

• create a correct SQL INSERT statement
• replace all variables in the query with with question marks (called placeholders or parameters)
• prepare the resulting query
• bind variables to placeholders
• execute the query

Just make sure you’ve got a properly configured mysqli connection variable that is required in order to run SQL queries and to inform you of the possible errors.

Running INSERT query with raw Mysqli

Just write a code like in this example

$sql = "INSERT INTO users (name, email, password) VALUES (. )";
$stmt= $conn->prepare($sql);
$stmt->bind_param("sss", $name, $email, $password);
$stmt->execute(); 

and have your query executed without a single syntax error or SQL injection!

Explanation

$sql = "INSERT INTO users (name, email, password) VALUES (. )"; 

Like it was said above, first we are writing an SQL query where all variables are substituted with question marks.

IMPORTANT! there should be no quotes around question marks, you are adding placeholders, not strings.

Then, the query is prepared. The idea is very smart. To avoid even a possibility of the SQL injection or a syntax error caused by the input data, the query and the data are sent to database server separately. So it goes on here: with prepare() we are sending the query to database server ahead. A special variable, a statement is created as a result. We would use this variable from now on.

$stmt->bind_param("sss", $name, $email, $passwor); 

Then variables must be bound to the statement. The call consists of two parts — the string with types and the list of variables. With mysqli, you have to designate the type for each bound variable. It is represented by a single letter in the first parameter. The number of letters should be always equal to the number of variables. The possible types are

NOTE that you can almost always safely use «s» for any variable.

So you can tell now that «sss» means «there would be 3 variables, all of string type». And then, naturally, three variables obediently follow.

Then the query finally gets executed. Means variables get sent to database server and the query is actually executed.

NOTE that you don’t have to check the execution result. Given you have the proper connection code mentioned above, in case of error mysqli will raise an error automatically.

INSERT query with a helper function

As you may noted, the code is quite verbose. If you like to build a code like a Lego figure, with shining ranks of operators, you may keep it as is. If you, like me, hate useless repetitions and like to write concise and meaningful code, then there is a simple helper function. With it, the code will become two times shorter:

$sql = "INSERT INTO users (name, email, password) VALUES (. )";
prepared_query($conn, $sql, [$name, $email, $password]); 

Here we are calling the helper function using a connection variable, an sql query and an array with variables. Simple, clean and safe!

INSERT query from an array

It is often happens that we have an array consists of fields and their values that represents a row to be inserted into a database. And naturally it would be a good idea to have a function to convert such an array into a correct SQL INSERT statement and execute it. So here it goes (utilizing a helper function mentioned above but you can easily rewrite it to raw mysqli if you’d like to):

First of all this function will need a helper function of its own. We will need a function to escape MySQL identifiers. Yes, all identifiers must be quoted and escaped, according to MySQL standards, in order to avoid various syntax issues.

 function escape_mysql_identifier($field) return "`".str_replace("`", "``", $field)."`";
> 

And now we can finally have a function that accepts a table name and an array with data and runs a prepared INSERT query against a database:

 function prepared_insert($conn, $table, $data) $keys = array_keys($data); 
$keys = array_map('escape_mysql_identifier', $keys); 
$fields = implode(",", $keys); 
$table = escape_mysql_identifier($table); 
$placeholders = str_repeat('?,', count($keys) - 1) . '?'; 
$sql = "INSERT INTO $table ($fields) VALUES ($placeholders)"; 
prepared_query($conn, $sql, array_values($data));
> 

Related articles:

Got a question?

I am the only person to hold a gold badge in , and on Stack Overflow and I am eager to show the right way for PHP developers.

Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.

SEE ALSO:

• Top 10 PHP delusions
• PDO Examples
• Mysqli Examples
• Principles of web-programming
• Mysqli SELECT query with prepared statements
• How to run a SELECT query using Mysqli
• How to run an UPDATE query using Mysqli
• Using mysqli prepared statements with LIKE operator in SQL
• Mysqli prepared statement with multiple values for IN clause
• Mysqli examples
• How to call stored procedures with mysqli
• How to create a search filter for mysqli
• How to run 1000s insert queries with mysqli?

Latest comments:

• 24.07.23 01:16
  Tim Dawson for PDO Examples:
  Further to my previous reply to your e-mail, I now find that the following, with double quotes.
  read more
• 23.07.23 16:47
  Tim Dawson for PDO Examples:
  I have a web site where visitors can look at accommodation reviews. A selection of reviews is.
  read more
• 17.07.23 21:18
  Jim for (The only proper) PDO tutorial:
  Thanks for the reply! I was hoping you would know if what I’m attempting is even possible! :).
  read more
• 16.07.23 00:35
  Jim for (The only proper) PDO tutorial:
  Hi, I just discovered this site today, so first and foremost, thank you for all your work.
  read more
• 27.06.23 05:30
  Jeff Weingardt for MVC in simpler terms or the structure of a modern web-application:
  I was just curious what you find the most effective way to learn php and become proficient? so.
  read more

Add a comment

Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator’s review.

Comments:

Love your prepared_query() method. I use it all he time. How I can get the last insert ID from this function?

Reply:

Hello Christie! Thank you for such a nice feedback! You don’t get the last insert ID from the function because it’s a property of mysqli object. So it would be $mysqli->insert_id or similar, depends on how you name your mysqli variable.

Hi, Can you make a tutorial about using MySQLi UPDATE with ‘column => value’ associative array and your helper function, just as you did with INSERT? It will be really appreciated. Thank you, Boris

Reply:

Hello Boris! An UPDATE function is a tricky business, as it requires a WHERE clause which at best requires a full featured Query Builder. But I am not sure I am to go down that rabbit hole for a simple helper function. All I can suggest is a quick palliative, a function where a WHERE clause limited to the primary key lookup with the primary key field name hardcoded, in this case as id :

 function prepared_update_by_id($conn, $table, $data, $id) $table = escape_mysql_identifier($table); 
$sql = "UPDATE $table SET ";
 
foreach (array_keys($data) as $i => $field) $field = escape_mysql_identifier($field); 
$sql .= ($i) ? ", " : ""; 
$sql .= "$field = ?"; 
> 
$sql .= " WHERE style="color: #007700">; 
$data[] = $id; 
prepared_query($conn, $sql, array_values($data));
> 

$id = 1;
$data = ['name' => 'sue', 'car' => 'VW']; 
prepared_update_by_id($conn, 'users', $data, $id); 

But to be honest, I don’t like the idea. Such a function is good for an ORM — a class where you could define all the field names beforehand. But as a function is looks awkward. Better to write the UPDATE statement by hand.

Greetings, I would like to ask you a big favor. I know this may be a long shot but long story short, I am currently hitting my head on the wall because of my Final Year Project. The problem is, the code works perfectly fine on my localhost but after I uploaded it, there were many errors and things that were supposed to work, didn’t work, without a single error code. This makes it hard for me to even know what my problems even were. I am a student and a complete novice in PHP, and I’m too broke to pay someone even if I want to. If it’s okay with you, may I upload my .php and .sql files for you to look at? Also I will send the connection info for uploading to the server. If you don’t want to listen to my selfish request, I completely understand, please just ignore this message. Have a nice day.

Источник

Insert with mysqli in php

В прошлой теме мы добавили в базу данных таблицу 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. В качестве параметров она принимает объект подключения и строку, которую надо экранировать.
 
Источник
 
Читайте также:  Setting label text javascript