Pages

Monday, 7 March 2011

Sem 2 PHP Database

Employee database


//html code

<h2><center>INSERTION FOR DEPARTMENT TABLE</CENTER></h2>

<form action=employ1.php method=post>

<table border=0>

<tr>
<td><font size=5>Dept No</td>
<td><input type=text name=dno></td>
</tr>

<tr>
<td><font size=5>Dept name</td>
<td><input type=text name=dnm></td>
</tr>

<tr>
<td><font size=5>Location</td><td>
<input type=text name=loc></td>
</tr>

<tr>
<td><input type=submit value=insert></td>

<td><input type=reset value=reset></td>
</tr>

</table>

</form>



<H2><center>INSERTION FOR EMPLOYEE TABLE</CENTER></h2>

<form action=employ2.php method=post>
<table border=0>

<tr>
<td><font size=5>Dept No</td><td>
<input type=text name=dno></td>
</tr>

<tr><td><font size=5>Emp no</td>
<td><input type=text name=eno></td></tr>

<tr><td><font size=5>Emp name</td>
<td><input type=text name=enm></td></tr>

<tr><td><font size=5>Address</td>
<td><input type=text name=addr></td></tr>

<tr><td><font size=5>Phone no</td>
<td><input type=text name=phno></td></tr>

<tr><td><font size=5>Salary</td>
<td><input type=text name=sal></td></tr>

<tr><td><input type=submit value=insert></td>

<td><input type=reset value=reset></td></tr>

</table>
</form>


//php1.php

<?
 $dno=$_POST['dno'];
 $dnm=$_POST['dnm'];
 $loc=$_POST['loc'];
 $con=mysql_connect("localhost","root");
 if(!$con)
 echo"<h2>CONNECTION FAILED<br>";
 else
 echo"<h2>CONNECTION ESTABLISHED<br>";

 mysql_select_db("Sneha",$con);
 $res=mysql_query("insert into department values($dno,'$dnm','$loc')");
 if($res)
 echo"<h2>RECORD INSERTED SUCCESSFULLY<br>";
 else
 echo"<h2>RECORD NOT INSERTED<br>";
 mysql_close($con);
?>


//php2.php

<?
$eno=$_POST['eno'];
$enm=$_POST['enm'];
$addr=$_POST['addr'];
$phno=$_POST['phno'];
$sal=$_POST['sal'];
$dno=$_POST['dno'];
$con=mysql_connect("localhost","root");
mysql_select_db("Sneha",$con);
$res=mysql_query("select * from department where dno=$dno");
if($row=mysql_fetch_array($res))
{
$res1=mysql_query("insert into employee values($eno,'$enm','$addr',$phno,$sal,$dno)");
if($res1)
echo"<h2>INSERTION OF NEW EMPLOYEE IS DONE";
else
echo"<h1>INSERTION NOT DONE";
}
else
echo"<H1>RECORD NOT INSERTED DUE TO INVALID DEPARTMENT DNO</H1>";
mysql_connect($con);
?>


*****************************************************************************

mysql> select * from employee;
+-----+--------+----------+-----------+-------+------+
| eno | enm    | addr     | phno      | sal   | dno  |
+-----+--------+----------+-----------+-------+------+
|   1 | Raj    | Pune     |  27671378 | 15000 |    1 |

|   2 | Priya  | Talegoan |  27645678 | 16000 |    2 |
|   3 | Samrat | Mumbai   | 276456678 | 10000 |    1 |
|   4 | Gunjan | Rajkot   |   2745678 | 17000 |    2 |
|   5 | Mayank | Pimpri   |   2798765 | 27000 |    1 |
|   6 | Nupur  | Mumbai   |   2767689 | 23000 |    3 |
|   7 | Leela  | Dehuroad | 276768934 | 23500 |    3 |
+-----+--------+----------+-----------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from department;
+-----+-------------+-----------+
| dno | dnm         | loc       |
+-----+-------------+-----------+
|   1 | Computer    | Bangalore |
|   2 | Electronics | Mumbai    |
|   3 | Maths       | Pune      |
+-----+-------------+-----------+
3 rows in set (0.00 sec)


//html code


<html>

<body>

<form action=employ3.php method=post>

<font size=5><b>Enter department name</b>
<input type=text name=dnm><br>

<input type=submit value=show>

<input type=reset value=reset>

</form>

</body>

</html>






//php code

<?
 $dnm=$_POST['dnm'];
 $con=mysql_connect("localhost","root");
 mysql_select_db("Sneha",$con);
 $res=mysql_query("select sal from employee,department where department.dno=employee.dno and dnm='$dnm'");
 $sum=0;
 $min=0;
 $max=0;
 while($row=mysql_fetch_array($res))
 {
  $cursal=$row['sal'];
  if($cursal>$max)
  $max=$cursal;
  else
  $min=$cursal;
  $sum=$min+$max;
 }
 echo"<H1>MAXIMUM AND MINIMUM SALARY OF ".$dnm." DEPARTMENT IS AS FOLLOWS</H1>";
 echo"<table border=2>";
 echo"<tr><td><b><font size=5>MAXIMUM SALARY</b></td><td><b><font size=5>MINIMUM SALARY</b></td><td><b><font size=5>SUM</b></td></tr>";
 echo"<tr><td><b><font size=5>$max</b></td><td><b><font size=5>$min</b></td><td><b><font size=5>$sum</b></td></tr>";
 echo"</table>";
?>
*******************************************************************************
HOSPITAL DOCTOR DATABASE :

Assignment No:-1          
Que No:2


************DOCTOR-HOSPITAL DATABASE*******************
mysql> select * from doctor;
+-----+---------------+------------------+-----------+
| dno | dnm           | addr             | city      |
+-----+---------------+------------------+-----------+
|   1 | AJIT KALE     | Shop no:22 Nigdi | Pune      |
|   2 | MAYANK SHARMA | Magadi road-24   | Bangalore |
|   3 | ANGAD MEHTA   | Garuda district  | Bhopal    |
|   4 | RAJA RAM      | Chakan road      | Chennai   |
+-----+---------------+------------------+-----------+






mysql> select * from DH;
+------+------+
| dno  | hno  |
+------+------+
|    1 |    1 |
|    2 |    3 |
|    3 |    2 |
|    4 |    1 |
|    3 |    3 |
|    1 |    3 |
+------+------+
6 rows in set (0.00 sec)

mysql> select * from hospital;
+-----+----------+-----------+
| hno | hnm      | city      |
+-----+----------+-----------+
|   1 | Lokmanya | Chinchwad |
|   2 | Sancheti | Kothrud   |
|   3 | Birla    | Pune      |
+-----+----------+-----------+
3 rows in set (0.00 sec)

//html file

<html>
<body>
<form action=dohos.php method=post>
<font size=5>
Enter Hospital Name<input type=text name=hnm><br>
<input type=submit value=submit>
<input type=reset value=reset>
</form>
</body>
</html>




//php file

<?
 $hnm=$_POST['hnm'];
 $con=mysql_connect("localhost","root");
 mysql_select_db("suhas",$con);
 $res=mysql_query("select doctor.dno,dnm,doctor.city,addr from doctor,hospital,DH where doctor.dno=DH.dno and hospital.hno=DH.hno and hnm='$hnm'");
 echo"<font size=5><i><center><b>Doctor Working/Visiting in ". $hnm." Hospital is</b></center></i><br>";
 echo"<table border=2 width=70%>";
 echo"<th><font size=5>DOCTOR NO</th><th><font size=5>DOCTOR NAME</th><th><font size=5>CITY</th><th><font size=5>ADDRESS</th>";
 while($row=mysql_fetch_array($res))
 {
  echo"<tr>";
  echo"<td align=center>"."<font size=6>".$row['dno']."</td>";
  echo"<td align=center>"."<font size=6>".$row['dnm']."</td>";
  echo"<td align=center>"."<font size=6>".$row['addr']."</td>";
  echo"<td align=center>"."<font size=6>".$row['city']."</td>";
  echo"</tr>";
 }
 echo"</table>";
 mysql_close($con);
?>
*******************************************************************************


MOVIE DATABASE:


mysql> create table movies(mno int primary key,mnm varchar(20),year varchar(20));
Query OK, 0 rows affected (0.09 sec).
mysql> select * from movies;
+-----+----------+------+
| mno | mnm      | year |
+-----+----------+------+
|   1 | dabang   | 2010 |
|   2 | 3 idiots | 2009 |
|   3 | Wanted   | 2009 |
|   4 | Veer     | 2010 |
|   5 | Ghajini  | 2008 |
+-----+----------+------+
5 rows in set (0.00 sec)

mysql> create table actor(ano int primary key,anm varchar(15),addr varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor;
+-----+-------------+-----------+
| ano | anm         | addr      |
+-----+-------------+-----------+
|   1 | Salman khan | mumbai    |
|   2 | Aamir khan  | Delhi     |
|   3 | Siddharth   | Bangalore |
+-----+-------------+-----------+
3 rows in set (0.00 sec)
mysql> create table MA(mno int references movies(mno),ano int references actor(ano),budget int);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from MA;
+------+------+--------+
| mno  | ano  | budget |
+------+------+--------+
|    1 |    1 |  20000 |
|    2 |    2 | 230000 |
+------+------+--------+
2 rows in set (0.00 sec)


//HTML FILE
<html>
<body>
<form action=movie1.php method=post>
<font size=5>
Enter Actor Name<input type=text name=anm><br>
<input type=submit value=submit>
<input type=reset value=reset>
</form>
</body>
</html>

//PHP FILE

<?
 $anm=$_POST['anm'];
 $con=mysql_connect("localhost","root");
 mysql_select_db("manisha",$con);
 $res=mysql_query("select movies.mno,mnm,year from movies,actor,MA where movies.mno=MA.mno and actor.ano=MA.ano and anm='$anm'");
 echo"<font size=5><i><center><b>Actor ". $anm." acted in following MOVIES</b></center></i><br>";
 echo"<table border=2 width=70%>";
 echo"<th><font size=5>MOVIE NO</th><th><font size=5>MOVIE NAME</th><th><font size=5>RELEASE YEAR</th>";
 while($row=mysql_fetch_array($res))
 {
  echo"<tr>";
  echo"<td align=center>"."<font size=6>".$row['mno']."</td>";
  echo"<td align=center>"."<font size=6>".$row['mnm']."</td>";
  echo"<td align=center>"."<font size=6>".$row['year']."</td>";
  echo"</tr>";
 }
 echo"</table>";
 mysql_close($con);
?>
********************************************************************************

 //HTML SCRIPT
<form action=movie2.php method=post>
<font size=5>
<center><b>INSERTION FOR MOVIES TABLE</b></center>
<table border=0>
<tr><td><font size=5>MOVIE NO</td><td><input type=text name=mno></td></tr>
<tr><td><font size=5>MOVIE Name</td><td><input type=text name=mnm></td></tr>
<tr><td><font size=5>MOVIE Year</td><td><input type=text name=year></td></tr>
<tr><td><input type=submit value=submit></td><td><input type=reset value=reset></td></tr>
</table>
</form>

//PHP SCRIPT

<?
 $mno=$_POST['mno'];
 $mnm=$_POST['mnm'];
 $year=$_POST['year'];
 $con=mysql_connect("localhost","root");
 if(!$con)
 echo"<h2>CONNECTION FAILED<br>";
 else
 echo"<h2>CONNECTION ESTABLISHED<br>";

 mysql_select_db("Sneha",$con);
 $res=mysql_query("insert into movies values($mno,'$mnm','$year')");
 if($res)
 echo"<h2><I>RECORD INSERTED SUCCESSFULLY</I><br>";
 else
 echo"<h2>RECORD NOT INSERTED<br>";
 mysql_close($con);
?>


******** TO CHECK RECORD IS INSERTED OR NOT*************
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use Sneha;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

//AFTER INSERTION OF MNO=9

Database changed
mysql> select * from movies;
+-----+------------------+------+
| mno | mnm              | year |
+-----+------------------+------+
|   1 | dabang           | 2010 |
|   2 | 3 idiots         | 2009 |
|   3 | Wanted           | 2009 |
|   4 | Veer             | 2010 |
|   5 | Ghajini          | 2008 |
|   6 | Taare Zameen Par | 2008 |
|   7 |  Maan            | 2005 |
|   8 |  Mangal Pandey   | 2005 |
|   9 |  Bodyguard       | 2011 |  |
+-----+------------------+------+
10 rows in set (0.00 sec)

mysql>


****************************************************************************

<html>
<body>
<form action=movie3.php method=post>
<font size=5>
<center><b>TO UPDATE RELEASE YEAR</b></CENTER>
<table border=0>
<tr><td><font size=5>NEW RELEASE YEAR</td><td><input type=text name=year></td></tr>
<tr><td><font size=5>MOVIE NAME</td><td><input type=text name=mnm></td></tr>
<tr><td><font size><input type=submit value=submit></td><td><input type=reset value=reset></td></tr>
</form>
</html>
</body>

PHPcode:
<?
 $year=$_POST['year'];
 $mnm=$_POST['mnm'];
 $con=mysql_connect("localhost","root");
 if(!$con)
 echo"<h2>CONNECTION FAILED<br>";
 else
 echo"<h2>CONNECTION ESTABLISHED<br>";

 mysql_select_db("Sneha",$con);
 $res=mysql_query("update movies set year='$year' where mnm='$mnm'");
 if($res)
 echo"<h2>RECORD UPDATED SUCCESSFULLY<br>";
 else
 echo"<h2>RECORD NOT UPDATED<br>";
 mysql_close($con);
?>
*******************************************************************************
******************************************************************************

STUDENT COMPITITON :

Assignment No:-1
Que No:4


mysql> create table students(sno int primary key,snm varchar(20),class int);
Query OK, 0 rows affected (0.03 sec)
mysql> create table competetion(cno int primary key,cnm varchar(20),type varchar(20)); Query OK, 0 rows affected (0.00 sec)
mysql> create table SC(sno int references students(sno),cno int references competetion(cno),rank int);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
ERROR 1146 (42S02): Table 'Sneha.student' doesn't exist
mysql> select * from students;
+-----+----------+-------+
| sno | snm      | class |
+-----+----------+-------+
|   1 | Ram      |     3 |
|   2 | Suresh   |     6 |
|   3 | Sneha    |     9 |
|   4 | Nimisha  |     9 |
|   5 | Vijay    |     8 |
|   6 | Suprith  |     5 |
|   7 | Sonalish |     7 |
|   8 | Navin    |     6 |
+-----+----------+-------+
8 rows in set (0.00 sec)

mysql> select * from competetion;
+-----+------------+-------------------+
| cno | cnm        | type              |
+-----+------------+-------------------+
|   1 | High jump  | Sport             |
|   2 | Long jump  | Sport             |
|   3 | Quiz       | Academic          |
|   4 | Group Song | Cultural Activity |
+-----+------------+-------------------+
4 rows in set (0.00 sec)

mysql> select * from SC;
+------+------+------+
| sno  | cno  | rank |
+------+------+------+
|    1 |    1 |    2 |
|    2 |    5 |    1 |
|    3 |    1 |    1 |
|    4 |    3 |    1 |
|    5 |    2 |    1 |
|    6 |    4 |    2 |
|    7 |    4 |    1 |
|    8 |    3 |    2 |
+------+------+------+
8 rows in set (0.01 sec)

//html file

<html>
<body>
<form action=comstu.php method=post>
<font size=5>
Enter Competetion Name<input type=text name=cnm><br>
<input type=submit value=submit>
<input type=reset value=reset>
</form>
</body>
</html>


//php file
<?
 $cnm=$_POST['cnm'];
 $con=mysql_connect("localhost","root");
 mysql_select_db("Sneha",$con);
 $res=mysql_query("select students.sno,snm,class from students,competetion,SC where students.sno=SC.sno and competetion.cno=SC.cno and rank=1 and cnm='$cnm'");
 echo"<font size=5><i><center><b>Student came first in ". $cnm." competetion is</b></center></i><br>";
 echo"<table border=2 width=70%>";
 echo"<th><font size=5>STUDENT NUMBER</th><th><font size=5>STUDENT NAME</th><th><font size=5>CLASS</th>";
 while($row=mysql_fetch_array($res))
 {
  echo"<tr>";
  echo"<td align=center>"."<font size=6>".$row['sno']."</td>";
  echo"<td align=center>"."<font size=6>".$row['snm']."</td>";
  echo"<td align=center>"."<font size=6>".$row['class']."</td>";
  echo"</tr>";
 }
 echo"</table>";
 mysql_close($con);
?>











Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)