MySQL INSERT ... SET

MySQL supports an INSERT ... SET form for inserting one row by assigning values directly to column names. It is a MySQL-specific alternative to the standard INSERT ... VALUES syntax and can be convenient when a row has many columns.

INSERT INTO library SET book_name = 'Learning MySQL', author = 'plus2net group';

Columns that have an AUTO_INCREMENT value or a suitable DEFAULT can be omitted.

Insert into the student table

INSERT INTO student SET name = 'my_name', class = 'my_class', mark = 80, gender = 'Male';

PDO prepared statement

Use parameters for values coming from a form or another external source.

<?php
require 'config.php'; // Database connection

$name = 'Alex R';
$class = 'Five';
$mark = 70;
$gender = 'Female';

$query = 'INSERT INTO student SET name = :name, class = :class, mark = :mark, gender = :gender';
$stmt = $dbo->prepare($query);
$stmt->execute([
    ':name' => $name,
    ':class' => $class,
    ':mark' => $mark,
    ':gender' => $gender
]);

$member_id = $dbo->lastInsertId();
echo 'Thanks. Your membership id = ' . $member_id;
?>

This also corrects an error in the older sample where the mark placeholder was accidentally bound to the name variable.

MySQLi prepared statement

<?php
require 'config.php'; // MySQLi connection in $connection

$name = 'my_name';
$class = 'Three';
$mark = 70;
$gender = 'Male';

$query = 'INSERT INTO student SET name = ?, class = ?, mark = ?, gender = ?';
$stmt = $connection->prepare($query);
$stmt->bind_param('ssis', $name, $class, $mark, $gender);
$stmt->execute();

echo 'No. of records inserted: ' . $stmt->affected_rows;
echo ' Insert ID: ' . $connection->insert_id;
?>

No. of records inserted: 1
Insert ID: 52

PDO tutorial | MySQLi tutorial | MySQLi connection

Student table SQL dump

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: