Display data from 2 dropdown tables and save to 3rd table

Get Started
1

After over a week of trying various tutorials and web searches, I have spent the day working through the post ‘Display other data based on dropdown selection’ (which was closed last year) for help posted by @fredep57 which @James_Hibbard went into marvelous detail in his reply. The post is almost exactly what I’m trying to do, except that I need to query 2 database tables and save the results to a 3rd database table.

My 3 tables are as follows:
chemicals
c_id int(11) No
unno varchar(10) No
name text No

incidents
site_no smallint(4) No
status int(11) Yes NULL
incident_date date Yes NULL

products
product_id int(4) No
s_no int(4) No
u_no varchar(10) No

As there can be more than 1 chemical related to an incident, I need to save the site no and the UN no to the product table to use in other forms.

After following the post above, I can now get the chemicals into a dropdown list and output them to the screen. How do I add the incidents dropdown list? and how do I save the results to the products database?

Please, I’ve been putting this web app together for over a year now and I’m on the last bit, any help is appreciated.

Thank you

1 Like
2

Can’t you make a form that gets the chemicals and the incidents.

Bassicly add a second drop down maybe.
And when you send then you could get the chemical and the incident.

3

That’s what I’m trying to do, but it isn’t working.

4

Hmm, what do you get back when send the form. ( var dump )

And how does your form look like.

5

At the moment, I only have the 2 dropdown lists and places that should be filled with the data returned from the selection.

How do I do a ‘var dump’?

6

Do you use php ?
If so send the form to a page that contains.

<?php
var_dump($_POST);
7

I must be doing it wrong because I’m not getting anything.

8

Ok, I’m getting this from the ajax file:

array (size=0)
empty

9

Could you share your code on github gist ?

10

This is the page that it’s on:

11

try moving the vardump above your queries or better only get your vardump and do nothing else.
And also if you view your page source of the form and look at the option elements does it show anything you would expect ?

12

I’ve tried moving the vardump down the code in steps and I get the same result all the way down - array (size=0)

In the Site No select box, it gives me the status numbers instead of the site_no’s and in the UN No select box, I get the UN No’s as it should.

13

But if I select from either boxes, it doesn’t output to the boxes below.

I managed to get the site no select box to show the site no’s instead of the status. :smile:

14

and now it shows data in vardump or still empty ?

15

still empty

I also tried ‘echo $_post;’ - I guess I did that wrong because it didn’t do anything at all.

16

hmm maybe a wacky thought try sending the form without ajax and let it send to a php page that only contains the vardump.

17

Ok, I deleted the ajax scripts and checked it and I still get my dropdown lists - beautifully populated - with the blank boxes below.
I hope this is what you mean by ‘php page that only contains the vardump’ …
I’ve created a file called vardump.php which has only the following code in it:

<? include('product.php'); var_dump($_post); ?>

It doesn’t show anything at all when I run it

18

no your form action should point to : yourfile.php ← Contains :

<?php

vardump($_POST);
19

Ok, so my vardump.php only has the vardump in it
my products.php has the includes(‘vardump.php’); in it
and it doesn’t do anything

20

change product.php to this :

  <?php
$servername = "localhost";
$username = "xyz";
$password = "xyz";

try {
    $conn = new PDO("mysql:host=$servername;dbname=dbdemo", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

  # Perform database query - Sites
  $query1 = "SELECT site_no, status from incidents";
  $result1 = $conn->query($query1);
  
  # Perform database query - Chemicals
  $query = "SELECT c_id, unno, name from chemicals";
  $result = $conn->query($query);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta charset="utf-8">
    <title>Select - onchange AJAX example</title>
  </head>

  <body>
<form action="vardump.php" method="post">
    <label for="siteId">Site No:</label>
    <select id="siteId">
      <option value="0">Please select</option>
      <?php
        while($row1 = $result1->fetch(PDO::FETCH_ASSOC)) { 
            echo '<option value="' . $row1['site_no'] . '">' . $row1['status'] . '</option>';
        }
      ?>
    </select>
        
    <label for="unnoId">UN No:</label>
    <select id="unnoId">
      <option value="0">Please select</option>
      <?php
        while($row = $result->fetch(PDO::FETCH_ASSOC)) { 
            echo '<option value="' . $row['c_id'] . '">' . $row['unno'] . '</option>';
        }
      ?>
<? var_dump($siteno); ?>
    </select>
    <div>
      <label for="element_4_1">Site No:  </label>
      <input id="element_4_1" name="element_4_1" class="element text large" type="text">
      <span class="floatLeft">
        <label for="element_4_3">Status:</label>
        <input id="element_4_3" name="element_4_3" class="element text medium" style="width:14em" type="text">
      </span>
    <div>
      <label for="element_5_1">UN No:  </label>
      <input id="element_5_1" name="element_5_1" class="element text large" type="text">
      <span class="floatLeft">
        <label for="element_5_3">Product:</label>
        <input id="element_5_3" name="element_5_3" class="element text medium" style="width:14em" type="text">
      </span>
    </div>
<input type="submit">
</form>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script>
      function insertResults(json){
        $("#element_4_1").val(json["site_no"]);
        $("#element_4_3").val(json["status"]);
        
        $("#element_5_1").val(json["unno"]);
        $("#element_5_3").val(json["name"]);
      }

      function clearForm(){
        $("#element_4_1, #element_4_3, #element_5_1, #element_5_3").val("");
      }

      function makeAjaxRequest(siteno){
        $.ajax({
          type: "POST",
          data: { siteno: siteno },
          dataType: "json", 
          url: "product-process_ajax1.php",
          success: function(json) {
            insertResults(json);
          }
        });
      }

      function makeAjaxRequest(unnoid){
        $.ajax({
          type: "POST",
          data: { unnoid: unnoid },
          dataType: "json", 
          url: "product-process_ajax1.php",
          success: function(json) {
            insertResults(json);
          }
        });
      }

      $("#siteNo").on("change", function(){
        var site_no = $(this).val();
        if (site_no === "0"){
          clearForm();
        } else {
          makeAjaxRequest(site_no);
        }
      });

      $("#unnoId").on("change", function(){
        var c_id = $(this).val();
        if (c_id === "0"){
          clearForm();
        } else {
          makeAjaxRequest(c_id);
        }
      });
    </script>

var_dump($_POST);
  </body>
</html>