Showing posts with label Select data from database. Show all posts
Showing posts with label Select data from database. Show all posts

Monday, July 10, 2017

Connecting PHP with MySQL 

Connecting PHP with MySQL

Content

* Connectivity
* Selecting Database
* Insert, Update and Delete
* Select data from database
* Sample Application

Connectivity

* PHP provides several predefined functions to access MySQL, but before accessing MySQL, application need to connect MySQL as a user (similar to login from MySQL client)
* To establish connection between PHP and MySQL, PHP provides fine tuned methods like mysqli_connect().

mysqli_connect()

* Following is the format of mysqli_connection function
mysqli_connect(host,username,password,dbname);
* host: host ip address of database server, in our case it is localhost 
* username: Name of the MySQL user
* password: Password of the user
* dbname: Database name (optional)
Example
<?php 
     $con=mysqli_connect('localhost','root');
    if(!$con)
  echo "Connection failed";
    else
  echo "Connection done";
?> 

mysqli_close()

* This method is used to disconnect PHP with MySQL, similar to logout.

mysqli_close($con); 

Example

<?php 
$con=mysqli_connect('localhost','root');
     if(!$con)
  echo "Connection failed";
     else
  echo "Connection done";
/* do needful database operations here */
    mysqli_close($con); 
?>

Selecting Database

* We can select database after getting connection, using mysqli_select_db() method
mysqli_select_db($con,$databsename);

Insert, Update and Delete

* We can insert, update or delete data using mysqli_query() method. 
mysqli_query($con,$query);
* $query is query string, it can be any insert, update or delete sql statement.

Select data

* Selecting data is somewhat requires more handling, as the result of execution of select query, we may get several rows of a table.

$result = mysqli_query($con,$query);

* mysqli_query() method returns extracted data as two dimensional associative array.
$row=mysqli_fetch_array($result);

* We can fetch first row as an array of values using method mysqli_fetch_array()

* you can also count number of rows using mysqli_num_rows() method
$num=mysqli_num_rows($result);

Sample Application

* Application gives interfaces to the user to manage book records. The following functionalities must be provided:
 - Insertion of new book record
 - Deletion of record
 - Updation of record
 - View all records
 - Search book records

* First of all create a database 'library'.
* Create a table 'book' in the 'library' database.

* Columns: 
 - bookid int(5) AUTO_INCREMENT PK
 - title varchar(255)
 - price float(6,2)

* Define following php files:
 - home.php
 - insert.php
 - delete.php
 - update.php
 - bookfun.php

home.php

<?php include('bookfun.php'); ?>
<html>
 <head>
  <title> Book Store Management</title>
  <style type="text/css">
   #buttons
   {
     margin-left:50px;
     width:50%;
     border:2px solid silver;
   }
   #activityarea
   {
     margin-left:50px;
     width:50%;
     border:2px solid silver;
     height:50%;
   }
   table {
    position:relative; 
    left:25%;
    width:300px;
   }
   #insertform { display:none;}
   #back {display:none;}
   #view,#edit,#delete {display:none;}
   #view table{ border:2px solid navy; }
   #view table th {background-color:orange;}
   #view table td {background-color:khaki;}
  </style>
  <script type="text/javascript" src="jquery.js"></script>
  <script type="text/javascript">
   $(document).ready(function(){
     $("button").click(function(){
      if($(this).html()=="Insert")
       {
          $("#insertform").show();
   $(".b1").hide();
          $("#back").show();
       } 
     
      if($(this).html()=="View")
       {
          $("#view").show();
   $(".b1").hide();
          $("#back").show();
       }

      if($(this).html()=="Edit")
       {
          $("#edit").show();
   $(".b1").hide();
          $("#back").show();
       } 
      if($(this).html()=="Delete")
       {
          $("#delete").show();
   $(".b1").hide();
          $("#back").show();
       } 
     });
     $("#back").click(function(){
          $("#insertform").hide();
          $("#view").hide();
          $("#edit").hide();
          $("#delete").hide();
   $(".b1").show();
          $("#back").hide();  
     });     
   });
  </script>
 </head>
 <body>
  <h1> Book Store Management</h1>
  <div id="buttons">
   <button class="b1">Insert</button>
   <button class="b1">View</button>
   <button class="b1">Edit</button>
   <button class="b1">Delete</button>
   <button id="back">Back</button>
  </div>
  <div id="activityarea">
   <form id="insertform" action="insert.php" method="post">
    <table>
     <tr>
 <td>Book Title</td>
 <td><input type="text" name="title"/></td>
     </tr>
     <tr>
 <td>Price</td>
 <td><input type="text" name="price"/></td>
     </tr>
     <tr>
 <td></td>
 <td><input type="submit" value="store"/></td>
     </tr>
    </table>
   </form>
   <div id="view" > <?php viewbook(); ?></div>
   <div id="edit" > <?php editbook(); ?></div>
   <div id="delete" > <?php deletebook(); ?></div>
  </div>
 </body>
</html>

insert.php

<?php
  $title=$_POST['title'];
  $price=$_POST['price'];
  $con=mysql_connect('localhost','root');
  if(!$con)
   echo "Could not connect";
  mysql_select_db("lms",$con);
  $q="insert into book (title,price) values ('$title',$price)";
  mysql_query($q);
  mysql_close($con);
  header('location:http://localhost/ex2/home.php');
?>

update.php

<?php
 /* extracting HTML form data */
 $i=1;
 while(isset($_POST["title".$i]))
 {
  $bookid[$i]=$_POST["bookid".$i];
  $title[$i]=$_POST["title".$i];
  $price[$i]=$_POST["price".$i];
  $i++;
 }
 $length=$i-1;
/* Establishing Connection */
   $con=mysql_connect('localhost','root');
   if(!$con)
    echo "Could not connect";
   mysql_select_db("lms",$con);
  for($i=1;$i<=$length;$i++)
  {
   $q="update book set title='"."$title[$i]"."' ,price=$price[$i] where bookid=$bookid[$i]";
   mysql_query($q);
  }
  mysql_close($con);
  header('location:http://localhost/ex2/home.php');
?>

delete.php

<?php
 /* extracting HTML form data */
 $i=1;
 $j=1;
 while(isset($_POST["title".$i]))
 {
  if(isset($_POST["cb".$i]))
  {
   $bookid[$j]=$_POST["bookid".$i];
   $title[$j]=$_POST["title".$i];
   $price[$j]=$_POST["price".$i];
   $j++;
  }
  $i++;
 }
 $length=$j-1;
/* Establishing Connection */
   $con=mysql_connect('localhost','root');
   if(!$con)
    echo "Could not connect";
   mysql_select_db("lms",$con);
  for($j=1;$j<=$length;$j++)
  {
   $q="delete from book where bookid=$bookid[$j]";
   $s= mysql_query($q);
  }
  mysql_close($con);
  header('location:http://localhost/ex2/home.php');
?>

bookfun.php

<?php
/* Function 1*/
 function viewbook()
 {
  $con=mysql_connect('localhost','root');
  if(!$con)
   echo "Could not connect";
  mysql_select_db("lms",$con);
  $q="select * from book";
  $result=mysql_query($q);
?>
 <table>
  <tr><th>Title</th><th>Price</th></tr>
<?php
  while($row=mysql_fetch_array($result))
  {
  echo "<tr><td>".$row['title']."</td><td>".$row['price']."</td></tr>";
  }
  echo "</table>";
 }
?>

<?php 
/* Function2*/
  function editbook()
  { 
   $con=mysql_connect('localhost','root');
   if(!$con)
    echo "Could not connect"; 
   mysql_select_db("lms",$con);
   $q="select * from book";
   $result=mysql_query($q);
?>
 <form action="update.php" method="post">
 <table>
  <tr><th></th><th>Title</th><th>Price</th></tr>
<?php
  $i=0;
  while($row=mysql_fetch_array($result))
  {
   $i++;
   echo "<tr><td><input type='hidden' value='".$row['bookid']."' name='bookid".$i."' /></td>";
   echo "<td><input type='text' value='".$row['title']."' name='title".$i."'/></td><td><input type='text' value='".$row['price']."' name='price".$i."' /></td></tr>";
  }
?>
 <tr><td></td><td></td><td><input type="submit" value="save" /></td></tr>
<?php  
  echo "</table></form>";    
  }
?>

<?php
  /* function 3 */
 function deletebook()
 {
   $con=mysql_connect('localhost','root');
   if(!$con)
    echo "Could not connect"; 
   mysql_select_db("lms",$con);
   $q="select * from book";
   $result=mysql_query($q);
?>
 <form action="delete.php" method="post">
 <table>
  <tr><th></th><th>Title</th><th>Price</th></tr>
<?php
  $i=0;
  while($row=mysql_fetch_array($result))
  {
   $i++;
   echo "<tr><td><input type='checkbox' name='cb".$i."' /><input type='hidden' value='".$row['bookid']."' name='bookid".$i."' /></td>";
   echo "<td><input type='text' value='".$row['title']."' name='title".$i."'/></td><td><input type='text' value='".$row['price']."' name='price".$i."' /></td></tr>";
  }
?>
 <tr><td></td><td></td><td><input type="submit" value="Delete" /></td></tr>
<?php
  echo "</table></form>";    
 }
?>

* Do not forget to include jquery.js file

<<Prev     Home     Next>>
at No comments:
Labels: , , , , , , ,
Subscribe to: Posts (Atom)