Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would do this by using Ajax and JQuery. You would need to do 3 things - Add Ajax/JQuery code in <code>head</code> of document, create php file for Ajax to connect to, and add <code>onChange</code>, <code>id</code> &amp; <code>value</code> attributes to fields.</p> <p>In your <code>&lt;head&gt;&lt;/head&gt;</code> add this javascript after your <code>&lt;title&gt;&lt;/title&gt;</code>-</p> <pre><code>&lt;script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"&gt;&lt;/script&gt; &lt;script type="text/javascript"&gt; function check(){ var pipeno = $('#pipeno').val(); if(pipeno != "pipeno"){ jQuery.ajax({ type: "POST", url: "check.php", data: 'pipeno='+pipeno, cache: false, success: function(response){ var response_array = JSON.parse(response); $('#wallthickness').val(response_array['wallthickness']); $('#jointno').val(response_array['jointno']); $('#measuredlength').val(response_array['measuredlength']); $('#serialno').val(response_array['serialno']);} }); } else{ $('#wallthickness').val(''); $('#jointno').val(''); $('#measuredlength').val(''); $('#serialno').val('');} } &lt;/script&gt; </code></pre> <p>Then create a file named <code>check.php</code> with code -</p> <pre><code>&lt;?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 * FROM fieldtally WHERE pipeno = $pipeno "; $result = pg_query($db_handle,$query); $row = pg_fetch_row($result)) $row_info = array('wallthickness'=&gt;$row[1],'jointno'=&gt;$row[2],'measuredlength'=&gt;$row[3],'serialno'=&gt;$row[4]); $row_info = json_encode($row_info); print_r($row_info); ?&gt; </code></pre> <p>And finally, add <code>id</code> &amp; <code>value</code> attributes to your form fields (Don't change your file, just update these lines)</p> <pre><code>Select Pipe No:&lt;select name="pipeno" id="pipeno" onChange="check()"&gt;&lt;option value="pipeno"&gt; --Select-- &lt;/option&gt; ... (keep your database connection / how you create your dropdown, etc here, just edit the option below) echo "&lt;option value=\"$pipeno\"&gt; $pipeno&lt;/option&gt;"; ... (keep your database connection / how you create your dropdown, etc. here) &lt;/select&gt; ... Input Joint No: &lt;input type="text" name="jointno" id="jointno"&gt; Input Wall Thickness: &lt;input type="text" name="wallthickness" id="wallthickness"&gt; Input measured Length: &lt;input type="text" name="measuredlength" id="measuredlength"&gt; Input Serial No: &lt;input type="text" name="serialno" id="serialno"&gt; </code></pre> <hr> <p>UPDATED 10/19 Due to changed code - </p> <p>I have put numbered anchors in your code, and the notes are at the end. You can do this all in 2 files, but the second file <code>check1.php</code>, can only have the 1 database query, or you will have errors in your ajax.</p> <p>autopopulate.php-</p> <pre><code>&lt;!-- #1 --&gt; &lt;?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"); //&lt;!-- #2 --&gt; if(isset($_POST['submit_1'])){ //Code to post data to the database $pipeno = pg_escape_string( $_POST['pipeno']); $wallthickness = pg_escape_string($_POST['wallthickness']); $heatno1 = pg_escape_string( $_POST['heatno1']); $pipeno2 = pg_escape_string( $_POST['pipeno2']); $heatno2 = pg_escape_string($_POST['heatno2']); $jointno = pg_escape_string($_POST['jointno']); $measuredlength = pg_escape_string($_POST['measuredlength']); $serialno = pg_escape_string($_POST['serialno']); $wthick= pg_escape_string($_POST['wthick']); //&lt;!-- #3 --&gt; $query = "INSERT INTO fieldtally1(pipeno,wallthickness,heatno1,pipeno2,heatno2,jointno,measuredlength,serialno,wthick)VALUES ('$pipeno','$wallthickness','$heatno1','$pipeno2','$heatno2','$jointno','$measuredlength','$serialno','$wthick') ON DUPLICATE KEY UPDATE wallthickness='$wallthickness',heatno1='$heatno1',pipeno2='$pipeno2',heatno2='$heatno2',jointno='$jointno',measuredlength='$measuredlength',serialno='$serialno',wthick='$wthick'"; //&lt;!-- #4a --&gt; $result = pg_query($query); if (!$result) { $errormessage = pg_last_error(); $message = "Error with query: " . $errormessage; } $message = sprintf ("These values were inserted into the database - %s %s %s %s %s %s %s %s %s",$pipeno,$wallthickness,$heatno1,$pipeno2,$heatno2,$jointno,$measuredlength,$serialno,$wthick); } // Code to pull data from the database and load onto the form $query = 'select pipeno, wallthickness from fieldtally1 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]; $wallthickness_array[] = $row[1]; } // This function creates dropdowns that can be used in your forms function dropdown($field_name, $num){ // Creates the Dropdown //&lt;!-- #5a --&gt; $c = ($field_name == 'pipeno') ? ' onChange="check('.$num.');"' : ''; echo "&lt;select name=\"".$field_name."\" id=\"".$field_name.$num."\"$c&gt;\n"; echo "&lt;option value=\"\"&gt; --- Select --- &lt;/option&gt;\n"; // Chooses which array to use for Dropdown options global $pipeno_array, $wallthickness_array; $name_array = ($field_name == 'pipeno') ? $pipeno_array : $wallthickness_array; // Creates the Dropdown options based off the array above foreach($name_array as $k){ echo "&lt;option value=\"$k\"&gt;$k&lt;/option&gt; \n"; } // Ends the Dropdown echo "&lt;/select&gt;\n"; } ?&gt; &lt;html&gt; &lt;head&gt;&lt;title&gt;UG Pipeline Field Data Capture&lt;/title&gt;&lt;/head&gt; &lt;body&gt; &lt;script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"&gt;&lt;/script&gt; &lt;script type="text/javascript"&gt; &lt;!-- #5b --&gt; function check(num){ var pipeno_id = '#pipeno_'+num; var pipeno = $(pipeno_id).val(); if(pipeno != ""){ jQuery.ajax({ type: "POST", url: "check1.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']); $('#jointno').val(response_array['jointno']); //$('#measuredlength').val(response_array['measuredlength']); // this should be input from the user // $('#serialno').val(response_array['serialno']); //This should also be input from the user } }); } else{ $('#heatno1').val(''); $('#pipeno2').val(''); $('#heatno2').val(''); $('#jointno').val('');} } &lt;/script&gt; &lt;!-- #4b --&gt; &lt;?php printf($message);?&gt; &lt;!-- #6.1 --&gt; &lt;form action="" method="post"&gt; &lt;table width="800" cellpadding= "10" cellspacing="1" border="2"&gt; &lt;tr align="center" valign="top"&gt; &lt;td align="center" colspan="1" rowspan="1" bgcolor="#64b1ff"&gt; &lt;h3&gt;Input Field Tally Information&lt;/h3&gt; Select Pipe Thickness:&lt;select name="wthick" id="wthick"&gt; &lt;!-- #7.1 --&gt; &lt;option value=""&gt; --Select-- &lt;/option&gt; &lt;option value="9.8"&gt; 9.8 &lt;/option&gt; &lt;option value="13.5"&gt; 13.5 &lt;/option&gt; &lt;option value="15.9"&gt; 15.9 &lt;/option&gt; &lt;/Select&gt; Select Pipe No:&lt;?php dropdown('pipeno', 1); ?&gt; Select Wall Thickness:&lt;?php dropdown('wallthickness', 1); ?&gt;&lt;br /&gt;&lt;br /&gt; HeatNo1: &lt;input type="text" name="heatno2" id="heatno1"&gt; PipeNo2: &lt;input type="text" name="pipeno2" id="pipeno1"&gt; HeatNo2: &lt;input type="text" name="heatno2" id="heatno2"&gt;&lt;br /&gt;&lt;br /&gt; Joint No: &lt;input type="text" name="jointno"&gt; Input measured Length: &lt;input type="text" name="measuredlength"&gt; Input Serial No: &lt;input type="text" name="serialno"&gt;&lt;br&gt;&lt;br&gt; &lt;!-- #8.1 --&gt; &lt;input type="Submit" name="submit_1" value="Submit"&gt; &lt;!-- #9.1 --&gt; &lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;&lt;/form&gt; &lt;p&gt;&lt;/p&gt; &lt;!-- #6.2 --&gt; &lt;form action="" method="post"&gt; &lt;table width="800" cellpadding= "10" cellspacing="1" border="2"&gt; &lt;tr align="center" valign="top"&gt; &lt;td align="center" colspan="1" rowspan="1" bgcolor="#ff9d9d"&gt; &lt;h3&gt;Input Field Bend Information&lt;/h3&gt; Select Wall Thickness:&lt;select name="wallthickness" id="wallthickness"&gt; &lt;!-- #7.2 --&gt; &lt;option value=""&gt; --Select-- &lt;/option&gt; &lt;option value="9.8"&gt; 9.8 &lt;/option&gt; &lt;option value="13.5"&gt;13.5 &lt;/option&gt; &lt;option value="15.9"&gt; 15.9 &lt;/option&gt; &lt;/select&gt; &lt;!-- #10.1 --&gt; Select Pipe No:&lt;?php dropdown('pipeno', 2); ?&gt; Select Wall Thickness:&lt;?php dropdown('wallthickness', 2); ?&gt;&lt;br /&gt;&lt;br /&gt; HeatNo1: &lt;input type="text" name="heatno1" id="heatno1_2"&gt; PipeNo2: &lt;input type="text" name="pipeno2" id="pipeno2_2"&gt; HeatNo2: &lt;input type="text" name="heatno2" id="heatno2_2"&gt;&lt;br /&gt;&lt;br /&gt; Joint No: &lt;input type="text" name="jointno"&gt; Input Measured Distance: &lt;input type="text" name="measureddistance"&gt;&lt;br&gt;&lt;br&gt; Input Bend Angle: &lt;input type="text" name="benddegree"&gt; Input Bend Type: &lt;input type="text" name="bendtype"&gt;&lt;br&gt;&lt;br&gt; &lt;!-- #8.2 --&gt; &lt;input type="Submit" name="submit_2" value="Submit"&gt; &lt;!-- #9.2 --&gt; &lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;&lt;/form&gt; &lt;p&gt;&lt;/p&gt; &lt;!-- #6.3 --&gt; &lt;form action="" method="post"&gt; &lt;table width="800" cellpadding= "10" cellspacing="1" border="2"&gt; &lt;tr align="center" valign="top"&gt; &lt;td align="center" colspan="1" rowspan="1" bgcolor="#66CC66"&gt; &lt;h3&gt;Input App. Tally Information&lt;/h3&gt; &lt;!-- #11 --&gt; Select Wall Thickness:&lt;select name="wallthickness1" id="wallthickness1"&gt; &lt;!-- #7.3 --&gt; &lt;option value=""&gt; --Select-- &lt;/option&gt; &lt;option value="9.8"&gt; 9.8 &lt;/option&gt; &lt;option value="13.5"&gt; 13.5 &lt;/option&gt; &lt;option value="15.9"&gt; 15.9 &lt;/option&gt; &lt;/select&gt; &lt;!-- #10.2 --&gt; Select Pipe No:&lt;?php dropdown('pipeno', 3); ?&gt; Select Wall Thickness:&lt;?php dropdown('wallthickness', 3); ?&gt;&lt;br /&gt;&lt;br /&gt; Input Tally Type: &lt;input type="text" name="type"&gt; Input Serial No: &lt;input type="text" name="serialno"&gt;&lt;br&gt;&lt;br&gt; Input Reference ID: &lt;input type="text" name="referenceid"&gt;&lt;br&gt;&lt;br&gt; &lt;!-- #8.3 --&gt; &lt;input type="Submit" name="submit_3" value="Submit"&gt; &lt;/td&gt;&lt;/tr&gt;&lt;/table&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p>check1.php-</p> <pre><code>&lt;?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'=&gt;$row[1],'pipeno2'=&gt;$row[2],'heatno2'=&gt;$row[3],'jointno'=&gt;$row[4]); $row_info = json_encode($row_info); print_r($row_info); ?&gt; </code></pre> <p>Here are the issues-</p> <p>1 - Move all <code>&lt;?php ?&gt;</code> scripts to the top of the page. This will: (1) clean up your code, (2) be able to process all database queries at the same time.</p> <p>2 - Your query to update <code>fieldtally1</code> is now done before you get your dropdowns from the database.</p> <p>3 - To fix your second issue <code>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</code> use <code>INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE ...</code>. If <code>pipeno</code> is already in the database (since it is a <code>Primary Key</code>) it will <code>UPDATE</code> instead of <code>INSERT</code>.</p> <p>4 - I recommend saving your <code>error</code> or <code>success</code> message (#4a) and echo it at the top of your html (#4b).</p> <p>5a - You changed from a hard coded <code>pipeno</code> dropdown, back to the dynamically dropdown using the function. So you are missing the javascript - <code>onChange=check();</code> 5b - now that you are creating your <code>pipeno</code> &amp; <code>onChange=check();</code> dynamically, you have to change your <code>check()</code> function to get the id dynamically as well.</p> <p>6 - <code>$PHP_SELF</code> is not valid. I think you were trying for <code>$_SERVER['PHP_SELF']</code>, but this is easily hacked, so it is better just to use <code>action=""</code>. [6.1,6.2,6.3] </p> <p>7 - When doing your <code>&lt;select&gt;&lt;option&gt;</code> you were using the same <code>value=""</code>, but each one needs to be different for you to get the value when posting. Also, most <code>&lt;/option&gt;</code>'s were misspelled as <code>&lt;/optio&gt;</code>. [7.1,7.2,7.3]</p> <p>8 - If you have 3 forms on the same page, each one has to have a different name, or you will not be able to tell which submit button was clicked. [8.1,8.2,8.3]</p> <p>9 - Your first two forms were missing closing tags - <code>&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;&lt;/form&gt;</code>. [9.1, 9.2]</p> <p>10 - In you 2 &amp; 3 forms, you are using <code>id</code>'s that are used in the 1st form. This is invalid as every <code>id</code> must be unique. [10.1,10.2]</p> <p>11 - 3rd form uses <code>id="wallthickness"</code> that was used in form 2. see above about <code>id</code> uniqueness. </p>
    singulars
    1. This table or related slice is empty.
    1. POPHP\HTML script to auto populate form fields when an item is selected from a dropdown list
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSean
    1. USSean
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks Sean for helping,I did the modifications, the script is able to create the form with the dropdown but it is not able to populate the dropdownlist or the listed formfeilds. It looks to me like the php script is not been accessed by the Javascript because It does not echo the cheque "connection succeded" to confirm connection to the database. I have modified my script above to refelct what I have so far.
      singulars
      1. PO
      1. USShade
    2. COHi Sean, I have been working on the script, You recall, initially I had the Wallthickness to be a dropdown but the current script kind of made it auto populate instead of dropdown. Though both the autopopulate and the dropdown is currently not populating, I am just making the Wallthickness to be static populated as defined in the option and it is working perfectly fine, but the dropdown populate for the pipeno as well as the autopopulate defined for the various field is yet to function well. I have pasted the script above. I appreciate your help please!
      singulars
      1. PO
      1. USShade
    3. COIt is not creating you `pipeno` dropdown, because you removed your database query. I did not want to retype your database query so that is why I just typed `...` before and after the `echo "<option value=\"$pipeno\"> $pipeno</option>";` to show that you just needed to update a few things with `id`, `value`, and `onChange`. You need to re-add your method of creating your dropdown. Also, you need to remove `if($db_handle){echo'Connection attempt succeeded.';}else{echo 'Connection attempt failed.';}` from your `check.php` file as this will cause issues with the Ajax post.
      singulars
      1. PO
      1. USSean
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload