php - Postgresql更新数据函数

Question

我想知道是否有人知道为什么我的脚本出现此错误。我一直在研究这个脚本一会儿，清理到这一步。我只需要得到一些当一个项目是时自动填充我的表单上的字段从作为触发器的 pipeno 下拉列表中选择案件。到目前为止，我只是收到这两个错误，这似乎是什么阻碍脚本自动填充必填字段。感谢您的贡献

这里是错误

错误信息是

Warning: pg_query(): Query failed: ERROR: syntax error at or near "," LINE 9: ...T wthick = 9.80, pipeno=837913,heatno1=56,pipeno2=,heatno2=6... ^ in C:\Users\iegbulefu\Documents\My Web Sites\Personal Site1\dropdown3.php on line 32

这是我的脚本

 <?php
//Php Code to connect to postgresqldatabase
$PGHOST = "localhost:25376";
 $PGDATABASE = "Pipeline";
 $PGUSER = "postgres";
 $PGPASSWORD = "Casa2009";
 $PGPORT = 5432;
 $db_handle = pg_connect("dbname=$PGDATABASE user=$PGUSER password=$PGPASSWORD");
//<!-- #2 -->
 if(isset($_POST['submit_1'])){
 //Code to post fieldtally data to the database
 $pipeno = pg_escape_string( $_POST['pipeno']);
 $heatno1 = pg_escape_string( $_POST['heatno1']);
 $pipeno2 = pg_escape_string( $_POST['pipeno2']);
 $heatno2 = pg_escape_string($_POST['heatno2']);
 $Djointno = pg_escape_string($_POST['Djointno']);
 $measuredlength = pg_escape_string($_POST['measuredlength']);
 $serialno = pg_escape_string($_POST['serialno']);
 $wthick= pg_escape_string($_POST['wthick']);
 $remark= pg_escape_string($_POST['remarks']); 
 //<!-- #3 -->
$query = "CREATE FUNCTION trigf()  RETURNS trigger AS 'fieldtally';
            CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE ON fieldtally
              FOR EACH ROW EXECUTE PROCEDURE trigf();

          SELECT * FROM fieldtally WHERE pipeno = $pipeno;

          INSERT INTO fieldtally VALUES('$wthick', '$pipeno','$heatno1','$pipeno2','$heatno2','$Djointno','$measuredlength','$serialno','$remark');

          UPDATE fieldtally SET wthick = $wthick, pipeno=$pipeno,heatno1=$heatno1,pipeno2=$pipeno2,heatno2=$heatno2,Djointno=$Djointno,measuredlength=$measuredlength,serialno=$serialno,remarks=$remark WHERE pipeno = $pipeno;
          LANGUAGE plpgsql";
 $result = pg_query($query);
 if (!$result) {
 $errormessage = pg_last_error();
 $message = "Error with query: " . $errormessage;
 }
 $message = sprintf ("These values were inserted into the pipeline database - %s %s %s %s %s %s %s %s %s",$wthick,$pipeno,$heatno1,$pipeno2,$heatno2,$Djointno,$measuredlength,$serialno,$remark);
 }
 //<!-- #2 -->
 if(isset($_POST['submit_2'])){
 //Code to post fieldbend data under suto the database
 $pipeno = pg_escape_string( $_POST['pipeno']);
 $wthick1= pg_escape_string($_POST['wthick1']);
 $heatno1 = pg_escape_string( $_POST['heatno1']);
 $pipeno2 = pg_escape_string( $_POST['pipeno2']);
 $heatno2 = pg_escape_string($_POST['heatno2']);
 $Djointno = pg_escape_string($_POST['Djointno']);
 $measureddistance = pg_escape_string($_POST['measureddistance']);
 $benddegree = pg_escape_string($_POST['benddegree']);
 $bendtype= pg_escape_string($_POST['bendtype']); 
 $remark= pg_escape_string($_POST['remarks']);
 //<!-- #3 -->
$query1 = "INSERT INTO fieldbend(pipeno,wthick1,heatno1,pipeno2,heatno2,Djointno,measureddistance,benddegree,bendtype,remarks)VALUES ('$pipeno','$wthick1','$heatno1','$pipeno2','$heatno2','$Djointno','$measureddistance','$benddegree','$bendtype','$remark') ON DUPLICATE KEY UPDATE wthick1='$wthick1',heatno1='$heatno1',pipeno2='$pipeno2',heatno2='$heatno2',Djointno='$Djointno''";

//<!-- #4a -->
 $result1 = pg_query($query1);
 if (!$result1) {
 $errormessage = pg_last_error();
 $message1 = "Error with query: " . $errormessage;
 }
 $message1 = sprintf ("These values were inserted into the pipeline database - %s %s %s %s %s %s %s %s %s %s",$pipeno,$wthick1,$heatno1,$pipeno2,$heatno2,$Djointno,$measureddistance,$benddegree, $bendtype,$remark);
 }
 //<!-- #2 -->
 if(isset($_POST['submit_3'])){
 //Code to post apptally data under suto the database
 $pipeno = pg_escape_string( $_POST['pipeno']);
 $wthick2= pg_escape_string($_POST['wthick2']);
 $tallytype = pg_escape_string( $_POST['tallytype']);
 $qty = pg_escape_string( $_POST['qty']);
 $serialno = pg_escape_string($_POST['serialno']);
 $referenceid = pg_escape_string($_POST['referenceid']);
 //<!-- #3 -->
$query2 = "INSERT INTO apptally(pipeno,wthick2,tallytype,qty,serialno,referenceid)VALUES ('$pipeno','$wthick2','$tallytype','$qty','$serialno','$referenceid') ON DUPLICATE KEY UPDATE wthick2='$wthick2', pipeno='$pipeno'";

//<!-- #4a -->
 $result2 = pg_query($query2);
 if (!$result2) {
 $errormessage = pg_last_error();
 $message2 = "Error with query: " . $errormessage;
 }
 $message2 = sprintf ("These values were inserted into the Pipeline database - %s %s %s %s %s %s",$pipeno,$wthick2,$tallytype,$qty, $serialno,$referenceid);
 }
 // Code to pull data from the database and load onto the form
 $query = 'select pipeno from fieldtally order by pipeno asc'; 
 $result = pg_query($db_handle,$query); 
 while ($row = pg_fetch_row($result))
 {
    // Creates Arrays to use in dropdowns
     $pipeno_array[] = $row[0];

 } 
 // This function creates dropdowns that will be used in the forms
 function dropdown($field_name, $num){
     // Creates the Dropdown
     //<!-- #5a -->
     $c = ($field_name == 'pipeno') ? ' onChange="check('.$num.');"' : '';
     echo "<select name=\"".$field_name."\" id=\"".$field_name.$num."\"$c>\n";
     echo "<option value=\"\"> --- Select --- </option>\n";
     // Chooses which array to use for Dropdown options
     global $pipeno_array;
     $name_array = ($field_name == 'pipeno') ? $pipeno_array : $wallthick;
     // Creates the Dropdown options based off the array above
     foreach($name_array as $k){
         echo "<option value=\"$k\">$k</option> \n"; }
     // Ends the Dropdown
     echo "</select>\n";
 }
?>
<html>
<head>
<meta charset="utf-8">
<title>UG Pipeline Field Data Capture</title>
</head>
<body>
     <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
     </head>
      <body>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
<script type="text/javascript">

<!-- #5b -->
        function check(num){
           var pipeno_id = '#pipeno_'+num;
           var pipeno = $(pipeno_id).val();
           if(pipeno!= ""){
             jQuery.ajax({
             type: "POST",
             url: "check.php" , 
             data:'pipeno='+pipeno,
             cache: false,
             success: function(response){
             var response_array = JSON.parse(response);
             $('#heatno1').val(response_array['heatno1']);
             $('#pipeno2').val(response_array['pipeno2']); 
             $('#heatno2').val(response_array['heatno2']);
             $('#Djointno').val(response_array['Djointno']);
             }           
            });
           }
           else{
           $('#heatno1').val('');
           $('#pipeno2').val('');
           $('#heatno2').val('');
           $('#Djointno').val('');}
            }
  </script>
          <!-- #4b -->
 <!--<?php printf($message);?>-->

 <!-- #6.1 -->
<form action="" method="post">
 <table width="800" cellpadding= "10" cellspacing="1" border="2">
 <tr align="center" valign="top">
 <td align="center" colspan="1" rowspan="1" bgcolor="#00CC00">
 <h3>Input Field Tally Information</h3>
      Select Wall Thickness:<select name="wthick" id="wthick">
 <!-- #7.1 -->
  <option value=""> --Select-- </option> 
  <option value="9.80">  9.80  </option>
  <option value="13.50"> 13.50 </option>
  <option value="15.90"> 15.90 </option>
  </Select>           
 Select Pipe No:<?php dropdown('pipeno', 1); ?> HeatNo1: <input type="text" name="heatno1" id="heatno1"><br /><br />  
 PipeNo2: <input type="text" name="pipeno2" id="pipeno2"> HeatNo2: <input type="text" name="heatno2" id="heatno2">Joint No: <input type="text" name="Djointno"><br /><br /> 
  Input measured Length: <input type="text" name="measuredlength"> Input Serial No: <input type="text" name="serialno"><br><br> Remarks: <input type="text" name="remarks"><br><br> 
 <!-- #8.1 -->
 <input type="Submit" name="submit_1" value="Submit">
 <!-- #9.1 -->
</td></tr></table></form>
<!-- #6.2 -->
 <form action="" method="post">
 <table width="800" cellpadding= "10" cellspacing="1" border="2">
 <tr align="center" valign="top">
 <td align="center" colspan="1" rowspan="1" bgcolor="#FF99FF">
 <h3>Input Field Bend Information</h3>
  Select Wall Thickness:<select name="wthick1" id="wthick1">
 <!-- #7.2 -->
     <option value=""> --Select-- </option> 
     <option value="9.80">  9.80  </option>
     <option value="13.50">13.50 </option>
     <option value="15.90"> 15.90 </option>
  </select>
 <!-- #10.1 -->           
 Select Pipe No:<?php dropdown('pipeno', 2); ?>   HeatNo1: <input type="text" name="heatno1" id="heatno1_2"> <br><br>
 PipeNo2: <input type="text" name="pipeno2" id="pipeno2_2"> HeatNo2: <input type="text" name="heatno2" id="heatno2_2"> Joint No: <input type="text" name="Djointno"> <br><br>
 Input Measured Distance: <input type="text" name="measureddistance"> Input Bend Angle: <input type="text" name="benddegree"> <br><br>
Select Bend Type:<select name="bendtype" id="bendtype">
    <option value=""> --Select-- </option> 
    <option value="Combo">Combo</option>
    <option value="SAG">SAG</option>
    <option value="OB">OB</option>
    <option value="SBRT">SBRT</option>
    <option value="SBLT">SBLT</option>
    <option value="HBLT">HBLT</option>
    <option value="HBRT">HBRT</option><p></p> 
  Remarks: <input type="text" name="remarks"><br></br>

  <input type="Submit" name="submit_2" value="Submit"> 
 <!-- #9.2 -->
</td></tr></table></form>
<!-- #6.3 -->

<form action="" method="post">
 <table width="800" cellpadding= "10" cellspacing="1" border="2">
 <tr align="center" valign="top">
 <td align="center" colspan="1" rowspan="1" bgcolor="#99FF33">
 <h3>Input App. Tally Information</h3>
 <!-- #11 -->
       Select Wall Thickness:<select name="wthick2" id="wthick2">
 <!-- #7.3 -->
     <option value=""> --Select-- </option> 
     <option value="9.80">  9.80  </option>
     <option value="13.50"> 13.50 </option>
     <option value="15.90"> 15.90 </option>
  </select>
 <!-- #10.2 -->             
 Select Pipe No:<?php dropdown('pipeno', 3); ?>  <br><br> Input Tally Type: <input type="text" name="tallytype">
 Input Tally Qty: <input type="text" name="qty"><br></br>  Input Serial No: <input type="text" name="serialno"> 
 RefID: <input type="text" name="referenceid"><br></br>
 <!-- #8.3 -->
 <input type="Submit" name="submit_3" value="Submit">
 </td></tr></table>
</form>
</body>
</html>

上面jquery中指定的check.php文件

<?php
//Php Code to connect to postgresqldatabase
include connection.php;
// Code to pull data from the database and load onto the form  
 $pipeno = pg_escape_string($_POST['pipeno']);
//$query = 'SELECT heatno1,pipeno2,jointno,FROM fieldtally WHERE pipeno = $pipeno';
$query = 'select * from fieldtally order by pipeno asc';
$result = pg_query($db_handle,$query);
$row = pg_fetch_row($result);
$row_info = array('heatno1'=>$row[1],'pipeno2'=>$row[2],'heatno2'=>$row[3],'jointno'=>$row[4]);
$row_info = json_encode($row_info);
print_r($row_info); 
?>

Answer 1

不确定为什么要即时创建函数，而不只是部署和执行它。

对于这个错误

Warning: pg_query(): Query failed: ERROR: function "updatefunction" already exists with same argument types in C:\Users\iegbulefu\Documents\My Web Sites\Personal Site1\autopopulate.php on line 35

将 CREATE FUNCTION 更改为 CREATE OR RELACE FUNCTION。这将删除它并重新创建它(如果它已经存在)。