Autopopulate Html Form Fields From Postgresql Database

[emmy]

Hi all, I have been on this script for a while, the objective is to automatically populate the fields in the html form once an item is selected from the drop down list. The dropdown contains tuples in a postgresql database and a selection of each of the tuples should bring up the other tuples in that record to fill-in the specified fields on the form. The HTML form also contains some input fields which nees to be manually entered by the users.

The script is expected to automatically update the fields in the database after the submit tab is hit i.e if the selected tuple in drop down exists, where it does not exist, the script should flash an error and prompt the user to enter the data manually - in that case a blank form will appear for the user to enter the information which will also get posted to the database.

So far I have two drop down listing on the form, one is dynamic(i.e picks data from the database) and the other is a static dropdown. These two are working perfectly well but I haven't been able to get the other required fields to automatically populate from the database. I am hoping to get some positive contributions and help from this forum as I have tried posting this on one other forum without much results, though they hints i received were very helpful to get me this far. The script is a combination of HTML, JQuery and PHP.

All positive contributions will be highly appreciated. Thanks you for taking the time to make look at this issue!

Below is my script and attached is what the form looks like form2.pdf

 

<script removed, please re-enter following directions in post #3>

Edited October 27, 2012 by Philip

[MDCode]

Wow...please organize your code first.

[Philip]

You'll need to disable the WYSIWYG editor (the top left button), paste as plain text (top right, the clipboard icon with a blank paper) or use the WYSIWYG editor's code input (the <> icon on the bottom row).

[emmy]

Right now, its not even giving me the permission to edit. I want to remove the post and repost it properly. Please how do i do this

[emmy]

Sorry I had to repost this question because my first posting appeared to be disjointed because i had problems posting the script along with the problem description which makes it difficult for people to follow.

so here is the inital problem desription.

have been on this script for a while, the objective is to automatically populate the fields in the html form once an item is selected from the drop down list. The dropdown contains tuples in a postgresql database and a selection of each of the tuples should bring up the other tuples in that record to fill-in the specified fields on the form. The HTML form also contains some input fields which nees to be manually entered by the users.

The script is expected to automatically update the fields in the database after the submit tab is hit i.e if the selected tuple in drop down exists, where it does not exist, the script should flash an error and prompt the user to enter the data manually - in that case a blank form will appear for the user to enter the information which will also get posted to the database.

So far I have two drop down listing on the form, one is dynamic(i.e picks data from the database) and the other is a static dropdown. These two are working perfectly well but I haven't been able to get the other required fields to automatically populate from the database. I am hoping to get some positive contributions and help from this forum as I have tried posting this on one other forum without much results, though they hints i received were very helpful to get me this far. The script is a combination of HTML, JQuery and PHP.

All positive contributions will be highly appreciated. Thanks you for taking the time to make look at this issue!

and here is the script so far

<?php
//Php Code to connect to postgresqldatabase
include connection.php;
//<!-- #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']);
$remarks= pg_escape_string($_POST['remarks']); 
//<!-- #3 -->
$query = "CREATE FUNCTION updatefunction() RETURNS void AS $$
           Begin
            IF EXISTS( SELECT * FROM fieldtally WHERE pipeno = $pipeno ) THEN
                       UPDATE fieldtally
                       SET set  wthick = $wthick, pipeno=$pipeno,heatno1=$heatno1,pipeno2=$pipeno2,heatno2=$heatno2,Djointno=$Djointno,measuredlength=$measuredlength,serialno=$serialno,remarks=$remarks WHERE pipeno = $pipeno;
            ELSE
                       INSERT INTO fieldtally VALUES($wthick, $pipeno,$heatno1,$pipeno2,$heatno2,$Djointno,$measuredlength,$serialno,$remark);
             END IF;

             Return;
           END;
           $$ 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,$remarks);
}
//<!-- #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']); 
$remarks= 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','$remarks') 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,$remarks);
}
//<!-- #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>

PHP script to pull and load data from database onto forms i.e check.php file

<?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 $_GET['pipeno'];
$query = "SELECT heatno1,pipeno2, jointno, FROM fieldtally WHERE pipeno = $pipeno ";
$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); 
?>

form1.pdf

Edited October 27, 2012 by Pikachu2000
[code] . . . [/code] BBCode tags added.

[Pikachu2000]

Don't double post.

When posting code, enclose it within the forum's

 . . . 

BBCode tags.

 

[emmy]

Thanks Pikachu, im new to this forum and was having a little difficulty trying to format the code to fit into the code tag. So i was adviced to just post it as a text file but i can see you've given it a good format. Thanks again. As for the double posting, I already explained above because i needed to get viewers to read the problem description and see the script immediately after; which is what i couldnt achieve in my previous post. I wanted to delete the previous post but it wouldn't even give me access to modify or delete it

[Pikachu2000]

I've deleted the other block of code for you, to make the thread easier to read . . .