duandunzhuo3234 2017-03-13 06:39
浏览 38
已采纳

插入查询在服务器上不起作用

Below code runs without any problems on my local server. However, when I try to run it on the intended server, two of my queries don't work - they do not INSERT as they are supposed to. I've marked two queries that don't work with comments, the rest works. Intended server runs on PHP 5.6.30-0+deb8u1.

UPDATE: thanks to aynber, I've tracked the error. This is the error for the first query: prepared statement \"editRecord\" does not exist" I don't understand why this works on local server but not on intended one.

UPDATE 2: error between prepared statement and execution: syntax error at or near \"ON\" LINE 3:

case "editRecord":

$id = openPandoraBox(post("id"));
$tutorAbsence = post("tutorAbsence");
$clientAbsence = post("clientAbsence");

if($tutorAbsence == "1") {

    if(post("tutor") != "0") {
        //  ------------this query does not work.-----------
        $absUpsSql = "INSERT INTO tutorabsence(id, tutorid, reason)
                      VALUES ($1, $2, $3)
                      ON CONFLICT (id)
                      DO UPDATE SET tutorid=$2, reason=$3";

        $absUpsPrep = pg_prepare($conn, 'editRecord', $absUpsSql);
        $absUpsQry = pg_execute($conn, 'editRecord',
                                array($id, post("tutor"), post("tutorreason"))
                     );
    } else {
        $tutorAbsence = "0";
    };
} else {
    $absDelSql = "DELETE FROM tutorabsence WHERE id=$1";
    $absDelPrep = pg_prepare($conn, 'absDel', $absDelSql);
    $absDelQry = pg_execute($conn, 'absDel', array($id));
};

if($clientAbsence == "1"){ 
    if(post("client") != "0") {
        //  ------------this query does not work.-----------
        $absUpsSql = "INSERT INTO clientabsence(id, clientid, reason)
                      VALUES ($1, $2, $3)
                      ON CONFLICT (id)
                      DO UPDATE SET clientid=$2, reason=$3";

        $absUpsPrep = pg_prepare($conn, 'absUps', $absUpsSql);
        $absUpsQry = pg_execute($conn, 'absUps',
                                array($id, post("client"), post("clientreason"))
                     );
    } else {
        $clientAbsence = "0";
    };
} else {
    $absDelSql = "DELETE FROM clientabsence WHERE id=$1"; 
    $absDelPrep = pg_prepare($conn, 'absDelOne', $absDelSql);
    $absDelQry = pg_execute($conn, 'absDelOne', array($id));
};

$resultSql = "UPDATE appointments
                 SET hour=$1, tutorid=$2, 
                     clientid=$3,  purpose=$4, 
                     tutornotshown=$5, clientnotshown=$6
              WHERE appid=$7"; 
$resultPrep = pg_prepare($conn, 'resultSql', $resultSql);
$result = pg_execute($conn, 'resultSql',
                     array(post('hour'), post("tutor"), post("client"),
                           post("purpose"), $tutorAbsence, $clientAbsence, $id
                     )
          );

echo json_encode(array("success" => 1));
break;

展开全部

  • 写回答

2条回答 默认 最新

  • dongshen7561 2017-03-13 12:49
    关注

    Upsert for PostgreSQL below 9.5 is too complicated. I am very short of time, so I'll just use SELECT COUNT(*) and if's.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部