在if/else中,在update的where子句中使用select语句的值

4zcjmb1e  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(395)

我重构了这个脚本,首先使用select语句来查看某个表中是否存在记录。
现在,如果什么都不存在,我只是插入。但我的误解是现在的更新。我已经有了 $content 作为变量。
但我该怎么做呢 existingContent 在更新的where子句中选择并使用它(如果在select中找到现有记录,则调用该子句):

$content = $_POST['page_content'];
$panelID = $_POST['panel_type'];
$pageID = $_POST['page_id'];

$checkIfExists = "SELECT COUNT(*) AS count, cont_id as existingContent FROM 
 panels WHERE panel_type_id = $panelID AND page_id = $pageID";

if($mysqlConn->query($checkIfExists)->count = 0){
     //working insert
else{
    $updateContent = "
    UPDATE content
        SET content = '$content'
        WHERE id = /*this would be existingContent from my select above*/
 }

完整更新代码:

$content = $_POST['page_content'];
$panelID = $_POST['panel_type'];
$pageID = $_POST['page_id'];

$checkIfExists = "SELECT COUNT(*) AS count, cont_id as existingContent FROM panels WHERE panel_type_id = $panelID AND page_id = $pageID";

$existingContent = $mysqlConn->query($checkIfExists);

if($existingContent->count == 0){

    $addContent = "
        INSERT INTO content(content)
        VALUES('$content');
    ";

    if ($mysqlConn->query($addContent) === TRUE) {
        $cont_id = $mysqlConn->insert_id;
        $data['last_insert_id'] = $cont_id;
        echo json_encode($data);
    } else {
        echo "Error: " . $addContent . "<br>" . $mysqlConn->error;
    }

    $addPanel = "
        INSERT INTO panels(panel_type_id, page_id, cont_id)
        VALUES ('$panelID', '$pageID', '$cont_id');
    ";

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

else{

    $updateContent = "
        UPDATE content
            SET content = '$content'
            WHERE id = $existingContent->existingContent;
    ";

}
niwlg2el

niwlg2el1#

$checkIfExists = "SELECT COUNT(*) AS count, cont_id as existingContent FROM 
 panels WHERE panel_type_id = $panelID AND page_id = $pageID";

$stmt = $mysqlConn->prepare($checkIfExists); 
$stmt->execute(); 
$row = $stmt->fetch();

if($row['count'] == 0){
     //working insert
else{
    $updateContent = "
    UPDATE content
        SET content = '$content'
        WHERE id = {($row['cont_id']}
 }

也许试试这个?

ua4mk5z4

ua4mk5z42#

$content = $_POST['page_content'];
$panelID = $_POST['panel_type'];
$pageID = $_POST['page_id'];

$checkIfExists = "SELECT COUNT(*) AS count, cont_id as existingContent FROM 
 panels WHERE panel_type_id = $panelID AND page_id = $pageID";

$existingContent = $mysqlConn->query($checkIfExists);

// You had an error in your IF statement you have used (=) instead of (==)
if($existingContent->count == 0){
     //working insert
else{
    $updateContent = "
    UPDATE content
        SET content = '$content'
        WHERE id = $existingContent->existingContent
 }

建议您的代码如果非常不安全,您应该在查询中使用值绑定。
这应该让你开始,但是,请调查值绑定。
祝你好运

相关问题