PHP 에서 MySQL 사용하기 (연결, DB&테이블 생성, 데이터 삽입/선택)

1. 데이터베이스에 접근하기 위해 MySQL 서버에 연결

PHP 에서는 mysqli() 또는 mysqli_connect() 함수를 이용한다.

mysqli() 함수는 객체지향 프로그래밍 스타일을 위한 것이고,
mysqli_connect() 함수는 절차지향 프로그래밍 스타일을 위한 것이라고 한다.

둘의 목적에 큰 차이는 없다고 한다. 출처 : 스택오버플로우

<?php
$host = 'localhost';
$user = 'root';
$pw = 'root-password';
$dbname = 'test_db';

// Create connection
$conn = new mysqli($host, $user, $pw, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

 

2. MySQLi 를 이용해서 MySQL 에 Database 를 생성(Create)

database 를 생성할때는 MySQL 에 연결할때, 파라미터에 host, user, pw 만 넣어주어야 한다.

<?php
$host = 'localhost';
$user = 'root';
$pw = 'root-password';

// Create connection
$conn = new mysqli($host, $user, $pw);

// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}
?>

CREATE DATABASE 문은 MySQL 에서 데이터베이스를 생성할 때 사용한다.

명령을 실행하기 위해서는 query() 함수에 CREATE DATABASE 문을 추가해야한다.

위의 예제는 myDB 라는 이름의 데이터베이스를 생성한다.

 

3. MySQLi 를 이용해서 데이터베이스에 Table 을 생성(Create)

<?php
$host = 'localhost';
$user = 'root';
$pw = 'root-password';
$dbname = 'test_db';

// Create connection
$conn = new mysqli($host, $user, $pw, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

CREATE TABLE 문은 MySQL 에서 테이블을 생성할 떄 사용한다.

명령을 실행하기 위해서는 query() 함수에 CREATE TABLE 문을 추가해야한다.

위의 예제는 MyGuests 라는 이름의 테이블을 생성하고,
테이블에 id, firstname, lastname, email, reg_date 라는 5개의 column 들을 추가했다.

< Data Type >
TABLE 에 column 을 추가할때, column 이 보유할 수 있는 데이터의 타입을 명시해야 한다.
대표적으로 많이 쓰이는 타입들만 정리했다. https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Text types

Data type	Description
CHAR(size)	고정 길이 문자열을 보유한다. (문자, 숫자 및 특수 문자를 포함 할 수 있음). 고정 크기는 괄호 안에 지정되며, 최대 255자를 저장할 수 있다.
VARCHAR(size)	가변 길이 문자열을 보유한다 (문자, 숫자 및 특수 문자를 포함 할 수 있음). 최대 크기는 괄호 안에 지정되어 있으며, 최대 255자를 저장할 수 있다. 참고 : 255보다 큰 값을 입력하면 TEXT 유형으로 변환된다.
TEXT	최대 길이가 65,535자인 문자열을 보유한다.
BLOB	BLOB (Binary Large OBjects)를 보유한다. 최대 65,535 바이트의 데이터를 저장할 수 있다.

 

Number types

Data type	Description
INT(size)	-2147483648 에서 2147483647 사이의 숫자를 보유할 수 있다. 최대 자릿수는 괄호 안에 지정한다.
FLOAT(size,d)	부동 소수점이있는 작은 숫자이다. 최대 자릿수는 size 매개 변수에 지정 될 수 있다. 소수점 오른쪽의 최대 자릿수는 d 매개 변수에 지정된다.

 

Data types

Data type	Description
DATE()	날짜. Format: YYYY-MM-DD
DATETIME()	날짜와 시간 조합, Format: YYYY-MM-DD HH:MM:SS

 

< Attributes >
데이터 타입의 명시 뒤에는 column 에 대한 속성(attribute)을 설정할 수 있다.

• NOT NULL - 해당 column 에 대한 row 는 null 값을 가질 수 없다.
• DEFAULT value - 해당 column 에 대한 row 에 값이 전달되지 않을 때 추가되는 기본값이다.
• UNSIGNED - 숫자형 자료 타입에 사용되며, 저장된 데이터를 양수와 0 으로 제한한다.
• AUTO INCREMENT - 새 레코드가 추가될때마다 필드 값을 1씩 자동으로 증가시킨다.
• PRIMARY KEY - 테이블에서 행을 고유하게 식별하는데 사용된다. PRIMARY KEY 설정이 있는 열은 대부분 ID 번호이며, AUTO INCREMENT 와 함께 사용되는 경우가 있다.

 

<여기서 테이블이란?>

관계형 데이터베이스에서 테이블이란, column 과 row 로 이루어진 데이터 요소들의 집합이다.
column 은 열(기둥) 이라는 뜻으로, 세로로 이루어져 특정 필드를 나타낸다.
row 는 행이라는 뜻으로, 가로로 이루어져 가각의 레코드를 나타낸다.
행과 열의 교차는 테이블의 각 셀을 정의 한다.
첫번째 열에는 ID 와 같은 고유 식별자(ID)가 포함되는 경우가 많다.
각 레코드(행) 들을 고유 식별자로 식별할 수 있으며, 다른 테이블에서 해당 레코드의 고유 식별자 값을 참조하여 사용할 수가 있는데, 이 경우를 두고 테이블끼리 관계가 있다고 말한다(관계형 데이터베이스).

테이블에 대한 참고 링크1 : https://www.techopedia.com/definition/1247/table

테이블에 대한 참고 링크2 : https://www.ntchosting.com/encyclopedia/databases/mysql/database-table/

테이블에 대한 참고 링크3 : https://database.guide/what-is-a-table/

 

4. MySQLi 를 이용해서 Data 를 삽입(Insert)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

INSERT INTO 문은 MySQL 에서 테이블에 새로운 레코드를 삽입할 때 사용한다.

명령을 실행하기 위해서는 query() 함수에 INSERT INTO 문을 추가해야한다.

위의 예제는 MyGuests 라는 이름의 테이블에
firstname, lastname, email 이라는 column 에 각각 John, Doe, john@example.com 이라는 값을 추가한다.

* 데이터가 위치하는 열의 이름을 지정하지 않고 그 값만 지정할 수도 있다.

한번에 여러개의 값을 삽입할 수도 있는데, 이때는 세미콜론으로 각 SQL 구문을 구분해주어야 한다.

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";

if ($conn->multi_query($sql) === TRUE) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

 

5. MySQLi 를 이용해서 Data 를 선택(Select)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

SELECT 문은 MySQL 에서 데이터를 선택할 때 사용한다.

명령을 실행하기 위해서는 query() 함수에 SELECT 문을 추가해야한다.
FROM 은 어떤 테이블에서 select  할것인지를 명시한다.

위의 예제는 MyGuests 라는 이름의 테이블에서
id, firstname, lastname 이라는 column 들의 데이터를 선택한다.

선택한 데이터의 num_rows 가 0 이상일때, fetch_assoc() 함수를 이용하여, 필드 이름으로 된 배열을 저장한다.
Fetch 함수 참고 : https://delirussum.tistory.com/118

위의 예제의 실행결과는 다음과 같다.

id: 1 - Name: John Doe
id: 2 - Name: Mary Moe
id: 3 - Name: Julie Dooley

 

참고 사이트>>>

https://www.w3schools.com/php/php_mysql_connect.asp

http://jun.hansung.ac.kr/SWP/PHP/PHP%20Connect%20to%20the%20MySQL%20Server.html