Tag: select

SELECT Columns/Fields: MongoDB

In this video tutorial we shall see selecting of key: value in MongoDB.

In RDBMSs like MySQL, we do the same using SELECT statement.
In MongoDB, we use findOne() and find() methods.

key-value-pair-mongoDB

Documents in ‘info’ collection

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

> db.info.find().forEach(printjson)
{
        "_id" : ObjectId("517e829d005b19f1f0d96b25"),
        "name" : "Apple",
        "product" : "iPhone5S",
        "emp_no" : "100"
}
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "name" : "Technotip",
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}

Learn Create and Insert Documents: MongoDB.

Note:
find() returns cursor objects.
findOne() returns single object.

We can use findOne() method to select and retrieve only one record at a time.

findOne() Method

1
2
3
4
5
6
7
8

> db.info.findOne({name: 'Technotip'}).product
Video Tutorials - Educational
 
> db.info.findOne({name: 'Technotip'}).videos
{ "mongo" : "MongoDB Videos", "php" : "PHP Video Tutorials" }
 
> db.info.findOne({name: 'Technotip'}).emp;
[ "Satish", "Kiran" ]

Syntax for retrieving normal {key: value} pair, sub-object {key: value} pair and array {key: value} pair is same.

Limitation
Using findOne() method, we could select and return only 1 {key: value} pair.

To select and return more than 1 {key: value} pair, we can make use of find() method, with 2 parameters.

SELECT Columns or Fields ( { KEY: VALUE } ): MongoDB


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

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



find() Method, with two parameters

1
2

> db.info.find({name: 'Apple'}, {product: 1}).forEach(printjson);
{ "_id" : ObjectId("517e829d005b19f1f0d96b25"), "product" : "iPhone5S" }

First parameter is the condition, second parameter specifies the {key: value} pairs.

We can pass more than 1 key in second parameter; that would returns multiple {key: value} pairs.

1
2
3
4
5
6

> db.info.find({name: 'Apple'}, {product: 1, emp_no: 1}).forEach(printjson);
{
        "_id" : ObjectId("517e829d005b19f1f0d96b25"),
        "product" : "iPhone5S",
        "emp_no" : "100"
}

1 or true means, those {key: value} pairs need to be returned.
0 or false means, excluding those {key: value} pairs, all other {key: value} pairs(records) needs to be returned.

true(0)

1
2
3
4
5
6
7
8

> db.info.find({name: 'Apple'}, {product: true}).forEach(printjson);
{ "_id" : ObjectId("517e829d005b19f1f0d96b25"), "product" : "iPhone5S" }
> db.info.find({name: 'Apple'}, {product: true, emp_no: true}).forEach(printjson);
{
        "_id" : ObjectId("517e829d005b19f1f0d96b25"),
        "product" : "iPhone5S",
        "emp_no" : "100"
}

False(0): Exclusion

1
2

> db.info.find({name: 'Apple'}, {product: 0, emp_no: 0}).forEach(printjson);
{ "_id" : ObjectId("517e829d005b19f1f0d96b25"), "name" : "Apple" }

Note:
True and False combination doesn’t work

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

> db.info.find({name: 'Apple'}, {product: true, emp_no: false}).forEach(printjson);
Wed May 08 12:11:30.184 JavaScript execution failed: error: {
        "$err" : "You cannot currently mix including and excluding fields. 
                  Contact us if this is an issue.",
        "code" : 10053
} at src/mongo/shell/query.js:L128
> db.info.find({name: 'Apple'}, {product: 0, emp_no: 1}).forEach(printjson);
Wed May 08 12:11:40.840 JavaScript execution failed: error: {
        "$err" : "You cannot currently mix including and excluding fields. 
                  Contact us if this is an issue.",
        "code" : 10053
} at src/mongo/shell/query.js:L128

We can not combine true(1) and false(0) together in the second parameter.

Special Provision!
But we can do it with _id(ObjectId)

1
2
3

> db.info.find({name: 'Apple'}, 
               {product: 1, emp_no: 1, _id: 0}).forEach(printjson);
{ "product" : "iPhone5S", "emp_no" : "100" }

Operation of second document/record in ‘info’ collection.
This illustrates that the syntax for sub-objects, array and the normal key/value pair is same.

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
35
36
37
38
39
40
41
42
43
44
45

> db.info.find({name: 'Technotip'}, {product: 1}).forEach(printjson);
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "product" : "Video Tutorials - Educational"
}
{ "_id" : ObjectId("518363e2d73694e289255486") }
> db.info.find({name: 'Technotip'}, {product: 1, videos: 1}).forEach(printjson);
 
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "product" : "Video Tutorials - Educational",
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}
 
> db.info.find({name: 'Technotip'}, 
               {product: 1, videos: 1, emp: 1}).forEach(printjson);
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}
 
> db.info.find({name: 'Technotip'}, 
               {product: 1, videos: 1, emp: 1, _id: 0}).forEach(printjson);
{
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}

Note:
Two documents with same name

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

{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "name" : "Technotip",
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}
{ "_id" : ObjectId("518363e2d73694e289255486"), "name" : "Technotip" }
1
2

> db.info.findOne({name: 'Technotip'})._id;
ObjectId("517e8377005b19f1f0d96b26")

If we use findOne() method on this collection, and the condition being the name key, then the oldest document will be returned.
Only one key: value pair is returned, as findOne() returns only single object.

UPDATE / EDIT Records In Database Table: PHP & MySQL

Video tutorial demonstrates updating / editing of data / records from the database table and displaying it on the browser.

First look at these short videos:
Connect to the database
Simple / basic insertion operation
Insertion of Records into Database using Forms: PHP & MySQL (important)
SELECT / LIST Records From Database Table: PHP & MySQL

Source Code: SELECTING and Displaying Data

index.php

<?php

include_once('db.php');
 
if(isset($_POST['name']))
{
  $name = $_POST['name'];
 
  if(mysql_query("INSERT INTO apple VALUES('','$name')"))
echo "Successful Insertion!";
  else
echo "Please try again";
}
 
 
$res = mysql_query("SELECT * FROM apple");
 
 
?>
 
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
 
<h1>List of companies ..</h1>
< ?php
while( $row = mysql_fetch_array($res) )
  echo "$row[id]. $row[name] 
                <a href='edit.php?edit=$row[id]'>edit<br />";
?>

Video Tutorial: UPDATE / EDIT Records In Database Table: PHP & MySQL


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

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


Source Code: Updating / Editing Data / Records In The Database Table

edit.php

<?php
include_once('db.php');
 
if( isset($_GET['edit']) )
{
$id = $_GET['edit'];
$res= mysql_query("SELECT * FROM apple WHERE id='$id'");
$row= mysql_fetch_array($res);
}
 
if( isset($_POST['newName']) )
{
$newName = $_POST['newName'];
$id   = $_POST['id'];
$sql     = "UPDATE apple SET name='$newName' WHERE id='$id'";
$res  = mysql_query($sql) 
                                    or die("Could not update".mysql_error());
echo "<meta http-equiv='refresh' content='0;url=index.php'>";
}
 
?>

<form action="edit.php" method="POST">
Name: <input type="text" name="newName" value="<?php echo $row[1]; ?/>"><br />
<input type="hidden" name="id" value="<?php echo $row[0]; ?/>">
<input type="submit" value=" Update "/>
</form>

meta tag used to redirect to other page..

<meta http-equiv='refresh' content='0;url=index.php'>

MySQL Query

mysql> UPDATE apple SET name='newName' WHERE id='session_id';

Security Issue:
Make sure not to allow people to explicitly pass random id’s and retrieve and edit/update other’s data. To solve this issue, you can save the id of the logged in user in a session variable and instead of passing id to edit.php we can directly use the id stored in our session variable.
This would solve the issue.

Output: After Updating the records:

  1. Google USA
  2. Apple USA
  3. Microsoft USA
  4. Oracle USA
  5. Technotip IN

Video’s To Watch:
GET method in action
Post method in action
session variable basics
User login and session

SELECT / LIST Records From Database Table: PHP & MySQL

Video tutorial demonstrates selecting / listing / fetching of data / records from the database table and displaying it on the browser.

First look at these short videos:
Connect to the database
Simple / basic insertion operation
Insertion of Records into Database using Forms: PHP & MySQL (important)

SELECTING and Displaying Data
index.php

1
2
3
4
5
6
7
8
9

< ?php
  include_once('db.php');
 
 $res = mysql_query("SELECT * FROM apple");
 
  while( $row = mysql_fetch_array($res) )
  echo "$row[id]. $row[name] <br />";
 
?>

Video Tutorial: SELECT / LIST Records From Database Table: PHP & MySQL


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

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



Full code
index.php

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

< ?php
include_once('db.php');
 
if(isset($_POST['name']))
{
  $name = $_POST['name'];
 
  if(mysql_query("INSERT INTO apple VALUES('','$name')"))
echo "Successful Insertion!";
  else
echo "Please try again";
}
 
 
$res = mysql_query("SELECT * FROM apple");
 
 
?>
 
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
 
<h1>List of companies ..</h1>
< ?php
while( $row = mysql_fetch_array($res) )
  echo "$row[id]. $row[name] <br />";
?>

while loop executes until there is data inside the table, once there is no more data to fetch, loop terminates.

MySQL Query

1

mysql> SELECT * FROM apple;

This mysql statement fetches data from all the fields of the table.
We could specify fields and get only list of those values.

Ex:

1

mysql> SELECT id FROM apple;

Using this we can get list of id’s only.

After fetching data from table apple

List of companies ..

1. Google
2. Apple
3. Microsoft
4. Oracle
5. Technotip