php - mySQL->更新/插入 "compound"记录

Question

我有 2 个表，结构如下:

CREATE TABLE `exp_ws_gk_text` (
    `WGT_RID` INT(9) NOT NULL AUTO_INCREMENT,
    `WGT_PRG_CODE` VARCHAR(5) NOT NULL,
    `WGT_TEXT` VARCHAR(4000) NOT NULL,
    INDEX `PrimaryKey` (`WGT_RID`)
)

CREATE TABLE `exp_ws_gk_state` (
    `WGS_RID` INT(9) NOT NULL AUTO_INCREMENT,
    `WGS_TYPE` INT(9) NOT NULL,
    `WGS_STATE` VARCHAR(3) NOT NULL,
    `WGS_WGT_RID` INT(9) NOT NULL,
    INDEX `PrimaryKey` (`WGS_RID`),
    INDEX `SecondaryKey` (`WGS_TYPE`, `WGS_STATE`)
)

这是我查询要在站点中使用的数据的方式:

SELECT a.wgt_rid id, a.wgt_text text, a.wgt_prg_code prg_code, 
       b.wgs_state state, b.wgs_type type, 
FROM exp_ws_gk_text a, exp_ws_gk_state b
WHERE a.wgt_rid = b.wgs_wgt_rid

我展示了包含所有返回数据的记录。现在，当我想要编辑或附加记录时，如果数据是从具有以下参数的表单发送的，我将如何正确保存数据:

$id = intval($_REQUEST['id']);
$prg_code = $_REQUEST['prg_code'];
$state = $_REQUEST['state'];
$crs_type = $_REQUEST['type'];
$text = $_REQUEST['text'];

插入:

$sql    = "BEGIN; ".
          "    INSERT INTO exp_ws_gk_text (WGT_PRG_CODE, WGT_TEXT) ".
          "      VALUES('".$prg_code."', '".$text."'); " .
          "    INSERT INTO exp_ws_gk_state (WGS_STATE, WGS_TYPE, WGS_WGT_RID) ".
          "      VALUES('".$state."', ".$course_type.", LAST_INSERT_ID()); ".
          "COMMIT;";
$mysql_query($sql);
$sql    = "SELECT WGS_WGT_RID id FROM exp_ws_gk_text WHERE WGS_RID = ".mysql_insert_id();
$result = mysql_query($sql);
$r = mysql_fetch_array($result, MYSQL_ASSOC);
echo json_encode(array(
    'id' => $r["id"],
    'prg_code' => $prg_code,
    'state' => $state,
    'type' => $type,
    'text' => $text
));

删除:

DELETE a, b FROM exp_ws_gk_text a
JOIN exp_ws_gk_state b ON a.WGT_RID = b.wgs_wgt_rid
WHERE a.WGT_RID = :id

Answer 1

$sql = "UPDATE exp_ws_gk_text a, exp_ws_gk_state b
SET a.WGT_PRG_CODE = ".$prg_code.", a.WGT_TEXT = ".$text.", b.WGS_STATE = ".$state.", b.WGS_TYPE = ".$crs_type."
WHERE
a.WGT_RID = ".$id." AND a.WGT_RID = b.wgs_wgt_rid";