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
Labels: , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)