[php,mysql,maria db] insert update - on duplicate key update 오라클 merge into 실제 예제

저 값을 입력 받는
sc_setting.html

<form name="form1" action="sc_setting_ok.php">
<input type="submit" value="저장">
<br> <br>
<? include_once 'include.php';
$sql = "SELECT id, name, godo_srl, url, price FROM sc_setting";
$result = mysqli_query($conn, $sql); for($i=0;$i<10;$i++){
$row = mysqli_fetch_assoc($result);
?>
<input type="text" name="a<?=$i?>" value="<?=$row['name']?>">
<input type="text" name="b<?=$i?>" value="<?=$row['godo_srl']?>">
<input type="text" name="c<?=$i?>" value="<?=$row['url']?>">
<input type="text" name="d<?=$i?>" value="<?=$row['price']?>">
<input type="button" onclick="location.href='sc_parsing.html?id=<?=$i?>'"> <br> <br> <? } ?> </form>




그 다음 값을 저장 하는 sc_setting_ok.php

on duplicate key update 를 사용해 값이 있으면 업데이트 없으면 insert를 한다.

<? header("Content-Type; text/html; charset=UTF-8");
include_once 'include.php';

 if($conn->connect_error){ die("Connection failed: ".$conn->connect_error);
}else{
print_r($conn);
 }
 $result = $conn->query("SHOW TABLES LIKE 'sc_setting'");
$exist = ( $result->num_rows > 0 ); if($exist){
}else{
$sql="create table sc_setting( id int(6) ,name varchar(500) not null ,godo_srl varchar(100) ,url varchar(900) ,price varchar(100) )";
if ($conn->query($sql) === TRUE) { echo "Table created successfully"; } else {
echo "Error creating table: " . $conn->error; }
$sql = " ALTER TABLE sc_setting ADD PRIMARY KEY (`id`) ";
if ($conn->query($sql) === TRUE) { echo "Table altered successfully"; } else {
echo "Error altering table= " . $conn->error;
}
}
for($i=0;$i<100;$i++){ $a=$_REQUEST["a$i"]; $b=$_REQUEST["b$i"]; $c=$_REQUEST["c$i"]; $d=$_REQUEST["d$i"];
echo "a=".$a; echo "<br>"; echo "b=".$b; echo "<br>"; echo "c=".$c; echo "<br>"; echo "d=".$d; echo "<br>";
if($a){
$id=$i;
$sql = "INSERT INTO sc_setting (id, name, godo_srl, url, price) VALUES ('$id','$a','$b','$c','$d') on duplicate key update name='$a',godo_srl='$b',url='$c',price='$d' ";
echo $sql;
if (mysqli_query($conn, $sql)) { echo "New record created successfully";
} else {
echo "Error= " . $sql . "<br>" . mysqli_error($conn);
}
}
 }