duandunzhuo3234
2017-03-13 14: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 20: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.

    打赏 评论
  • dsajkdadsa14222 2017-03-13 15:24

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

    If it works on your local server but not on production server, it is likely that they don't run the same version of PostgreSQL. ON CONFLICT is a feature that was released with PostgreSQL 9.5 (https://www.postgresql.org/docs/9.5/static/sql-insert.html) which is still fairly recent.

    You should run this query on the production server to check out which version of PostgreSQL it uses:

    SELECT version();
    

    Your server probably runs PostgreSQL 9.5 or 9.6 while the production server is probably on an older release.

    打赏 评论

相关推荐 更多相似问题