Tuesday 3 May 2016

How to use Mysql View in PHP Code


This is most useful concept on Mysql database this is because in this post we have learn how to use of mysql view within php code. First of what is view. View is a virtual table based on result set of an sql statement. A view has rows and columns just like a real table. We can add fields in view from one or more tables in database. We can also use sql function and WHERE condition and even we can use JOIN statement in view. In this post for describing view I have join two tables. View can be create by using CREATE VIEW statement and then after writing simple query. View is store on to database table just like other tables. Suppose you want to called view from php code then you have to write simple select with name of view. This way you can called view from PHP code. In this post you can find source code under this post and for detail description you can also watch video of this post.


Create View

 CREATE view productlist AS SELECT brand.brand_name, product.product_name FROM brand INNER JOIN product ON product.brand_id = brand.brand_id  

Update View

 CREATE OR REPLACE view productlist AS SELECT brand.brand_id, brand.brand_name, product.product_name FROM brand INNER JOIN product ON product.brand_id = brand.brand_id  

mysql_view.php

 <?php  
 $connect = mysqli_connect("localhost", "root", "", "zzz");  
 $sql = "SELECT * FROM productlist";  
 $result = mysqli_query($connect, $sql);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | How to use Mysql View in PHP Code</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
      </head>  
      <body>  
           <br />  
           <div class="container" style="width:500px;">  
                <h3 align="">How to Use Mysql View in PHP Code</h3><br />                 
                <div class="table-responsive">  
                     <table class="table table-striped">  
                          <tr>  
                               <th>Brand ID</th>  
                               <th>Brand Name</th>  
                               <th>Product Name</th>  
                          </tr>  
                          <?php  
                          while($row = mysqli_fetch_array($result))  
                          {  
                          ?>  
                          <tr>  
                               <td><?php echo $row["brand_id"]; ?></td>  
                               <td><?php echo $row["brand_name"];?></td>  
                               <td><?php echo $row["product_name"]; ?></td>  
                          </tr>  
                          <?php  
                          }  
                          ?>  
                     </table>  
                </div>  
           </div>  
           <br />  
      </body>  
 </html>  

1 comment: