4 PHP Mysql 7 Section 7 1mysqlconnect php

4장 PHP와 Mysql 연동 7

Section 7 -1】mysql_connect. php 01 【예제 02 mysql_connect( ) 함수 resource mysql_connect(string hostname [: port], string username, string password); 1 : <? 2 : echo " <h 2> mysql_connect 함수 사용하기</h 2>"; 3 : $connect = mysql_connect("localhost", "php 5", "1234") 4 : or die(" 접속이 안되네요~~"); 5 : echo ("mysql 서버와 연결되었어요!!! "); 6 : echo $connect; 7 : 8 : mysql_close($connect); 9 : ? > 4

Section 7 -2】mysql_close. php 01 【예제 02 mysql_close( ) 함수 bool mysql_close(resource link_identifier); 1 : <? 2 : echo " <h 2> mysql_close함수 사용하기</h 2>"; 3 : $connect = mysql_connect("localhost", "php 5", "1234") 4 : or die(" 접속이 안되네요~~"); 5 : echo ("mysql 서버 접속 완료!!! "); 6 : 7 : mysql_close($connect); 8 : echo (" 접속 종료됨"); 9 : ? > 5

Section 7 -3】mysql_create_db. php 01 【예제 02 mysql_create_db( ) 함수 bool mysql_create_db(string database_name, resource link_identifier); 1 : <? 2 : $connect = mysql_connect("localhost", "root", "1234"); 3 : $dbname = mysql_create_db("sample", $connect); 5 : if (!$dbname) 6 : { echo(" 새로운 데이터베이스 생성에 실패했습니다 !!! "); 8 : exit; } 10 : else 11 : echo(" sample 데이터베이스가 생성되었습니다!!!"); 15 : mysql_close($connect); 16 : ? > 6

Section 7 -4】mysql_drop_db. php 01 【예제 02 mysql_drop_db() 함수 bool mysql_drop_db(string database_name , resouce link_identifier); 1 : <? 2 : $connect = mysql_connect("localhost", "root", "1234"); 3 : $dropdb = mysql_drop_db("sample", $connect); 5 : if (!$dropdb) 6 : { echo("데이터베이스삭제에 실패했습니다!!! "); 8 : exit; } 10 : else 12 : echo("sample 데이터베이스가 삭제되었습니다"); 15 : mysql_close($connect); 16 : ? > 7

Section 7 -5】mysql_select_db. php 01 【예제 02 mysql_select_db( ) 함수 bool mysql_select_db(string database_name, resource link_identifier); 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : $db_con = mysql_select_db("php 5_db", $connect); 5 : if (!$db_con) 6 : { echo("php 5_db 데이터베이스를 선택하는데 실패!!! "); 8 : exit; } 10 : else 12 : echo("php 5_db 데이터베이스가 성공적으로 선택되었습니다 !!!"); 15 : mysql_close($connect); 16 : ? > 8

Section 7 -6】mysql_query. php 01 【예제 02 mysql_query( ) 함수 resouce mysql_query(string query, resource link_identifier); 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : mysql_select_db("php 5_db", $connect); 6 : $sql = "create table client ("; 7 : $sql = $sql. "name CHAR(12) NOT NULL, "; 8 : $sql = $sql. "id CHAR(12) NOT NULL, "; 9 : $sql = $sql. "email VARCHAR(40), "; 10 : $sql = $sql. "sex VARCHAR(4), "; 11 : $sql = $sql. "point INT, "; 12 : $sql = $sql. "grade VARCHAR(2), "; 13 : $sql = $sql. "PRIMARY KEY(id) )"; 15 : $result = mysql_query($sql, $connect); 9

Section 7 -6】mysql_query. php 01 【예제 02 17 18 19 21 25 26 27 29 30 31 32 33 35 39 40 41 : if (!$result) { : echo("client 테이블 생성 실패!! "); : exit; } : else echo("client 테이블 생성 성공!! "); : : $sql = "insert into client values"; : $sql = $sql. "('이경희', 'khlee@hanmail. net', '여', 30, 90)"; : $result = mysql_query($sql, $connect); : : if (!$result) { : echo(" 레코드 삽입 실패!!"); : exit; } : else echo(" 레코드 삽입 성공!!"); : : mysql_close(); : ? > 10

Section 7 -7】mysql_num_rows. php 01 【예제 02 mysql_num_rows( ) 함수 int mysql_num_rows(resource result); 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : mysql_select_db("php 5_db", $connect); 5 : $sql = "select * from mem"; 6 : $result = mysql_query($sql, $connect); 8 : $total_records = mysql_num_rows($result); 9 : 10 : echo("mysql_query() 함수의 반환 값 : $result "); 11 : echo("mem 테이블의 전체 레코드 수 : $total_records"); 13 : mysql_close(); 14 : ? > 11

Section 7 -8】mysql_num_fields. php 01 【예제 02 mysql_num_fields( ) 함수 int mysql_num_fields(resource result); 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : mysql_select_db("php 5_db", $connect); 5 : $sql = "select * from mem"; 6 : $result = mysql_query($sql, $connect); 8 : $total_fields = mysql_num_fields($result); 9 : 10 : echo("mysql_query() 함수의 반환 값 : $result "); 11 : echo("mem 테이블의 전체 필드 수 : $total_fields"); 13 : mysql_close(); 14 : ? > 12

Section 7 -9】mysql_fetch_row. php 01 【예제 02 mysql_fetch_row( ) 함수 array mysql_fetch_row(resource result); 1 2 3 4 5 6 7 8 9 : <? : : : : ? > $connect = mysql_connect("localhost", "php 5", "1234"); $db_con = mysql_select_db("php 5_db", $connect); $sql = "select * from mem"; $result = mysql_query($sql, $connect); $fields = mysql_num_fields($result); 13

Section 7 -9】mysql_fetch_row. php 01 【예제 02 10 11 12 13 14 15 16 17 18 19 20 21 : : : <h 2> mysql_fetch_row() 함수 예제</h 2> <table width= 800 border=1 cellpadding=10> <tr> <td bgcolor=lightblue align=center>번호</td> <td bgcolor=lightblue align=center>아이디</td> <td bgcolor=lightblue align=center>이름</td> <td bgcolor=lightblue align=center>성별</td> <td bgcolor=lightblue align=center>우편번호</td> <td bgcolor=lightblue align=center>주소</td> <td bgcolor=lightblue align=center>전화번호</td> <td bgcolor=lightblue align=center>나이</td> </tr> 14

Section 7 -9】mysql_fetch_row. php 01 【예제 02 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 : <? : while ( $row = mysql_fetch_row($result)) : { : echo("<tr>"); : : for ($i=0; $i < $fields; $i++) : { : echo("<td> $row[$i] </td>"); : } : : echo("</tr>"); : } : mysql_close(); : ? > : </table> 15

Section 7 -10】mysql_fetch_array. php 01 【예제 mysql_fetch_array( ) 함수 array mysql_fetch_array(resource result); 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : $db_con = mysql_select_db("php 5_db", $connect); 4 : 5 : $sql = "select * from mem"; 6 : $result = mysql_query($sql, $connect); 7 : 8 : $fields = mysql_num_fields($result); 9 : ? > 10 : <h 2> mysql_fetch_array() 함수 예제</h 2> 11 : <table width= 800 border=1 cellpadding=10> 16

Section 7 -10】mysql_fetch_array. php 01 【예제 11 12 13 14 15 16 17 18 19 20 21 : : : <table width= 800 border=1 cellpadding=10> <tr> <td bgcolor=lightblue align=center>번호</td> <td bgcolor=lightblue align=center>아이디</td> <td bgcolor=lightblue align=center>이름</td> <td bgcolor=lightblue align=center>성별</td> <td bgcolor=lightblue align=center>우편번호</td> <td bgcolor=lightblue align=center>주소</td> <td bgcolor=lightblue align=center>전화번호</td> <td bgcolor=lightblue align=center>나이</td> </tr> 17

Section 7 -10】mysql_fetch_array. php 01 【예제 22 23 24 25 27 28 29 30 31 32 33 34 36 37 38 39 40 : <? : while ( $row = mysql_fetch_array($result)) : { : echo("<tr>"); : echo("<td> $row[num] </td>"); : echo("<td> $row[id] </td>"); : echo("<td> $row[name] </td>"); : echo("<td> $row[sex] </td>"); : echo("<td> $row[post_num] </td>"); : echo("<td> $row[address] </td>"); : echo("<td> $row[tel] </td>"); : echo("<td> $row[age] </td>"); : echo("</tr>"); : } : mysql_close(); : ? > : </table> 18

Section 7 -11】mysql_result. php 01 【예제 02 mysql_result( ) 함수 mixed mysql_result(resource result, int row, mixed field); 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : $db_con = mysql_select_db("php 5_db", $connect); 4 : 5 : $sql = "select * from mem"; 6 : $result = mysql_query($sql, $connect); 7 : 8 : $records = mysql_num_rows($result); 9 : 10 : $fields = mysql_num_fields($result); 11 : ? > 19

Section 7 -11】mysql_result. php 01 【예제 02 12 13 14 15 16 17 18 19 20 21 22 23 : : : <h 2> mysql_result() 함수 예제</h 2> <table width= 800 border=1 cellpadding=10> <tr> <td bgcolor=lightblue align=center>번호</td> <td bgcolor=lightblue align=center>아이디</td> <td bgcolor=lightblue align=center>이름</td> <td bgcolor=lightblue align=center>성별</td> <td bgcolor=lightblue align=center>우편번호</td> <td bgcolor=lightblue align=center>주소</td> <td bgcolor=lightblue align=center>전화번호</td> <td bgcolor=lightblue align=center>나이</td> </tr> 20

Section 7 -11】mysql_result. php 01 【예제 02 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 : <? : for ($i = 0; $i < $records; $i++) : { : echo("<tr>"); : : for ($j = 0; $j < $fields; $j++) : { : $data = mysql_result($result, $i, $j); : echo("<td> $data </td>"); : } : : echo("</tr>"); : } : mysql_close(); : ? > : </table> 21

Section 7 -12】mem_form. php 01 【예제 02 회원가입 폼 양식 예 1 : <html> 2 : <body> 3 : <form name=mem_form method=post action="mem_print. php? var 1=10&var 2=join"> 4 : <table border="1" width=682 cellspacing="1" cellpadding="4"> 5 : <tr><td align=right>* 아이디 : </td> 7 : <td><input type=text size=15 maxlength=12 name=id></tr> 8 : <tr> <td align=right > * 이름 : </td> 10 : <td><input type=text size=15 maxlength=12 name=name> </td></tr> 11 : <tr> 12 : <td align=right> * 비밀번호 : </td> 13 : <td><input type=password size=10 maxlength=10 14 : name=passwd></tr> 22

Section 7 -12】mem_form. php 01 【예제 02 16 17 18 19 21 22 23 25 26 27 28 29 33 34 35 : <td align=right> * 비밀번호 확인 : </td> : <td><input type=password size=12 maxlength=12 : name=passwd_confirm></td></tr> : <tr> <td align=right>성별 : </td> : <td><input type=radio name=sex value='남성' checked>남 : <input type=radio name=sex value='여성'>여</td></tr> : <tr> <td align=right>휴대전화 : </td> : <td><select name=phone 1> : <option value=''>선택</option> : <option value='010'>010</option> : <option value='011'>011</option> : <option value='016'>016</option> : </select> : <input type=text size=4 name=phone 2 maxlength=4> : <input type=text size=4 name=phone 3 maxlength=4></td> </tr> 23

Section 7 -12】mem_form. php 01 【예제 02 36 37 38 39 40 41 42 : <tr> : <td align=right>주 소 : </td> : <td><input type=text size=50 name=address></td></tr> : <tr> : <td align=right>취 미 : </td> : <td> : <input type=checkbox name=movie value="yes" checked> 영화감상   43 : <input type=checkbox name=book value="yes" >독 서   44 : <input type=checkbox name=shop value="yes" >쇼 핑   45 : <input type=checkbox name=sport value="yes" checked>운 동 46 : </td></tr> 47 : <tr> 48 : <td align=right>자기소개 : </td> 49 : <td><textarea name=intro rows=5 cols=40></textarea></td> </tr> 24

Section 7 -12】mem_form. php 01 【예제 02 51 : 52 : <table border=0 width=682> 53 : <tr><td align=center> 54 : <input type=submit value="확인"> 55 : <input type=reset value="다시작성"></td></tr> 56 : </table> 57 : <input type=hidden name=title value="회원 가입 양식"> 58 : </form> 59 : </body> 60 : </html> 25

Section 7 -13】mem_print. php 01 【예제 02 15 : echo " 주소 : "; echo $address. " "; 17 : echo " 영화감상 : "; echo $movie. " "; 19 : echo " 독서 : "; echo $book. " "; 21 : echo " 쇼핑 : "; echo $shop. " "; 23 : echo " 운동 : "; echo $sport. " "; 25 : echo " 자기소개 : "; 26 : echo $intro. " "; 27 : echo " 제목(hidden 타입에서 전달) : "; 28 : echo $title. " "; 30 : echo " 2) Get 방식으로 전달". " "; 31 : echo " $var 1 : "; 32 : echo $var 1. " "; 33 : echo " $var 2 : "; 34 : echo $var 2. " "; 35 : ? > 27

Section 7 -14】student_table. php 01 【예제 02 DB 테이블 생성 프로그램 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : $dbconn = mysql_select_db("php 5_db", $connect); 5 : $sql = "create table student ("; 6 : $sql. = "num int not null auto_increment, "; 7 : $sql. = "name varchar(12), "; 8 : $sql. = "kor int, "; 9 : $sql. = "eng int, "; 10 : $sql. = "math int, "; 11 : $sql. = "sum int, "; 12 : $sql. = "avg float, "; 13 : $sql. = "primary key(num) )"; 14 : 15 : $result = mysql_query($sql, $connect); 29

Section 7 -14】student_table. php 01 【예제 02 17 18 19 20 21 22 23 : if (!$result) : echo "student 테이블 생성 실패 !!!"; : else : echo "student 테이블 생성 성공 !!!"; : : mysql_close(); : ? > 30

Section 7 -15】student_list. php 01 【예제 02 성적처리 메인 프로그램 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); // DB 연결 3 : mysql_select_db("php 5_db", $connect); // DB 선 택 4 : 5 : if ($mode == "insert") // 데이터 입력하기 6 : { $sum = $kor + $eng + $math; // 합계 구하기 8 : $avg = $sum/3; // 평균 구하기 9 : 10 : $sql = "insert into student (name, kor, eng, math, sum, avg) values"; 11 : $sql. = "('$name', $kor, $eng, $math, $sum, $avg)"; 12 : 13 : $result = mysql_query($sql, $connect); 14 : } 15 : ? > 31

Section 7 -15】student_list. php 01 【예제 02 17 18 20 21 : : 22 23 24 25 26 27 28 29 30 31 32 : : : <h 2>성적처리 프로그램 예제</h 2> 학생 성적 입력하기 <!-- 성적 입력/성적순 정렬/성적역순 정렬 --> <table width=800 border=0 cellspaceing=1 cellpadding=5 bgcolor=#cccccc> <tr> <form action=<? echo $PHP_SELF ? > method='post'> <td bgcolor=white> 이름 : <input type=text size=8 name=name> 국어 : <input type=text size=5 name=kor> 영어 : <input type=text size=5 name=eng> 수학 : <input type=text size=5 name=math>   <input type=submit value="입력"> <input type=hidden name=mode value='insert'> </td> </form> 32

Section 7 -15】student_list. php 01 【예제 02 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 50 : : : : <form action=<? echo $PHP_SELF ? > method='post'> <td bgcolor=white> <input type=submit value="성적순 정렬"> <input type=hidden name=mode value='big_first'> </td> </form> <form action=<? echo $PHP_SELF ? > method='post'> <td bgcolor=white> <input type=submit value="성적역순 정렬"> <input type=hidden name=mode value='small_first'> </td> </form> </tr> </table> 33

Section 7 -15】student_list. php 01 【예제 02 52 53 55 56 57 58 59 60 61 65 67 68 69 70 71 72 : <table width= 800 border=0 cellpadding=5 bgcolor=#cccccc > : <tr> <td bgcolor=#eeeeee align=center>번호</td> : <td bgcolor=#eeeeee align=center>이름</td> : <td bgcolor=#eeeeee align=center>국어</td> : <td bgcolor=#eeeeee align=center>영어</td> : <td bgcolor=#eeeeee align=center>수학</td> : <td bgcolor=#eeeeee align=center>합계</td> : <td bgcolor=#eeeeee align=center>평균</td> : <td bgcolor=#eeeeee align=center></td> </tr> : <? : if ($mode == "big_first") // 성적순 정렬(내림차순) : $sql = "select * from student order by sum desc"; : else if ($mode == "small_first") // 성적순 정렬(오름차순) : $sql = "select * from student order by sum"; : else : $sql = "select * from student"; 34

Section 7 -15】student_list. php 01 【예제 02 74 : $result = mysql_query($sql); 76 : $count = 1; // 화면 출력 시 일렬번호 79 : while ($row = mysql_fetch_array($result)) 80 : { $avg = round($row[avg], 1); 84 : echo("<td bgcolor=white align=center> $count </td>"); 85 : echo("<td bgcolor=white align=center> $row[name] </td>"); 86 : echo("<td bgcolor=white align=center> $row[kor] </td>"); 87 : echo("<td bgcolor=white align=center> $row[eng] </td>"); 88 : echo("<td bgcolor=white align=center> $row[math] </td>"); 89 : echo("<td bgcolor=white align=center> $row[sum] </td>"); 90 : echo("<td bgcolor=white align=center> $avg </td>"); 91 : echo("<td bgcolor=white align=center> 92 : <a href=student_delete. php? num=". $row[num]. ">". 93 : "삭제</a></td>"); 96 : $count++; 97 : } 100 : mysql_close(); ? > </table> 35

Section 7 -16】student_delete. php 01 【예제 02 레코드 삭제 1 : <? 2 : $connect = mysql_connect("localhost", "php 5", "1234"); 3 : mysql_select_db("php 5_db", $connect); 4 : 5 : // DB 테이블에서 num 필드의 값으로 $num 의 값을 가지는 6 : // 레코드 삭제 7 : $sql = "delete from student where num = $num"; 8 : mysql_query($sql, $connect); 9 : 10 : mysql_close($connect); 11 : 12 : // 레코드를 삭제하고 다시 student_list. php 로 돌아감 13 : Header("Location: student_list. php"); 14 : ? > 15 : 36