SQL Insert command using set option to add records to MySQL table

By using set option we can insert record to any table. This is an alternate to insert command using value option of adding single record to the table. Here we have to use insert command at the beginning and use field names along with the value for the files with an equal to symbol. This is help full when we are handling more number of fields with one insert command; we can match the value to field name easily without worrying about matching the value to field names. In other words we can ensure proper value to the correct column. This is also useful when we are adding value to some fields and allowing default value for other fields.

Here is an example of the sql insert command to add a record using set option.

Insert into library set book_name='Learning MySQL', author='plus2net group'

SQL Insert using VALUE option

This will add one new record to the table and values for two fields book_name and author gets added in the new record. This is quite helpful in matching field names to value.

Inserting record with default value or auto-increment field

In our student record we already have student id which is auto- increment field hence while adding a record we need no enter its name or value. Other than the auto increment field we can store records to our student table by using set command.

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

Similarly we need not specify for default value of the fields while adding records

PHP Script using SET command

We will add one record to our student table by using SET command and PHP Script.

We will first connect to database Here is the complete code

<?Php
require "config.php"; // Database Connection
$sql=$dbo->prepare("INSERT INTO student 
   SET name='my_name', class='my_class',mark='80',sex='male'");
if($sql->execute()){
$mem_id=$dbo->lastInsertId(); 
echo " Thanks .. Your Membership id = $mem_id ";
}
else{
echo " Not able to add data please contact Admin ";
}
?>

Output

Thanks .. Your Membership id = 37

Using Parameterized query

<?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";
$step=$dbo->prepare($query);
$step->bindParam(':name',$name,PDO::PARAM_STR, 15);
$step->bindParam(':class',$class,PDO::PARAM_STR, 15);
$step->bindParam(':mark',$name,PDO::PARAM_INT,5);
$step->bindParam(':gender',$gender,PDO::PARAM_STR,10);

if($step->execute()){
$mem_id=$dbo->lastInsertId(); 
echo " Thanks .. Your Membership id = $mem_id ";
}
else{
echo " Not able to add data please contact Admin ";
}
?>

Using MySQLI

<?Php
require "config.php";// Database connection

$name = 'my_name';
$class='Three';
$mark=70;
$gender='male';
$query="INSERT INTO student SET name=?,class=?,mark=?,gender=?";
$stmt=$connection->prepare($query);
if($stmt){ 
$stmt->bind_param("ssds", $name, $class,$mark,$gender);
if($stmt->execute()){
echo "<br>No of records inserted : ".$connection->affected_rows;
echo "<br>Insert ID : ".$connection->insert_id;
}else{
echo $connection->error;
}
}else{
echo $connection->error;
}
?>

Output

No of records inserted : 1
Insert ID : 52

How to connect MysQLI?

UPDATE record by using SET command MySQL auto increment field

Subscribe to our YouTube Channel here

anjali vaswani	18-01-2011
can set command be also used for checking a value in the field of one column and entering value in other column. example: if we want to enter value of y corresponding to its value o x in table t1 where x and y are columns of table t1 and x value is already entered in the database

SQL INSERT Command with SET option

Inserting record with default value or auto-increment field

PHP Script using SET command

Subscribe