관계형 데이터베이스 실습

개요

이번에는 관계형 데이터 베이스와 웹을 연동하여 사용하여 봅니다.

실습

처음에는 데이터베이스 값을 새로 입력하여줍니다.

CREATE TABLE `topic2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `author` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `topic2` VALUES ('1', 'About JavaScript', '<h3>Desctiption</h3>\r\n<p>JavaScript  is a dynamic computer programming language. It is most commonly used as part of web browsers, whose implementations allow client-side scripts to interact with the user, control the browser, communicate asynchronously, and alter the document content that is displayed.</p>\r\n<p>\r\nDespite some naming, syntactic, and standard library similarities, JavaScript and Java are otherwise unrelated and have very different semantics. The syntax of JavaScript is actually derived from C, while the semantics and design are influenced by the Self and Scheme programming languages.\r\n</p>\r\n<h3>See Also</h3>\r\n<ul>\r\n  <li><a href=\"http://en.wikipedia.org/wiki/Dynamic_HTML\">Dynamic HTML and Ajax (programming)</a></li>\r\n  <li><a href=\"http://en.wikipedia.org/wiki/Web_interoperability\">Web interoperability</a></li>\r\n  <li><a href=\"http://en.wikipedia.org/wiki/Web_accessibility\">Web accessibility</a></li>\r\n</ul>\r\n', '1', '2015-03-31 12:14:00');
INSERT INTO `topic2` VALUES ('2', 'Variable and Constant', '<h3>Desciption</h3>\r\n\r\nIn computer programming, a variable or scalar is a storage location paired with an associated symbolic name (an identifier), which contains some known or unknown quantity or information referred to as a value. The variable name is the usual way to reference the stored value; this separation of name and content allows the name to be used independently of the exact information it represents. The identifier in computer source code can be bound to a value during run time, and the value of the variable may thus change during the course of program execution.\r\n\r\n<h3>See Also</h3>\r\n<ul>\r\n<li>Non-local variable</li>\r\n<li>Variable interpolation</li>\r\n</ul>\r\n', '3', '2015-05-14 10:04:00');
INSERT INTO `topic2` VALUES ('3', 'Opeartor', '<h2>Operator</h2>\r\n<h3>Description</h3>\r\n<p>Programming languages typically support a set of operators: constructs which behave generally like functions, but which differ syntactically or semantically from usual functions</p>\r\n<p>Common simple examples include arithmetic (addition with +, comparison with >) and logical operations (such as AND or &&). </p>\r\n', '1', '2015-06-18 05:00:00');
INSERT INTO `topic2` VALUES ('4', 'Conditional', '<h3>Description</h3>\r\n<p>In computer science, conditional statements, conditional expressions and conditional constructs are features of a programming language which perform different computations or actions depending on whether a programmer-specified boolean condition evaluates to true or false. Apart from the case of branch predication, this is always achieved by selectively altering the control flow based on some condition.</p>\r\n<p>In imperative programming languages, the term \"conditional statement\" is usually used, whereas in functional programming, the terms \"conditional expression\" or \"conditional construct\" are preferred, because these terms all have distinct meanings.</p>\r\n<h3>See Also</h3>\r\n<ul>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Branch_(computer_science)\" title=\"Branch (computer science)\">Branch (computer science)</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Conditional_compilation\" title=\"Conditional compilation\">Conditional compilation</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Dynamic_dispatch\" title=\"Dynamic dispatch\">Dynamic dispatch</a> for another way to make execution choices</li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/McCarthy_Formalism\" title=\"McCarthy Formalism\">McCarthy Formalism</a> for history and historical references</li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Named_condition\" title=\"Named condition\" class=\"mw-redirect\">Named condition</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Test_(Unix)\" title=\"Test (Unix)\">Test (Unix)</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Yoda_conditions\" title=\"Yoda conditions\">Yoda conditions</a></li>\r\n</ul>', '2', '2015-07-25 00:00:00');
INSERT INTO `topic2` VALUES ('5', 'Function', 'A function model or functional model in systems engineering and software engineering is a structured representation of the functions (activities, actions, processes, operations) within the modeled system or subject area.', '2', '0000-00-00 00:00:00');
INSERT INTO `topic2` VALUES ('6', 'Object', 'In computer science, an object is a location in memory having a value and possibly referenced by an identifier. An object can be a variable, a data structure, or a function. In the class-based object-oriented programming paradigm, \"object\" refers to a particular instance of a class where the object can be a combination of variables, functions, and data structures. In relational database management, an object can be a table or column, or an association between data and a database entity (such as relating a person\'s age to a specific person)', '3', '0000-00-00 00:00:00');
 

INSERT INTO `user` VALUES ('1', 'egoing', '111111');
INSERT INTO `user` VALUES ('2', 'jin', '222222');
INSERT INTO `user` VALUES ('3', 'k8805', '333333');
INSERT INTO `user` VALUES ('4', 'sorialgi', '444444');
INSERT INTO `user` VALUES ('5', 'lily', '555555');
INSERT INTO `user` VALUES ('6', 'happydeveloper', '666666');

 

위의 sql을 입력하여 데이터베이스 안에 값들을 넣어줍니다

다음으로 전에 만든 index.php를 가져옵니다.

<?php
	$conn = mysqli_connect("localhost", "root", "jh1245");
	mysqli_select_db($conn, "mytester");
	$result = mysqli_query($conn, "SELECT * FROM topic2");
?>
<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<link rel="stylesheet" type="text/css" href="http://localhost/phpjs/style.css">
	</head>
	<body id="target">
	<header>
		<img src="https://s3.ap-northeast-2.amazonaws.com/opentutorials-user-file/course/94.png" alt="생활코딩">
		<h1><a href="http://localhost/index.php">JavaScript</a></h1>
	</header>
	<nav>
		<ol>
			<?php
				 while( $row = mysqli_fetch_assoc($result)){
					echo '<li><a href="http://localhost/index.php?id='.$row['id'].'">'.$row['title'].'</a></li>'."\n";
				}
			?>
		</ ol>
	</nav>
	<div id="control">
		<input type="button" value="white" onclick="document.getElementById('target').className='white'"/>
		<input type="button" value="black" onclick="document.getElementById('target').className='black'" />
		<a href="http://localhost/write.php">쓰기</a>
	</div>
	<article>
		<?php
			if(empty($_GET['id']) === false ) {
				$sql = "SELECT topic2.id,title,name,description FROM topic2 LEFT JOIN user ON topic2.author = user.id WHERE topic2.id=".$_GET['id'];
				$result = mysqli_query($conn, $sql);
				$row = mysqli_fetch_assoc($result);
				echo '<h2>'.$row['title'].'</h2>';
				echo '<p>'.$row['name'].'</p>';
				echo $row['description'];
			}
		?>
	</article>
	</body>
</html>

전과 달라진 점이라면 조인문을 사용하여 두 개의 테이블을 연결한 것을 추가하였습니다.

여기서 중요한 점은 원래는 author을 topic에서 가져오지만 이번에는 user에서 값을 가져오기 때문에 name으로 이름을 바꾸어줍니다.

위의 사진처럼 데이터베이스의 값이 잘 들어간 것을 볼 수 있습니다.

다음은 쓰기 버튼을 누르고 값을 입력 시 되는지 데이터베이스에 잘 들어가는지 확인하여 보겠습니다.

전엔 바꾼 데이터베이스를 연동하기 위해 코드를 다시 작성하겠습니다.

<?php
	$conn = mysqli_connect("localhost", "root", "jh1245");
	mysqli_select_db($conn, "mytester");
	$sql = "SELECT * FROM user WHERE name='".$_POST['author']."'";
	$result = mysqli_query($conn, $sql);
	if($result->num_rows == 0){
  		$sql = "INSERT INTO user (name, password) VALUES('".$_POST['author']."', '111111')";
  		mysqli_query($conn, $sql);
 		$user_id = mysqli_insert_id($conn);
	} else {
		$row = mysqli_fetch_assoc($result);
		$user_id = $row['id'];
	}
	$sql = "INSERT INTO topic2 (title,description,author,created) VALUES('".$_POST['title']."', '".$_POST['description']."', '".$user_id."', now())";
	$result = mysqli_query($conn, $sql);
	header('Location: http://localhost/index.php');
?>

위의 쿼리를 보면 user안에 데이터를 넣기 위한 쿼리입니다 이렇게 입력을 한 후 입력하면 데이터베이스 안에

값이 들어가 있는 것을 확인할 수 있습니다.

 

참고한 수업 링크

https://opentutorials.org/course/1688/9428