I created a HTML form with one drop down listing of items from postgresql database, I wish to have some of the fields in the form to populate automatically from the database when an item is selected from the dropdown menu. I also have some other fields on the form which needs to be filled-in by the users before pressing the submit button to push the entire data back to the database.
Currently my script is not able to connect to the database or populate the drop-down listing on the form and also not able to autopopulate the other specified form fields (which are the attributes of the items in the dropdown list also stored in the database) and the second issue im also having is that after entering data into the input fields and click submit. it creates a new record in the database instead of populating the required columns in the database. Can Someone please take a look at my script and help me out.
Here is the script to create the data entry form with a dropdown
<?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']);
$remarks= pg_escape_string($_POST['remarks']);
//<!-- #3 -->
$query = "Update fieldtally set wthick = $wthick, pipeno=$pipeno,heatno1=$heatno1,pipeno2=$pipeno2,heatno2=$heatno2,Djointno=$Djointno,measuredlength=$measuredlength,serialno=$serialno,remarks=$remarks where pipeno = $pipeno;
INSERT INTO fieldtally(wthick, pipeno,heatno1,pipeno2,heatno2,Djointno,measuredlength,serialno,remarks),
Select $wthick, $pipeno,$heatno1,$pipeno2,$heatno2,$Djointno,$measuredlength,$serialno,$remark,
WHERE NOT EXISTS (select pipeno from fieldtally where pipeno = $pipeno)";
//<!-- #4a -->
$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 load data onto the form field and push data back from the database - filename = check1.php
<?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");
// Code to pull data from the database and load onto the form
$pipeno = pg_escape_string($_POST['pipeno']);
$query = "SELECT * FROM fieldtally1 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);
?>
the errors I get when I hit the submit button with or without data is this
Notice: Undefined variable: remark in C:\Users\iegbulefu\Documents\My Web Sites\Personal Site1\autopopulate.php on line 27
Warning: pg_query(): Query failed: ERROR: syntax error at or near "GGFF" LINE 1: ...no2=67677,Djointno=8,measuredlength=80,serialno=99GGFF,remar... ^ in C:\Users\iegbulefu\Documents\My Web Sites\Personal Site1\autopopulate.php on line 30
