php) mysql 연동 insert, select, update, delete (CRUD)

mysql 연결

<?php
// mysql 연결
$host = 'localhost:3306';
$user = 'root';
$pw = '<password>';
$dbName = '<db 이름>';
$conn = new mysqli($host, $user, $pw, $dbName);

if($conn){
    echo "MySQL 접속 성공";
}else{
    echo "MySQL 접속 실패";
}
mysqli_close($conn);
?>

insert

<?php
// mysql 연결
$host = "localhost";
$user = "root";
$pw = "<password>";
$dbName = "<db 이름>";
$conn = new mysqli($host, $user, $pw, $dbName);

/* DB 연결 확인 */
if($conn){ echo "Connection established"."<br>"; }
else{ die( 'Could not connect: ' . mysqli_error($conn) ); }

/* INSERT 예제 */
$sql = "INSERT INTO user(uid, pwd) VALUES('neo','1')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
<?php
/* INSERT 예제 2*/
$conn = mysqli_connect( 'localhost', 'root', '<password>', '<db 이름>');
$sql = "INSERT INTO user(uid, pwd) VALUES('neo','1')";
$result = mysqli_query($conn, $sql);
if($result === false){
    echo '저장하는 과정에서 문제가 생겼습니다. 관리자에게 문의해주세요';
    error_log(mysqli_error($conn));
} else {
    echo '성공했습니다.';
}
?>
<?php
// mysql 연결
$host = 'localhost:3306';
$user = 'root';
$pw = '<password>';
$dbName = '<db 이름>';
$conn = new mysqli($host, $user, $pw, $dbName);

if($conn){
    echo "MySQL 접속 성공";
}else{
    echo "MySQL 접속 실패";
}
echo "</br>";

/* SELECT 예제 1 */
$sql = "SELECT * FROM user";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){
    echo $row['uid']." ".$row['pwd']."<br>";
}

/* SELECT 예제 2*/
$sql = "SELECT * FROM user WHERE uid='yona' ";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){
    echo $row['uid']." ".$row['pwd']."<br>";
}

$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
foreach($row as $key => $r){
    echo "{$key} : {$r}   |   ";
}
mysqli_close($conn);
// SELECT 예제 3
try
{
    $connect = new PDO("mysql:host=$host;dbname=$dbName", $user, $pw);
    $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM user WHERE uid='yona'";
    foreach ($connect->query($sql) as $field) {
        echo $field['uid']."의 비밀번호 : ";
        echo $field['pwd']."<br>";
    }
}
catch(PDOException $ex){
    echo "레코드 선택 실패! : ".$ex->getMessage()."<br>";
}
$connect = null;
?>

select

<?php
// mysql 연결
$host = 'localhost:3306';
$user = 'root';
$pw = '<password>';
$dbName = '<db 이름>';
$conn = new mysqli($host, $user, $pw, $dbName);

if($conn){
    echo "MySQL 접속 성공";
}else{
    echo "MySQL 접속 실패";
}
echo "</br>";

/* SELECT 예제 1 */
$sql = "SELECT * FROM user";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){
    echo $row['uid']." ".$row['pwd']."<br>";
}

/* SELECT 예제 2*/
$sql = "SELECT * FROM user WHERE uid='yona' ";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){
    echo $row['uid']." ".$row['pwd']."<br>";
}

$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
foreach($row as $key => $r){
    echo "{$key} : {$r}   |   ";
}
mysqli_close($conn);
// SELECT 예제 3
try
{
    $connect = new PDO("mysql:host=$host;dbname=$dbName", $user, $pw);
    $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM user WHERE uid='yona'";
    foreach ($connect->query($sql) as $field) {
        echo $field['uid']."의 비밀번호 : ";
        echo $field['pwd']."<br>";
    }
}
catch(PDOException $ex){
    echo "레코드 선택 실패! : ".$ex->getMessage()."<br>";
}
$connect = null;
?>

 

update

<?php
$con=mysqli_connect("localhost","root","<password>","<db 이름>");
// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
} else{
mysqli_query($con,"UPDATE user SET birth=19990515
WHERE uid='neo' AND pwd='1'");
echo "업데이트 성공";
}
mysqli_close($con);
?>

delete

<?php
$servername = "localhost";
$user = "root";
$password = "<password>";
$dbname = "<db 이름>";
$connect = mysqli_connect($servername, $user, $password, $dbname);
if (!$connect) { die("서버와의 연결 실패! : ".mysqli_connect_error()); }
$sql = "DELETE FROM user WHERE uid = 'neo'";
if (mysqli_query($connect, $sql)) {
    echo "레코드 삭제 성공!";
} else {
        echo "레코드 삭제 실패! : ".mysqli_error($connect);
}
mysqli_close($connect);
?>