Postgresql updata function

Question

Im wondering if there is anyone out there that can have any idea why im getting this error on my script. I have been working on this script for a while, and cleaned it up to this point. I just need to get some of the fields on my form to populated automatically when an item is selected from the pipeno drop down list which is the trigger in this case. So far Iam just getting this two errors which appears to be what is hindering the script from autopopulating the required fields. Thanks for your contributions

Here are the errors

Error message is

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

Here is my script

 <?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>

check.php file specified in the jquery above

<?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); 
?>

I also think PHP or your database access library might be getting confused by the $$ since $ seems to be used to denote variables in your database access code. — Colin 't Hart
– Colin 't Hart, Commented Oct 29, 2012 at 15:47
Your trigger function doesn't actually make any sense, it has an empty body. See postgresql.org/docs/current/static/plpgsql-trigger.html — Craig Ringer
– Craig Ringer, Commented Oct 29, 2012 at 23:48

Kuberchaun · Accepted Answer · 2012-10-29 15:33:34Z

1

Not sure why you are creating function on the fly and not just deploying it and executing.

For this error

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

Change CREATE FUNCTION to CREATE OR RELACE FUNCTION. This will drop it and recreate it if it already exists.

answered Oct 29, 2012 at 15:33

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shade Over a year ago

Thanks, I actually wanted to create a Trigger function there. I am not sure if im getting the syntax correct. I am not yet familiar with the Trigger function in postgresql which is what i wish to achieve there by making the pipeno selected in the dropdown list to be a trigger for the autopopulation of the the other fields. I have just modified the line, please take a look and see if you can help further. I apprecaite your input. thanks

Louis Huppenbauer · Accepted Answer · 2012-10-29 15:36:04Z

1

You're using the variable $remark but declare it as $remarks. So you're probably missing a s once.

answered Oct 29, 2012 at 15:36

Louis Huppenbauer

3,7141 gold badge20 silver badges24 bronze badges

1 Comment

Shade Over a year ago

Thanks, I have corrected the error though it did'nt provide the solution but I believe it is one step to the solution. Thank you for your observation

Collectives™ on Stack Overflow

Postgresql updata function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related