PHP - PHP 와 오라클 연동 및 테이블 설정
2022. 11. 24. 16:49ㆍPHP
Oracle 접속 : oconn.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name, $password, $server) or die('Could not connect:');
?>
<? echo("<br>"); show_source(__FILE__); ?>
MySQL 접속 : mconn.php
<?
$conn=@mysql_connect("localhost","php","php") or die('Could not connect:');
mysql_select_db("php",$conn);
echo("MySQL 접속 ₩$conn = $conn");
?>
PHP Oracle 연동
학생 테이블 입력 html
st_in.html
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title> student 입력폼 </title>
</head>
<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<form name="insert" action="st_in.php" method="POST">
<center>
<table border="0" width="250">
<tr>
<td width="50"><p align="center">학번</p></td>
<td> <input type="text" name="sno"></td>
</tr>
<tr>
<td width="50"><p align="center">이름</p></td>
<td> <input type="text" name="sname"></td>
</tr>
<tr>
<td width="50"><p align="center">성별</p></td>
<td> 남<input type="radio" name="sex" value="남">,
여<input type="radio" name="sex" value="여"></td>
</tr>
<tr>
<td width="50"><p align="center">학과</p></td>
<td> <select name="major" size="1">
<option value="화학">화학</option>
<option value="생물">생물</option>
<option value="물리">물리</option>
<option value="유공">유공</option>
<option value="식영">식영</option></td>
</tr>
<tr>
<td width="50"><p align="center">학년</p></td>
<td> 1<input type="radio" name="syear" value="1">,
2<input type="radio" name="syear" value="2">,
3<input type="radio" name="syear" value="3">,
4<input type="radio" name="syear" value="4"></td>
</tr>
<tr>
<td width="50"><p align="center">학점</p></td>
<td> <input type="text" name="avr"></td>
</tr>
<tr>
<td colspan="2">
<p align="center"><input type="submit" name="확인" value="확인"></p>
</td>
</tr>
</table>
</form>
</body></html>
<? echo("<br>"); show_source(__FILE__); ?>
학생 테이블에 입력된 데이터를 입력할 프로그램
- st_in.html로부터 데이터를 전달받는다
st_in.php
<?
$sno = $_POST['sno'];
$sname = $_POST['sname'];
$sex = $_POST['sex'];
$major = $_POST['major'];
$syear = $_POST['syear'];
$avr = $_POST['avr'];
require('conn.php');
$sql="insert into student (sno, sname, sex, major, syear, avr)
values ('$sno', '$sname', '$sex', '$major', '$syear', '$avr')";
$result=oci_parse($conn,$sql);
$re=oci_execute($result);
oci_free_statement($result);
oci_close($conn);
if($re) echo("정상 입력 되었습니다.");
// echo(" <meta http-equiv='Refresh' content = '0; URL=st_vi.php'>");
?>
<? echo("<br>"); show_source(__FILE__); ?>
학생 테이블의 내용을 출력할 프로그램 작성
st_vi.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name, $password, $server) or die('Could not connect:');
?>
<?
echo("<a href=./st_in.html>자료 입력하기</a><hr>");
require('conn.php');
//==========================================================================
$del=$_GET['del'];
if (empty($del)){}
else {
$sql="delete from student where sno='$del'";
$result=oci_parse($conn,$sql);
oci_execute($result);
oci_free_statement($result);
}
//==========================================================================
//==========================================================================
$search = $_GET['search'];
if (empty($search)) // $search 변수가 없을 때 $sql
{
$sql="select sno,sname,sex,major,syear,to_char(avr,'0.00') avr
from student order by sno";
}
else // $search 변수가 있을 때 $sql
{
$sql="select sno,sname,sex,major,syear,to_char(avr,'0.00') avr
from student
where sname like '%{$search}%' order by sno";
}
//==========================================================================
/*$sql="select sno, sname, sex, major, syear, to_char(avr,'0.00') avr
from student order by sno";*/
$result=oci_parse($conn,$sql);
oci_execute($result);
$row_num=oci_fetch_all($result, $row); // $row_num : 전제 행의 수
oci_free_statement($result);
oci_close($conn);
echo("Row의 개수는 $row_num 입니다.<br><hr>");
$scale=5;
$start = $_GET['start'];
if (empty($start)){ $start=0;}
echo("<table border='0'>");
echo("
<tr>
<td width='50'><p align='center'>학번</p></td>
<td width='80'><p align='center'>학생이름</p></td>
<td width='50'><p align='center'>성별</p></td>
<td width='50'><p align='center'>학년</p></td>
<td width='50'><p align='center'>학과</p></td>
<td width='70'><p align='center'>평균평점</p></td>
</tr>
");
for ($i=$start; $i<($start+$scale); $i++) {
if($i<$row_num){
echo("
<tr>
<td width='50'><p align='center'>{$row['SNO'][$i]}</p></td>
<td width='80'><p align='center'>{$row['SNAME'][$i]}</p></td>
<td width='20'><p align='center'>{$row['SEX'][$i]}</p></td>
<td width='20'><p align='center'>{$row['SYEAR'][$i]}</p></td>
<td width='50'><p align='center'>{$row['MAJOR'][$i]}</p></td>
<td width='50'><p align='center'>{$row['AVR'][$i]}</p></td>
<td width='30'><a href=./st_vi.php?del={$row['SNO'][$i]}>del</a></td>
</tr>
");
}
}
echo("</table><hr>");
$p=$start-$scale;
$n=$start+$scale;
if($p>=0)
echo("<a href=./st_vi.php?start=$p&search=$search>[이전페이지]</a> ");
else
echo("이전페이지 ");
if($n<$row_num)
echo("<a href=./st_vi.php?start=$n&search=$search>[다음페이지]</a>");
else
echo("다음페이지");
?>
<form name='search' method='GET' action='st_vi.php'>
검색창 <input type='text' name='search'>
<input type='submit' name='확인' value='확인'>
</form>
<? echo("<br>"); show_source(__FILE__); ?>
과목 테이블
co_in.html
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title> course 입력폼 </title>
</head>
<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<form name="insert" action="co_in.php" method="POST"> // POST 방식으로 PHP 파일에
<center> // 입력 값을 전달한다.
<table border="0" width="250">
<tr>
<td width="50"><p align="center">과목번호</p></td>
<td> <input type="text" name="cno"></td>
</tr>
<tr>
<td width="50"><p align="center">과목명</p></td>
<td> <input type="text" name="cname"></td>
</tr>
<tr>
<td width="50"><p align="center">학점</p></td>
<td> <input type="text" name="st_num"></td>
</tr>
<tr>
<td width="50"><p align="center">담당교수번호</p></td>
<td> <input type="text" name="pno"></td>
</tr>
<tr>
<td colspan="2">
<p align="center"><input type="submit" name="확인" value="확인"></p>
</td>
</tr>
</table>
</form>
</body></html>
<? echo("<br>"); show_source(__FILE__); ?>
co_in.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name,$password,$server) or die('not connect:');
?>
<? echo("<br>"); show_source(__FILE__); ?>
<?
echo("<a href=./co_in.html>자료 입력하기</a><hr>");
require('conn.php');
$sql="select cno, cname, st_num, c.pno, pname
from course c, professor p where c.pno = p.pno order by cno";
$result=oci_parse($conn,$sql);
oci_execute($result);
$row_num=oci_fetch_all($result, $row); // $row_num : 전제 행의 수
oci_free_statement($result);
oci_close($conn);
echo("Row의 개수는 $row_num 입니다.<br><hr>");
echo("<table border='0'>");
echo("
<tr>
<td width='50'><p align='center'>CNO</p></td>
<td width='100'><p align='center'>CNAME</p></td>
<td width='20'><p align='center'>ST_NUM</p></td>
<td width='20'><p align='center'>PNO</p></td>
<td width='100'><p align='center'>PNAME</p></td>
</tr>
");
for($i=0; $i<$row_num; $i++) {
echo("
<tr>
<td width='50'><p align='center'>{$row['CNO'][$i]}</p></td>
<td width='100'><p align='center'>{$row['CNAME'][$i]}</p></td>
<td width='20'><p align='center'>{$row['ST_NUM'][$i]}</p></td>
<td width='20'><p align='center'>{$row['PNO'][$i]}</p></td>
<td width='100'><p align='center'>{$row['PNAME'][$i]}</p></td>
</tr>
");
}
echo("</table>");
?>
<? echo("<br>"); show_source(__FILE__); ?>
co_vi.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name,$password,$server) or die('not connect:');
?>
<?
echo("<a href=./co_in.html>자료 입력하기</a><hr>");
require('conn.php');
//==========================================================================
$del=$_GET['del'];
if (empty($del)){}
else {
$sql="delete from course where cno='$del'";
$result=oci_parse($conn,$sql);
oci_execute($result);
oci_free_statement($result);
}
//==========================================================================
//==========================================================================
$search = $_GET['search'];
if (empty($search)) // $search 변수가 없을 때 $sql
{
$sql="select cno, cname, st_num, c.pno, pname
from course c, professor p where c.pno = p.pno order by cno";
}
else // $search 변수가 있을 때 $sql
{
$sql="select cno, cname, st_num, c.pno, pname
from course c, professor p where c.pno = p.pno and cname like '%{$search}%'
order by cno";
}
//==========================================================================
$result=oci_parse($conn,$sql);
oci_execute($result);
$row_num=oci_fetch_all($result, $row); // $row_num : 전제 행의 수
oci_free_statement($result);
oci_close($conn);
echo("Row의 개수는 $row_num 입니다.<br><hr>");
$scale=5;
$start = $_GET['start'];
if (empty($start)){ $start=0;}
echo("<table border='0'>");
echo("
<tr>
<td width='70'><p align='center'>과목번호</p></td>
<td width='100'><p align='center'>과목이름</p></td>
<td width='50'><p align='center'>학점</p></td>
<td width='70'><p align='center'>교수번호</p></td>
<td width='100'><p align='center'>교수이름</p></td>
</tr>
");
for ($i=$start; $i<($start+$scale); $i++) {
if($i<$row_num){
echo("
<tr>
<td width='50'><p align='center'>{$row['CNO'][$i]}</p></td>
<td width='100'><p align='center'>{$row['CNAME'][$i]}</p></td>
<td width='20'><p align='center'>{$row['ST_NUM'][$i]}</p></td>
<td width='20'><p align='center'>{$row['PNO'][$i]}</p></td>
<td width='100'><p align='center'>{$row['PNAME'][$i]}</p></td>
<td width='30'><a href=./co_vi.php?del={$row['CNO'][$i]}>del</a></td>
</tr>
");
}
}
echo("</table>");
$p=$start-$scale;
$n=$start+$scale;
if($p>=0)
echo("<a href=./co_vi.php?start=$p&search=$search>[이전페이지]</a> ");
else
echo("이전페이지 ");
if($n<$row_num)
echo("<a href=./co_vi.php?start=$n&search=$search>[다음페이지]</a>");
else
echo("다음페이지");
?>
<form name='search' method='GET' action='co_vi.php'>
검색창 <input type='text' name='search'>
<input type='submit' name='확인' value='확인'>
</form>
<? echo("<br>"); show_source(__FILE__); ?>
교수 테이블
pr_in.html
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title> professor 입력폼 </title>
</head>
<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<form name="insert" action="pr_in.php" method="POST"> // POST 방식으로 PHP 파일에
<center> // 입력 값을 전달한다.
<table border="0" width="250">
<tr>
<td width="50"><p align="center">교수번호</p></td>
<td> <input type="text" name="pno"></td>
</tr>
<tr>
<td width="50"><p align="center">이름</p></td>
<td> <input type="text" name="pname"></td>
</tr>
<tr>
<td width="50"><p align="center">학과</p></td>
<td> <select name="section" size="1">
<option value="화학">화학</option>
<option value="생물">생물</option>
<option value="물리">물리</option>
<option value="유공">유공</option>
<option value="식영">식영</option></td>
</tr>
<tr>
<td width="50"><p align="center">지위</p></td>
<td> <select name="orders" size="1">
<option value="화학">정교수</option>
<option value="생물">부교수</option>
<option value="물리">조교수</option>
</tr>
<tr>
<td width="50"><p align="center">고용일</p></td>
<td> <input type="text" name="hiredate"></td>
</tr>
<tr>
<td colspan="2">
<p align="center"><input type="submit" name="확인" value="확인"></p>
</td>
</tr>
</table>
</form>
</body></html>
<? echo("<br>"); show_source(__FILE__); ?>
pr_in.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name,$password,$server) or die('not connect:');
?>
<? echo("<br>"); show_source(__FILE__); ?>
<?
echo("<a href=./pr_in.html>자료 입력하기</a><hr>");
require('conn.php');
$sql="select pno, pname, section, orders, TO_CHAR(hiredate,'YYYY-MM-DD') hiredate
from professor order by pno";
$result=oci_parse($conn,$sql);
oci_execute($result);
$row_num=oci_fetch_all($result, $row); // $row_num : 전제 행의 수
oci_free_statement($result);
oci_close($conn);
echo("Row의 개수는 $row_num 입니다.<br><hr>");
echo("<table border='0'>");
echo("
<tr>
<td width='50'><p align='center'>PNO</p></td>
<td width='80'><p align='center'>PANME</p></td>
<td width='80'><p align='center'>SECTION</p></td>
<td width='80'><p align='center'>ORDERS</p></td>
<td width='100'><p align='center'>HIREDATE</p></td>
</tr>
");
for($i=0; $i<$row_num; $i++) {
echo("
<tr>
<td width='50'><p align='center'>{$row['PNO'][$i]}</p></td>
<td width='80'><p align='center'>{$row['PNAME'][$i]}</p></td>
<td width='80'><p align='center'>{$row['SECTION'][$i]}</p></td>
<td width='80'><p align='center'>{$row['ORDERS'][$i]}</p></td>
<td width='100'><p align='center'>{$row['HIREDATE'][$i]}</p></td>
</tr>
");
}
echo("</table>");
?>
<? echo("<br>"); show_source(__FILE__); ?>
pr_vi.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name,$password,$server) or die('not connect:');
?>
<?
echo("<a href=./pr_in.html>자료 입력하기</a><hr>");
require('conn.php');
//==========================================================================
$del=$_GET['del'];
if (empty($del)){}
else {
$sql="delete from professor where pno='$del'";
$result=oci_parse($conn,$sql);
oci_execute($result);
oci_free_statement($result);
}
//==========================================================================
//==========================================================================
$search = $_GET['search'];
if (empty($search)) // $search 변수가 없을 때 $sql
{
$sql="select pno, pname, section, orders, TO_CHAR(hiredate,'YYYY-MM-DD') hiredate
from professor order by pno";
}
else // $search 변수가 있을 때 $sql
{
$sql="select pno, pname, section, orders, TO_CHAR(hiredate,'YYYY-MM-DD') hiredate
from professor where pname like '%{$search}%' order by pno";
}
//==========================================================================
//$sql="select pno, pname, section, orders, TO_CHAR(hiredate,'YYYY-MM-DD') hiredate
//from professor order by pno";
$result=oci_parse($conn,$sql);
oci_execute($result);
$row_num=oci_fetch_all($result, $row); // $row_num : 전제 행의 수
oci_free_statement($result);
oci_close($conn);
echo("Row의 개수는 $row_num 입니다.<br><hr>");
$scale=5;
$start = $_GET['start'];
if (empty($start)){ $start=0;}
echo("<table border='0'>");
echo("
<tr>
<td width='70'><p align='center'>교수번호</p></td>
<td width='80'><p align='center'>교수이름</p></td>
<td width='80'><p align='center'>담당학과</p></td>
<td width='80'><p align='center'>직위</p></td>
<td width='100'><p align='center'>부임일</p></td>
</tr>
");
for ($i=$start; $i<($start+$scale); $i++) {
if($i<$row_num){
echo("
<tr>
<td width='50'><p align='center'>{$row['PNO'][$i]}</p></td>
<td width='80'><p align='center'>{$row['PNAME'][$i]}</p></td>
<td width='80'><p align='center'>{$row['SECTION'][$i]}</p></td>
<td width='80'><p align='center'>{$row['ORDERS'][$i]}</p></td>
<td width='100'><p align='center'>{$row['HIREDATE'][$i]}</p></td>
<td width='30'><a href=./pr_vi.php?del={$row['PNO'][$i]}>del</a></td>
</tr>
");
}
}
echo("</table><hr>");
$p=$start-$scale;
$n=$start+$scale;
if($p>=0)
echo("<a href=./pr_vi.php?start=$p&search=$search>[이전페이지]</a> ");
else
echo("이전페이지 ");
if($n<$row_num)
echo("<a href=./pr_vi.php?start=$n&search=$search>[다음페이지]</a>");
else
echo("다음페이지");
?>
<form name='search' method='GET' action='pr_vi.php'>
이름검색 <input type='text' name='search'>
<input type='submit' name='확인' value='확인'>
</form>
<? echo("<br>"); show_source(__FILE__); ?>
점수 테이블
sc_in.html
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title> score 입력폼 </title>
</head>
<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<form name="insert" action="sc_in.php" method="POST"> // POST 방식으로 PHP 파일에
<center> // 입력 값을 전달한다.
<table border="0" width="250">
<tr>
<td width="50"><p align="center">학번</p></td>
<td> <input type="text" name="sno"></td>
</tr>
<tr>
<td width="50"><p align="center">과목번호</p></td>
<td> <input type="text" name="cno"></td>
</tr>
<tr>
<td width="50"><p align="center">기말고사점수</p></td>
<td> <input type="text" name="result"></td>
</tr>
<tr>
<td colspan="2">
<p align="center"><input type="submit" name="확인" value="확인"></p>
</td>
</tr>
</table>
</form>
</body></html>
<? echo("<br>"); show_source(__FILE__); ?>
sc_in.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name,$password,$server) or die('not connect:');
?>
<? echo("<br>"); show_source(__FILE__); ?>
<?
echo("<a href=./sc_in.html>자료 입력하기</a><hr>");
require('conn.php');
$sql="select sc.sno, sname, sc.cno, cname, result, grade
from score sc, student, course, scgrade sg
where sc.sno = student.sno and sc.cno = course.cno and result between sg.loscore and sg.hiscore
order by sno";
$result=oci_parse($conn,$sql);
oci_execute($result);
$row_num=oci_fetch_all($result, $row); // $row_num : 전제 행의 수
oci_free_statement($result);
oci_close($conn);
echo("Row의 개수는 $row_num 입니다.<br><hr>");
echo("<table border='0'>");
echo("
<tr>
<td width='50'><p align='center'>SNO</p></td>
<td width='50'><p align='center'>SNAME</p></td>
<td width='80'><p align='center'>CNO</p></td>
<td width='50'><p align='center'>CNAME</p></td>
<td width='20'><p align='center'>RESULT</p></td>
<td width='20'><p align='center'>GRADE</p></td>
</tr>
");
for($i=0; $i<$row_num; $i++) {
echo("
<tr>
<td width='50'><p align='center'>{$row['SNO'][$i]}</p></td>
<td width='50'><p align='center'>{$row['SNAME'][$i]}</p></td>
<td width='80'><p align='center'>{$row['CNO'][$i]}</p></td>
<td width='100'><p align='center'>{$row['CNAME'][$i]}</p></td>
<td width='20'><p align='center'>{$row['RESULT'][$i]}</p></td>
<td width='20'><p align='center'>{$row['GRADE'][$i]}</p></td>
</tr>
");
}
echo("</table>");
?>
<? echo("<br>"); show_source(__FILE__); ?>
sc_vi.php
<?
$server = "st09";
$user_name = "php";
$password = "php";
$conn=@oci_connect($user_name,$password,$server) or die('not connect:');
?>
<?
echo("<a href=./sc_in.html>자료 입력하기</a><hr>");
require('conn.php');
//==========================================================================
$del=$_GET['del'];
$del2=$_GET['del2'];
if (empty($del)){}
else {
$sql="delete from score where sno='$del' and cno='$del2'";
$result=oci_parse($conn,$sql);
oci_execute($result);
oci_free_statement($result);
}
//==========================================================================
//==========================================================================
$search = $_GET['search'];
if (empty($search)) // $search 변수가 없을 때 $sql
{
$sql="select sc.sno, sname, sc.cno, cname, result, grade
from score sc, student, course, scgrade sg
where sc.sno = student.sno and sc.cno = course.cno and result between sg.loscore and sg.hiscore
order by sno";
}
else // $search 변수가 있을 때 $sql
{
$sql="select sc.sno, sname, sc.cno, cname, result, grade
from score sc, student, course, scgrade sg
where sc.sno = student.sno and sc.cno = course.cno and result between sg.loscore and sg.hiscore
and sname like '%{$search}%'
order by sno";
}
//==========================================================================
$result=oci_parse($conn,$sql);
oci_execute($result);
$row_num=oci_fetch_all($result, $row); // $row_num : 전제 행의 수
oci_free_statement($result);
oci_close($conn);
echo("Row의 개수는 $row_num 입니다.<br><hr>");
$scale=5;
$start = $_GET['start'];
if (empty($start)){ $start=0;}
echo("<table border='0'>");
echo("
<tr>
<td width='50'><p align='center'>학번</p></td>
<td width='70'><p align='center'>학생이름</p></td>
<td width='80'><p align='center'>과목번호</p></td>
<td width='50'><p align='center'>과목이름</p></td>
<td width='70'><p align='center'>시험점수</p></td>
<td width='50'><p align='center'>등급</p></td>
</tr>
");
for ($i=$start; $i<($start+$scale); $i++) {
if($i<$row_num){
echo("
<tr>
<td width='50'><p align='center'>{$row['SNO'][$i]}</p></td>
<td width='50'><p align='center'>{$row['SNAME'][$i]}</p></td>
<td width='80'><p align='center'>{$row['CNO'][$i]}</p></td>
<td width='100'><p align='center'>{$row['CNAME'][$i]}</p></td>
<td width='20'><p align='center'>{$row['RESULT'][$i]}</p></td>
<td width='20'><p align='center'>{$row['GRADE'][$i]}</p></td>
<td width='30'><a href=./sc_vi.php?del={$row['SNO'][$i]}&del2={$row['CNO'][$i]}>del</a></td>
</tr>
");
}
}
echo("</table>");
$p=$start-$scale;
$n=$start+$scale;
if($p>=0)
echo("<a href=./sc_vi.php?start=$p&search=$search>[이전페이지]</a> ");
else
echo("이전페이지 ");
if($n<$row_num)
echo("<a href=./sc_vi.php?start=$n&search=$search>[다음페이지]</a>");
else
echo("다음페이지");
?>
<form name='search' method='GET' action='sc_vi.php'>
이름검색 <input type='text' name='search'>
<input type='submit' name='확인' value='확인'>
</form>
<? echo("<br>"); show_source(__FILE__); ?>
'PHP' 카테고리의 다른 글
PHP - 배열 (0) | 2022.11.11 |
---|---|
PHP - 폼(form), 반복문 (0) | 2022.11.04 |
PHP - 조건 연산자(If, Else, Switch) (0) | 2022.10.24 |
PHP기초 - 변수 및 연산자 (0) | 2022.10.20 |
홈페이지 만들기 (0) | 2022.10.18 |