Postgresql database with web access- code correctly updates database but web page perpetually loads

I have a postgresql database for a hypothetical zoo. I am creating web access for my database using php and some javascript. I have successfully gotten the majority of the web pages to work, but am now working on a page that allows the client to add and remove animals from current exhibits. I have a dropdown that is populated with the exhibit names from the database, the second dropdown is populated from the database with animal names and their IDs that are assigned to the current exhibit selected(animal_id is foreign key in animal table referencing exhibit_id in exhibit table). This is dynamically changed when the exhibit name is selected. I have a third dropdown that is populated from the database with animal names and their ID that are not assigned to an exhibit. This all works upon initial loading of the page. Upon clicking the add or remove button my database is updated correctly, but the page just keeps loading. I was expecting it to give the success message and then the client could pick another exhibit and it would show the updates, but it doesn't get there. I've been teaching myself HTML, PHP, and JS so the code is pretty sloppy. I'm using some mix of examples I found on the web to get the dynamic dropdowns and ability to select multiple options from the dropdown lists so this is probably where the issue lies since I can exit the page and go back and then it will have the dropdowns with the values they should have. I would appreciate any help on why this is happening and if there are any fixes. Thanks!

<?php
  //Read database info from file and assgin to variables
  $myfile = fopen("../pg_connection_info.txt", "r") or die("Unable to open \"../pg_connection_info.txt\" file!");
  $my_host = fgets($myfile);
  $my_dbname = fgets($myfile);
  $my_user = fgets($myfile);
  $my_password = fgets($myfile);
  fclose($myfile);

  // Make a connection to the database
  $dbhost = pg_connect("host=$my_host dbname=$my_dbname user=$my_user password=$my_password");

  // If the $dbhost variable is not defined, there was an error
  if(!$dbhost)
  {
    die("Error: ".pg_last_error());
  }

  //Get exhibits from database
  $query = "SELECT exhibit_id, name FROM exhibit";
  $result = pg_query($dbhost, $query);
  
  while($row = pg_fetch_row($result))
  {
    $categories[] = array("id" => $row[0], "val" => $row[1]);
  }

  //Get animals assigned to exhibits
  $query2 = "SELECT animal_id, exhibit_id, name FROM animal";
  $result2 = pg_query($dbhost, $query2);

  while($row = pg_fetch_row($result2))
  {
    $subcats[$row[1]][] = array("id" => $row[0], "val" => $row[2]);
  }

  $jsonCats = json_encode($categories);
  $jsonSubCats = json_encode($subcats);
?>


<html lang="en-us">
 <head>
   <title>Manage Animals/Exhibits</title>
   <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script type='text/javascript'>
      <?php
        echo "var categories = $jsonCats; \n";
        echo "var subcats = $jsonSubCats; \n";
      ?>
      //exhibit dropdown options
      function loadCategories(){
        var select = document.getElementById("exhibit");
        select.onchange = updateSubCats;
        var j = 0;
        select.options[0] = new Option("--Select an option--"); 
        for(var i = 0; i < categories.length; i++){
          select.options[j + 1] = new Option(categories[i].val,categories[i].id);   
          j++;       
        }
      }
      //animals assigned to exhibits dropdown options
      function updateSubCats(){
        var catSelect = this;
        var catid = this.value;
        var subcatSelect = document.getElementById("animal");
        subcatSelect.options.length = 0; //delete all options if any present
        for(var i = 0; i < subcats[catid].length; i++){
          subcatSelect.options[i] = new Option(subcats[catid][i].val + " - " + subcats[catid][i].id ,subcats[catid][i].id);
        }
      }

     //Allows multiple selecting of dropdown items
     window.onmousedown = function(e)
    {
      var el = e.target;
      if(el.tagName.toLowerCase() == 'option' && el.parentNode.hasAttribute('multiple'))
      {
        e.preventDefault();
        if(el.hasAttribute('selected')) el.removeAttribute('selected');
        else el.setAttribute('selected', '');
      }
    }
   </script>

</head>
<body onload='loadCategories()'>
<h1>Add and Remove Animals from Exhibits</h1>
<form action="Manage_Animal_Exhibit.php" method="post">
<p>Select an exhibit to add or remove animal(s) from</p>
 </select>
  Exhibit: <select name="exhibit" id="exhibit">
  </select><br><br> 
  <p>Current animals in exhibit:</p> 
  <select name="animal[]" id='animal' multiple>
  </select><br><br>
  <input type="submit" name="Remove" value="Remove"/><br><br>
  <p>Current animals not assigned to an exhibit:</p>
  <select name="animalAvail[]" id='animalAvail' multiple>
<?php
//get animals not in exhibit
 $query3 = "SELECT name, animal_id FROM animal WHERE exhibit_id is NULL";
 $result3 = pg_query($dbhost, $query3);
  while($row = pg_fetch_row($result3)){
      $name = $row[0]; 
      $id = $row[1];
      //Display animal's name and id in dropwdown. Assign id to the option so no need for an associative array
      echo "<option value='$id'>$name - $id </option>";
  }
?>
  </select><br><br>
  <input type="submit" name="Add" value="Add"/><br><br>
  </form>

<!--Exits the Manage_Animal_Exhibit page and returns to the Home Page-->
  <form action="Home_Page.php" method="post">
    <input type="submit" name="Exit" value="Exit"/>
  </form>


<?php
//When add button is pressed assign animals to exhibit
if(isset($_POST["Add"]))
{ 
  //If exhibit isn't selected display message
  if($_POST["exhibit"] == "--Select an option--")
  {
    echo "<script type='text/javascript'>alert('Select an exhibit')</script>";
  }
  else
  { 
    $arr = array();
    //Get each animal selected from dropdown and add their ID to an array 
    foreach($_POST["animalAvail"] as $animalID)
    {
       array_push($arr, "$animalID");
    } 

    //Get id of exhibit selected and then add animals to exhibit
    $exhibitID =  $_POST["exhibit"];
    $query4 = "UPDATE Animal SET exhibit_id = $1 WHERE animal_id = $2";
    pg_prepare($dbhost, "prepare1", $query4);
    for($i = 0; i < count($arr); $i++)
    { 
      $idToUpdate = $arr[$i];
      pg_execute($dbhost, "prepare1", array($exhibitID, $idToUpdate));
    }
      echo "<script type='text/javascript'>alert('The animals were added to the exhibit')</script>";
  }
}


if(isset($_POST["Remove"]))
{
  //If exhibit isn't selected display message
  if($_POST["exhibit"] == "--Select an option--")
  {
    echo "<script type='text/javascript'>alert('Select an exhibit')</script>";
  }
  else
  { 
    $arr2 = array();
    //Get each animal selected from dropdown and add their ID to an array 
    foreach($_POST["animal"] as $aID)
    {
       array_push($arr2, "$aID");
    }      
    $query5 = "UPDATE Animal SET exhibit_id = NULL WHERE animal_id = $1";
    pg_prepare($dbhost, "prepare2", $query5);
    for($i = 0; i < count($arr2); $i++)
    { 
      $idUpdate = $arr2[$i];
      pg_execute($dbhost, "prepare2", array($idUpdate));
    }
    echo "<script type='text/javascript'>alert('The animals were removed from the exhibit')</script>";
  }
}
// Free the result from memory
pg_free_result($result);

// Close the database connection
pg_close($dbhost);
?>

</body>
</html>


Read more here: https://stackoverflow.com/questions/64961668/postgresql-database-with-web-access-code-correctly-updates-database-but-web-pag

Content Attribution

This content was originally published by ajg at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: