Computer Science Blog: September 2015

Monday, 7 September 2015

Display the record in table format and allow all operations on that table

The following code will allow the user to display all the records in the table format. It will allow edit, delete and insert operation on a particular record. Note that there will be one line change in the code of udpate.php and delete.php file that has been displayed in this code.
disp.php

<?php
   $cn=mysql_connect("localhost","root") or die(mysql_error());
   $db=mysql_select_db("student",$cn) or die(mysql_error());
   $row=mysql_query("select * from stud",$cn) or die(mysql_error());
   $no=mysql_num_rows($row);
   echo "<h2> Student Details </h2>";
   echo "<table border=3>";
   echo "<tr><th>No<th>Name<th>City<th colspan=2><a href=stud.html>Add New Record</a>";
   for($i=1;$i<=$no;$i++)
   {
       $data=mysql_fetch_row($row);
       echo "<tr>";
       echo "<td>".$data[0];
       echo "<td>".$data[1];
       echo "<td>".$data[2];
       echo "<td> <a href=edit.php?id=".$data[0].">Modify</a>";
       echo "<td> <a href=delete.php?id=".$data[0].">Delete</a>";
   }
?>

In edit.php file,
<?php
    $no=$_REQUEST['id'];
...........
...........
............

?>
in place of
<?php
     $no=$_POST['id'];
......
......
.....
?>
Remaining code is same .........

In delete.php file,
<?php
    $no=$_REQUEST['id'];
............
............
............
header("Location:disp.php");
?>
in place of
<?php
     $no=$_POST['id'];
.........
..........
.........
echo "Record deleted";
?>
Remaining code is same .........

Friday, 4 September 2015

Display the record of the emp in table format

To display the record in the table format, we will need to write the following code:
dispemp.php

<?php
    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("student",$cn) or die(mysql_error());
    $row=mysql_query("select * from emp",$cn) or die(mysql_error());
    $no=mysql_num_rows($row);
    echo "<h2> Emp Details </h2>";
    echo "<table border=3>";
    echo "<tr><th>No<th>Name<th>Dept<th>Salary<th>TA<th>DA<Th>HRA<th>PF<th>NET";
    for($i=1;$i<=$no;$i++)
    {
        $data=mysql_fetch_row($row);
        echo "<tr>";
        echo "<td>".$data[0];
        echo "<td>".$data[1];
        echo "<td>".$data[2];
        echo "<td>".$data[3];
        echo "<td>".$data[4];
        echo "<td>".$data[5];
        echo "<td>".$data[6];
        echo "<td>".$data[7];
        echo "<td>".$data[8];
    }
    echo "</table>";
?>

Delete the record in emp table

To delete the record in the emp table, we will need to create the design form so that user can input the value of his/her choice to delete the record....
delemp.html

<html>
    <head>
    </head>
    <body>
        <form action=delemp.php method=POST>
        Enter no<input type=text name=n>
        <input type=submit value=DELETE>
        </form>
    </body>
</html>

To check if the record exists or not, we will need to write the code in PHP file...If the record is found, then that record will be deleted...If not found, then we will need to display appropriate message as follows:

delemp.php

<?php
    $no=$_POST['n'];
    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("student",$cn) or die(mysql_error());
    $row=mysql_query("select * from emp where id=$no",$cn) or die(mysql_error());
    $n=mysql_num_rows($row);
    if($n<=0)
    {
        echo "Record not found";
        exit;
    }
    else
    {
        $q=mysql_query("delete from emp where id=$no");
        echo $q." Record Deleted";

    }
?>

Update the record in emp table

After inserting the record in the emp table, it is necessary to create a file in PHP that can help the user to update the record in emp table...
To update the record in the emp table, we will need to create form design to get the value by the user as follows........

editemp.html

<html>
    <head>
    </head>
    <body>
        <form action=editemp.php method=POST>
        Enter no<input type=text name=n>
        <input type=submit value=EDIT>
        </form>
    </body>
</html>

The above code will create the form to get the value from the user...Now to check if the record exists or not, we will need to create PHP file......If record not found then we have to display the message like "Record not found"......If record is found, then we will display the file in the editable format so that the user can edit the record in any fields as per his/her choice.......To perform this function, we will need to write the following code......

editemp.php

<?php
    $no=$_POST['n'];
    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("student",$cn) or die(mysql_error());
    $row=mysql_query("select * from emp where id=$no",$cn) or die(mysql_error());
    $n=mysql_num_rows($row);
    if($n<=0)
    {
        echo "Record not found";
        exit;
    }
    else
    {
        $data=mysql_fetch_row($row);
        echo "<form action=updemp.php method=POST>";
        echo "No<input type=text value=$data[0] name=id>";
        echo "<br> Name<input type=text value=$data[1] name=nm>";
        if($data[2]=="comp")
        {
            echo "<br> Dept<select name='dept'><option value=$data[2]>Computer
                            <option value='mgmt'>Management
                            <option value='comm'>Commerce
                    </select>";
        }
        else if($data[2]=="mgmt")
        {
            echo "<br> Dept<select name='dept'><option value=$data[2]>Management
                            <option value='comp'>Computer
                            <option value='comm'>Commerce
                    </select>";
        }
        else if($data[2]=="comm")
        {
            echo "<br> Dept<select name='dept'><option value=$data[2]>Commerce
                            <option value='comp'>Computer
                            <option value='mgmt'>Management
                    </select>";
        }
        echo "<br>Salary <input type=text name=sal value=$data[3]>";
        echo "<input type=submit value=UPDATE>";
        echo "<br><input type=hidden value=$no name=oldno>";
        echo "</form>";
    }
?>

This will display the record in the editable form format if the record is found ...........In this form, the user will edit the record in any fields.....After that the record will be needed to update in the database....To do this, we will need to write the following code:

updemp.php

<?php
    $nid=$_POST['id'];
    $nnm=$_POST['nm'];
    $ndep=$_POST['dept'];
    $nsal=$_POST['sal'];
    $oid=$_POST['oldno'];
    if($ndep=="comp")
    {
        $ta=$nsal*0.10;
        $da=$nsal*1.10;
        $hra=$nsal*0.25;
    }
    else if($ndep=="mgmt")
    {
        $ta=$nsal*0.12;
        $da=$nsal*1.05;
        $hra=$nsal*0.20;
    }
    else if($ndep=="comm")
    {
        $ta=$sal*0.09;
        $da=$sal*1.11;
        $hra=$sal*0.23;
    }
    $pf=$nsal*0.10;
    $net=($nsal+$ta+$da+$hra)-$pf;
    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("student",$cn) or die(mysql_error());
    $q=mysql_query("update emp set id=$nid,name='$nnm',dept='$ndep',sal=$nsal,ta=$ta,da=$da,hra=$hra,pf=$pf,net=$net where id=$oid",$cn) or die(mysql_error());
    echo $q." Record Updated";
?>

Please note that proper syntax must be written with the same field name that are in the database, otherwise can result into error.....

Insert record in Emp table

First of all create the database in MYSQL....Then create emp table with the following fields:
ID    INT              3
NAME    VARCHAR    34
DEPT    VARCHAR    34
SAL FLOAT    7
TA    FLOAT    7
DA FLOAT    7
HRA     FLOAT    7
PF    FLOAT    7
NET FLOAT    7
Then create the design of the form to insert the record in EMP table as follows:
empins.html

<html>
    <head><title>Insert Employee</title>
    </head>
    <body>
       <form action=empins.php method=POST>
       Emp ID<input type=text name=id><br>
       Emp name<input type=text name=nm><br>
       Emp Dept<select name=dept>
               <option value="comp">Computer
               <option value="mgmt">Management
               <option value="comm">Commerce
           </select><br>
       Emp Salary<input type=text name=sal><br>
       <input type=submit value=Insert>
       </form>
    </body>
</html>

Then create .php file to write the code of inserting the record in employee

empins.php

<?php
    $id=$_POST['id'];
    $nm=$_POST['nm'];
    $dep=$_POST['dept'];
    $sal=$_POST['sal'];
    if($dep=="comp")
    {
       $ta=$sal*0.10;
       $da=$sal*1.10;
       $hra=$sal*0.25;
    }
    else if($dep=="mgmt")
    {
       $ta=$sal*0.12;
       $da=$sal*1.05;
       $hra=$sal*0.20;
    }
    else if($dep=="comm")
    {
       $ta=$sal*0.09;
       $da=$sal*1.11;
       $hra=$sal*0.23;
    }
    $pf=$sal*0.10;
    $net=($sal+$ta+$da+$hra)-$pf;
    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("student",$cn) or die(mysql_error());
    mysql_query("insert into emp values($id,'$nm','$dep',$sal,$ta,$da,$hra,$pf,$net)",$cn) or die(mysql_error());
    echo "Record inserted";
?>
Please note that the count may vary as per the users' choice

Wednesday, 2 September 2015

Delete the record from the student table using PHP MYSQL

To delete the record from the table, the user will need to enter the value in the textbox in HTML Design form...
delete.html
<html>
    <head>
    </head>
    <body>
        <form action=delete.php method=POST>
        Enter no<input type=text name=n>
        <input type=submit value=DELETE>
        </form>
    </body>
</html>

After inputting the value, it will be necessary to check if the value exists or not. If record does not exists then you have to display the appropriate message like "Record not found" or if record exists then that record will be deleted.....

delete.php

<?php
    $no=$_POST['n'];
    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("sybca",$cn) or die(mysql_error());
    $row=mysql_query("select * from student where no=$no",$cn) or die(mysql_error());
    $n=mysql_num_rows($row);
    if($n<=0)
    {
       echo "Record not found";
       exit;
    }
    else
    {
       mysql_query("delete from student where no=$no");
       echo "Record Deleted";

    }
?>

Update the student table using PHP MYSQL

First of all create HTML Design to take the value of the record like no (recommended Primary Key Field) with the following code:
edit.html
<html>
    <head>
    </head>
    <body>
        <form action=edit.php method=POST>
        Enter no<input type=text name=n>
        <input type=submit value=EDIT>
        </form>
    </body>
</html>

Afterward, create edit.php file to check if the value entered by the user exists or not.......If the value exists then display that record in editable format or if not exists then display appropriate message as follows:
edit.php
<?php
    $no=$_POST['n'];
    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("sybca",$cn) or die(mysql_error());
    $row=mysql_query("select * from student where no=$no",$cn) or die(mysql_error());
    $n=mysql_num_rows($row);
    if($n<=0)
    {
        echo "Record not found";
        exit;
    }
    else
    {
        $data=mysql_fetch_row($row);
        echo "<form action=update.php method=POST>";
        echo "No<input type=text value=$data[0] name=no>";
        echo "<br> Name<input type=text value=$data[1] name=nm>";
        echo "<br> City<input type=text value=$data[2] name=ct>";
        echo "<input type=submit value=UPDATE>";
        echo "<br><input type=hidden value=$no name=oldno>";
       echo "</form>";
    }
?>

After this, if record found, it will be displayed in editable format. U can make the changes as per your need in any of the fields. Then when you will click on Update button, the record will be updated in the database.. for that you have to create another file update.php as follows:

update.php

<?php
    $no=$_POST['no'];
    $nm=$_POST['nm'];
    $ct=$_POST['ct'];
    $old=$_POST['oldno'];
    $cn=mysql_connect("localhost","root");
    $db=mysql_select_db("sybca",$cn);
    mysql_query("update student set no=$no,name='$nm',city='$ct' where no=$old",$cn);
    echo "Record Updated";
?>

Insert the record in the student table using PHP MYSQL

First of all create database of your choice name and then create table named student with the following fields in MYSQL :
NO                   INT                3 PK
NAME             VARCHAR   34
CITY                VARCHAR   34

Then create HTML design as follows

insstud.html

<html>
    <head>
    </head>
    <body>
        <form action=stud.php method=GET>
        No<input type=text name=sno><br>
        Name<input type=text name=nm><br>
        City<input type=text name=ct><br>
        <input type=submit value=INSERT>
        </form>
    </body>
</html>


stud.php

<?php
    $sno=$_GET['sno'];
    $nm=$_GET['nm'];
    $ct=$_GET['ct'];

    $cn=mysql_connect("localhost","root") or die(mysql_error());
    $db=mysql_select_db("student",$cn);
    mysql_query("insert into stud values($sno,'$nm','$ct')",$cn) or die(mysql_error());
    echo "Record successfully inserted."
?>

Then execute the program in the browser.....

Important note:
This program is without validation and will be checked according to the fields and data types in database.....