Use View in PHP

Introduction

"A view is a select statement that is stored in the database as a database object". Or we can say Views are files that contains display information for your application. To create a view, you use a CREATE VIEW statement.
 
Syntax

CREATE [OR REPLACE ] VIEW  viewName [column_alias 1, column_alias 2, column_alias 3...........]  As select_statement [with check option] [constraint constraint_name]

Note: The REPLACE VIEW statement is used to replace the existing view with a new one.

For Example

CREATE VIEW virtual_empdtl as select id, Firstname,Lastname,role from emp_dtl;

You can think of a view as a virtual table that consists only of the rows and columns specified in its CREATE VIEW statement. The table or tables, which you write with a from clause are called base tables for the view. The view does not store data itself, and it always reflects the most current data in the base tables.

Create view in MySQL and show Virtual (view) table data



Create view through PHP

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("mysql", $con);

print "<h2>MySQL: Creating View</h2>";

$result = mysql_query("CREATE VIEW virtual_empdtl as select id, Firstname,Lastname,role from emp_dtl");

echo "Query =CREATE VIEW virtual_empdtl as select id, Firstname,Lastname,role from emp_dtl";

print "<h2>MySQL: View Created.</h2>";

print "<h2>MySQL: Select Virtual( View) table data</h2>";

$result1 = mysql_query("select * from virtual_empdtl");

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Lastname</th>

<th>Role</th>

</tr>";

while($row = mysql_fetch_array($result1))

  {

   echo "<tr>";

  echo "<td>" . $row['id'] . "</td>";

  echo "<td>" . $row['Firstname'] . "</td>";

  echo "<td>" . $row['Lastname'] . "</td>";

  echo "<td>" . $row['role'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

  mysql_close($con);

  ?>

Output



Note: In the above example the CREATE VIEW statements create a view named virtual_empdtl that retrieves the id, Firstname, Lastname and role columns from the emp_dtl table.  After creating the view, the select * from virtual_empdtl query returns all the columns, which is stored in virtual (view) table.

A view can be updatable. It is possible with a view to use INSERT, DELETE and UPDATE statements.

UPDATE virtual_empdtl SET role='admin' where id =101;

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("mysql", $con);

print "<h2>MySQL: Update operation in View</h2>";

$result = mysql_query("UPDATE virtual_empdtl SET role='admin' where id =101");

echo "Query =UPDATE virtual_empdtl SET role='admin' where id =101";

print "<h2>MySQL: Virtual table has been Updated.</h2>";

print "<h2>MySQL: Select Virtual( View) table data</h2>";

$result1 = mysql_query("select * from virtual_empdtl");

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Lastname</th>

<th>Role</th>

</tr>";

while($row = mysql_fetch_array($result1))

  {

   echo "<tr>";

  echo "<td>" . $row['id'] . "</td>";

  echo "<td>" . $row['Firstname'] . "</td>";

  echo "<td>" . $row['Lastname'] . "</td>";

  echo "<td>" . $row['role'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

  mysql_close($con);

  ?>

Output



Note: In the above example, the UPDATE statement uses the virtual_empdtl view to update the role column in the emp_dtl table for the specific user.

Drop View in MySQL

You can drop the view using a DROP VIEW statement.

DROP VIEW viewname;



Why we use VIEWS

Reasons for using a view are:

• You can use views to limit the exposure of tables in your database to external users and applications.
• You can divide a single table into two or more tables. (e.g, suppose you have a table, in which id, name ,salary, role and hire_date columns are present, you can divide it into two sub tables (by creating views) .
• You can also use views to restrict access to a database (in other words to include just specified columns and rows you want a user or an application to have access to in the views).

For example: In the preceding (first) example, I created a view that does not include the salary column for the user who needs to view and maintain salary information.