Tag: insertion

Update with upsert: MongoDB

Lets learn to use upsert option with update() method.

upsert basically inserts document into the collection, if there is no matching document found in the collection. If there is a match found, then the document simply gets updated – in which case upsert option will not have any effect on the selected document.

update with upsert mongodb

test database, names collection

1
2
3
4
5
6
7
8
9
10
11
12
13

> db.names.find().pretty()
{
        "_id" : ObjectId("53c6392a2eea8062e084cb57"),
        "Company" : "Google",
        "Product" : "Nexus",
        "No" : 1
}
{
        "_id" : ObjectId("53c639392eea8062e084cb58"),
        "Company" : "Apple",
        "Product" : "iPhone",
        "No" : 2
}

There are 2 documents inside “names” collection, with _id, Company, Product and No as its fields.

Update with upsert: MongoDB


[youtube https://www.youtube.com/watch?v=ZQjNHjVv_W8]

YouTube Link: https://www.youtube.com/watch?v=ZQjNHjVv_W8 [Watch the Video In Full Screen.]



update() method, with $set operator

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

> db.names.update({"Company": "Apple"}, {$set: {"Product": "Mac"}});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
 
> db.names.find().pretty()
{
        "_id" : ObjectId("53c6392a2eea8062e084cb57"),
        "Company" : "Google",
        "Product" : "Nexus",
        "No" : 1
}
{
        "_id" : ObjectId("53c639392eea8062e084cb58"),
        "Company" : "Apple",
        "Product" : "Mac",
        "No" : 2
}

We select second document using “Company” – “Apple“. Here, the “Product” field value gets updated from iPhone to Mac.

Related Read: Update with SET Operator: MongoDB

No Match Found for Updation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

> db.names.update({"Company": "Xiaomi"}, {$set: {"Product": "Mi3", "No": 3}});
WriteResult({ "nMatched" : 0, "nUpserted" : 0, "nModified" : 0 })
 
> db.names.find().pretty()
{
        "_id" : ObjectId("53c6392a2eea8062e084cb57"),
        "Company" : "Google",
        "Product" : "Nexus",
        "No" : 1
}
{
        "_id" : ObjectId("53c639392eea8062e084cb58"),
        "Company" : "Apple",
        "Product" : "Mac",
        "No" : 2
}

Since, there is no matching “Company” called Xiaomi in “names” collection, there will be no matching document, hence no modification of document.

updation with upsert option

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

> db.names.update({"Company": "Xiaomi"}, 
                  {$set: {"Product": "Mi3", "No": 3}},
                  {upsert: true});
WriteResult({
        "nMatched" : 0,
        "nUpserted" : 1,
        "nModified" : 0,
        "_id" : ObjectId("53c63b26b003603dfdcf8c52")
})
 
> db.names.find().pretty()
{
        "_id" : ObjectId("53c6392a2eea8062e084cb57"),
        "Company" : "Google",
        "Product" : "Nexus",
        "No" : 1
}
{
        "_id" : ObjectId("53c639392eea8062e084cb58"),
        "Company" : "Apple",
        "Product" : "Mac",
        "No" : 2
}
{
        "_id" : ObjectId("53c63b26b003603dfdcf8c52"),
        "Company" : "Xiaomi",
        "Product" : "Mi3",
        "No" : 3
}

Observe the 3rd argument of update() method – upsert: true. In this case, since there is no matching document found inside the “names” collection, for “Company” Xiaomi, it inserts the document with all the fields and values specified in the update command. i.e., Company: Xiaomi, Product: Mi3, No: 3

unintentional modification of data with upsert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

> db.names.update({"No": {$gt: 3}}, 
                  {$set: {"Product": "Glass", "No": 4}}, 
                  {upsert: true});
WriteResult({
        "nMatched" : 0,
        "nUpserted" : 1,
        "nModified" : 0,
        "_id" : ObjectId("53c63bd1b003603dfdcf8c53")
})
 
> db.names.find().pretty()
{
        "_id" : ObjectId("53c6392a2eea8062e084cb57"),
        "Company" : "Google",
        "Product" : "Nexus",
        "No" : 1
}
{
        "_id" : ObjectId("53c639392eea8062e084cb58"),
        "Company" : "Apple",
        "Product" : "Mac",
        "No" : 2
}
{
        "_id" : ObjectId("53c63b26b003603dfdcf8c52"),
        "Company" : "Xiaomi",
        "Product" : "Mi3",
        "No" : 3
}
{
        "_id" : ObjectId("53c63bd1b003603dfdcf8c53"),
        "Product" : "Glass",
        "No" : 4
}

We have documents which has values 1, 2 and 3 for the field “No”. But in above command the condition is to select documents which has “No” value above 3 – which results in 0 documents being selected. But since we have “upsert: true” as the third argument of update() method, the “field: value” – “Product: Glass” and “No: 4” gets inserted into the “names” collection. So we need to take proper care of our commands and conditions – so that unintentional insertion of data or document can be avoided.

upsert effect on existing data/document

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

> db.names.update({"No": 4}, 
                  {$set: {"Company": "Sony", "Product": "Smart Watch"}}, 
                  {upsert: true});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
 
> db.names.find().pretty()
{
        "_id" : ObjectId("53c6392a2eea8062e084cb57"),
        "Company" : "Google",
        "Product" : "Nexus",
        "No" : 1
}
{
        "_id" : ObjectId("53c639392eea8062e084cb58"),
        "Company" : "Apple",
        "Product" : "Mac",
        "No" : 2
}
{
        "_id" : ObjectId("53c63b26b003603dfdcf8c52"),
        "Company" : "Xiaomi",
        "Product" : "Mi3",
        "No" : 3
}
{
        "_id" : ObjectId("53c63bd1b003603dfdcf8c53"),
        "Product" : "Smart Watch",
        "No" : 4,
        "Company" : "Sony"
}

In this case, there is a document inside the collection which has “No”: 4. So the upsert option will not have any effect on the selected data/document – it simply gets updated with the field values specified in the second argument of update() method. i.e., the document with “No”: 4 is selected and the “Company”: “Sony”, “Product”: “Smart Watch” gets added to the existing document.

Create and Insert Documents: MongoDB

In this video tutorial, we’ll look at the basic differences between MongoDB and traditional Database Management Systems. Also, commands to create database, collection, documents – insertion.

Topic of discussion:
Key differences.
Keyword differences.
Schema-free collection.
Database creation.
Creation of Collection.
Insertion of Documents.
JavaScript Console/Shall.

Some Commands

Show Database Already Present

1

show dbs

Create Database

1

use company

In MongoDB, it’ll never actually create any database or collection until we start storing documents in it!

Point object ‘db’ to our new database, by switching to company database

1
2
3
4

>use company
Switched to db company 
>db
company

Naming our Collection

1

db.info

So our collection name is info

Number of documents present

1

db.info.count()

using count() method, we check the no of documents present inside info collection.

Inserting document into Collection

1
2
3
4
5

db.info.insert({
 name     : 'Apple',
 product  : 'iPhone5S',
 emp_no   : 100
});

This inserts our first record.
insert() is the method, which accepts key-value pair object as it’s parameter.

JavaScript Shall
Since, this is a JavaScript shall, we can write any valid JavaScript code and interact directly with MongoDB.

Lets see another method to insert documents into the same collection.

Using save method

1
2
3
4
5
6
7
8
9

>var data = {}
>data.name = 'Technotip IT Solutions'
>data.product = 'Video Tutorials - Educational'
>data.emp = [ 'Satish', 'Kiran' ]
>data.videos = {}
>data.videos.mongo = 'MongoDB videos'
>data.videos.php = 'PHP Video Tutorials'
 
>db.info.save(data);

We create a JSON object data.
We start storing { key: value } pair into it.
emp is an array, which contains name of 2 employees.
videos is a sub object.

Now using save method we insert this data object into info collection.

Save()
Save() is a higher lever method. It checks to see if the object we’re inserting is already present in our collection, by checking the _id value we’re inserting. _id field(which is a primary key in MongoDB document)
– If there is no matching _id field in a document, it’ll call insert method and insert the data. And if the data being entered has no _id value, insert() method will create _id value for us, and insert the data into the collection.
– If _id matches to any document in our collection, it’ll use update() instead to update the previously present data with the new data entered by the user.

Database, Collections, Documents: MongoDB


[youtube https://www.youtube.com/watch?v=yAjG_fC6fPU]

YouTube Link: https://www.youtube.com/watch?v=yAjG_fC6fPU [Watch the Video In Full Screen.]



Note:
There is no Schema for our collection. i.e., no column name and datatype.
Since documents are in BSON format, insertion and finding data is much faster.

Schema-free design is flexible and is of much use in modern day web application.

Example: Users upload photos and tag names of people in the pic. This can be stored as an array of tags in a key value pair, for only those pics which has tags. For other pics, we need not create this array itself. This is flexible. Also BSON format helps create application with high Scalability.

Insert Data Into MySQL: jQuery + AJAX + PHP

Video tutorial illustrates insertion of data into MySQL database using jQuery and PHP, using AJAX method i.e., $.post() method of jQuery.

There are 5 shortcuts for AJAX in jQuery, ultimately calling ajax method, and by default configured to have different parameters.
$.get
$.post
$.getJSON
$.getScript
$.load

Tutorial Theme: Insert Data Into Database Without Refreshing Webpage

In this tutorial we’ll illustrate $.post method of jQuery.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<html>
<head><title>Insert Data Into MySQL: jQuery + AJAX + PHP</title></head>
<body>
 
<form id="myForm" action="userInfo.php" method="post">
Name: <input type="text" name="name" /><br />
Age : <input type="text" name="age" /><br />
<button id="sub">Save</button>
</form>
 
<span id="result"></span>
 
<script src="script/jquery-1.8.1.min.js" type="text/javascript"></script>
<script src="script/my_script.js" type="text/javascript"></script>
</body>
</html>

Here we have a form with an id called myForm, which has 2 input box.
A button with id sub. And a span tag with an id of result.

Once the user enters his/her name, age and hits Save button, the information will be sent to userInfo.php file present in the action field, via post method.

Using AJAX technique, we would make sure the form doesn’t redirect to userInfo.php file after the submission, and we’ll display appropriate message to the user on index.html file itself after the user entered values has been processed by userInfo.php file.

Database Connection code: PHP
db.php

1
2
3
4

< ?php
  $conn = mysql_connect('localhost', 'root', '');
  $db   = mysql_select_db('test');
?>

We connect our application to mysql server using the user name root( and we leave password field empty, as we haven’t set any password to it on our machine ).
Also select the database called test.

Create Database called test.
A table user inside the database test.
user table has 2 fields called name and age.

Processing Page code: PHP
userInfo.php

1
2
3
4
5
6
7
8
9
10
11

< ?php
 include_once('db.php');
 
$name = $_POST['name'];
$age = $_POST['age'];
 
if(mysql_query("INSERT INTO user VALUES('$name', '$age')"))
  echo "Successfully Inserted";
else
  echo "Insertion Failed";
?>

Once the user hits the Save button of our form in index.html, the information is being sent to userInfo.php

Where the user entered values are being copied to local variables $name and $age.
Next a simple MySQL query is formed to insert the data into the table user.

Using mysql_query() standard php function, the MySQL query is being processed.
Using if else conditional statements, we check if the process was successful or failed. Based on which we echo the result.

jQuery code: AJAXing
my_script.js

1
2
3
4
5
6

$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); 
  });
});

Once the user clicks on the button with the id sub, $.post() method is invoked.
$.post() is a shortcut to $.ajax() method.

General Syntax:
$.post( ‘pass_data_to_this_url’, ‘data’, ‘callback_function’ );

We fetch the URL from myForm form, from its action attribute.
Serialize all the user entered input’s.
SerializeArray makes the input into property: value pair.
After the data is passed to the url and is being processed, the result will be returned back to the callback function and is caught in info variable, and is inserted inside the span tag using html() method – which is similar to innerHTML() method of JavaScript.

jQuery code: Disable Form Redirection
my_script.js

1
2
3

$("#myForm").submit( function() {
  return false;
});

Select the form and make sure to return false upon submission.

jQuery code: Clear Input Fields
my_script.js

1
2
3
4
5

function clearInput() {
$("#myForm :input").each( function() {
   $(this).val('');
});
}

input-fields-form

Write a custom function. Select the input fields and set each of its value to none or empty.
Call this function once the user clicks the submit button.

Full jQuery code
my_script.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); 
   });
 clearInput();
});
 
$("#myForm").submit( function() {
  return false;
});
 
function clearInput() {
$("#myForm :input").each( function() {
   $(this).val('');
});
}

Note that, we have called clearInput() function inside the click event of #sub.

Video Tutorial: Insert Data Into MySQL: jQuery + AJAX + PHP


[youtube https://www.youtube.com/watch?v=gvGb5Z0yMFY]

YouTube Link: https://www.youtube.com/watch?v=gvGb5Z0yMFY [Watch the Video In Full Screen.]



Note:
You have to implement addslash and stripslash or any other methods into your PHP code, to secure your script from SQL Injection.

If the PHP returns json encoded data, then we need to use json in $.post() method as follows:

1
2
3
4
5

$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); },
         "json" );

But in our example, we are not returning json encoded data from userInfo.php file, so we do not specify encoding information in $.post() method.