Ankit B Desai: php basics

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>>

PHP Control Instruction

Content

* Conditional Statement
* Loop

Conditional Statements

* Very often when you write code, you want to perform different actions for different decisions. You can use conditional statements in your code to do this
* we have the following conditional statements:

 - if statement - executes some code only if a specified condition is true
 - if...else statement - executes some code if a condition is true and another code if the condition is false
 - if...elseif....else statement - selects one of several blocks of code to be executed
 - switch statement - selects one of many blocks of code to be executed

if statement

if(some condition)
{
 /* code to be executed if condition is true */
}

Example

<html>
<body>
<?php
$t=date("H");
if ($t<"12"&&$t>"5") {
   echo "Good Morning!";
}
?>
</body>
</html>

if-else statement

if(some condition)
{
/* code to be executed if condition is true */
}
else
{
/* code to be executed if condition is false */
}

Example

<html>
<body>
<?php
$x=25;
if ($x%2==0) {
   echo "Even Number";
}
else{
   echo "Odd Number";
}
?>
</body>
</html>

if-else-if….else statement

if(some condition){
/* code to be executed if condition is true */
} else if (some condition){
/* code to be executed if condition is true */
}else if(some condition){
/* code to be executed if condition is true */
}
else {
/* code to be executed if condition is false */
}

Example

<html>
<body>
<?php
$t=date("H");
if ($t<"12") {
   echo "Good Morning";
}else if($t<"17"){
   echo "Good After Noon";
} else if($t<"10"){
   echo "Good After Noon";
}else{
   echo "Good Night";
}
?> 
</body>
</html>

switch statement

switch(n)
{
 case label1:
/* code to be executed if n==label1 */
case label2:
/* code to be executed if n==label2 */
case label3:
/* code to be executed if n==label3 */
case label4:
/* code to be executed if n==label4 */
....
default:
/* code to be executed if n is different from all labels */
}

Example

<html>
<body>
<?php
$country="INDIA";
switch($country){
case "INDIA":
echo "Rupee";
break;
case "USA":
echo "Dollar";
break;
case "JAPAN":
echo "Yen";
break;
case "ENGLAND":
echo "Pound";
break;
default:
 echo "No Currency";
}
?>
</body>
</html>

Loop

* Sometimes, you want the same block of code to run over and over again, we can use loops to perform a task like this
* In PHP, we have the following looping statements:
 - while
 - do while
 - for
 - foreach

while statement

* The while loop executes a block of code as long as the specified condition is true

while(condition)
{
/*Code to be executed*/
}

Example

<html>
<body>
<?php
$x=1;
while($x<=10){
echo $x;
$x++;
}
?>
</body>
</html>

do while statement

* The do...while loop will always execute the block of code once, it will then check the condition, and repeat the loop while the specified condition is true

do
{
/*Code to be executed*/
} while(condition);

Example

<html>
<body>
<?php
$x=1;
do
{
echo $x;
$x++;
} while($x<=10);
?>
</body>
</html>

* Notice that in a do while loop the condition is tested AFTER executing the statements within the loop. This means that the do while loop would execute its statements at least once, even if the condition fails the first time.
for  statement
* The for loop is used when you know in advance how many times the script should run.

for(initialization; condition; flow)
{
/*Code to be executed*/
}

* Initialization is assigning initial value to the counter variable. Condition is any expression which will be evaluated as true of false. Flow is any change in counter variable so that loop reaches to its termination.

Example

<html>
<body>
<?php
for($x=1;$x<=10;$x++)
{
echo $x;
}
?>
</body>
</html>



Note: foreach loop will be covered with Arrays.

<<Prev     Home     Next>>

Ankit B Desai

Pages

Monday, July 10, 2017

Connecting PHP with MySQL

Saturday, July 8, 2017

PHP Control Instruction