Jump to content

Populate MySQL into a dropdown - then use it back in a update query.

andrewgarn

By andrewgarn
in PHP Coding Help

Recommended Posts

andrewgarn Member

andrewgarn

Posted
Posted

Right, so currently my page loads fields from a mysql table allows the user to edit them and then they can save the changes back into the database.

 

But one of those fields is a foreign key, so I want them to only select the values currently in the other table. I think this would be done via a drop down box.

 

However i'm not sure how to do it...here is an image of what the page looks like currently, and circled is the field in the form I wish to change into a drop down box.

 

page1wn1.png

 

Here is the relevant code. The query used to retrieve all the owners names is at the bottom of the code and can be seen on the image.

 

  $certif = $_GET["certid"];
      //$owneridif = $_GET["ownerid"];
  $sql = "select * from boat WHERE certid='$certif'";
      //echo $sql;
  $result = mysql_query($sql);        
      $myrow = mysql_fetch_array($result);
  $localownerid = $myrow["ownerid"];
  $sql2 = "SELECT forename, surname FROM owner WHERE ownerid='$localownerid'";
  $result2 = mysql_query($sql2);
  $myrow2 = mysql_fetch_array($result2); 	  	  	  	  
  ?>
  
      <form action="editboat.php" method="post">
      <input type=hidden name="certid" value="<?php echo $myrow["certid"] ?>">
   
      Hull Number: <INPUT TYPE="TEXT" NAME="hullnumber" VALUE="<?php echo $myrow["hullnumber"] ?>" SIZE=10><br>
      Certificate Number: <INPUT TYPE="TEXT" NAME="certid" VALUE="<?php echo $myrow["certid"] ?>" SIZE=15><br>
      Owner's ID: <INPUT TYPE="TEXT" NAME="ownerid" VALUE="<?php echo $myrow["ownerid"] ?>" SIZE=10> - (<?php echo $myrow2["forename"] ?> <?php echo $myrow2["surname"] ?>) See the table below for ID's<br>
      Design: <INPUT TYPE="TEXT" NAME="design" VALUE="<?php echo $myrow["design"] ?>" SIZE=45><br>
      Designer: <INPUT TYPE="TEXT" NAME="designer" VALUE="<?php echo $myrow["designer"] ?>" SIZE=45><br>
      Measurement Date: <INPUT TYPE="TEXT" NAME="dateofmeasurement" VALUE="<?php echo $myrow["dateofmeasurement"] ?>" SIZE=12><br>
      Rigs: <INPUT TYPE="TEXT" NAME="rigs" VALUE="<?php echo $myrow["rigs"] ?>" SIZE=10><br>
      Length: <INPUT TYPE="TEXT" NAME="length" VALUE="<?php echo $myrow["length"] ?>" SIZE=10><br>
      Weight: <INPUT TYPE="TEXT" NAME="weight" VALUE="<?php echo $myrow["weight"] ?>" SIZE=10><br>
      Draught: <INPUT TYPE="TEXT" NAME="draught" VALUE="<?php echo $myrow["draught"] ?>" SIZE=10><br>
      Status: <INPUT TYPE="TEXT" NAME="certstatus" VALUE="<?php echo $myrow["certstatus"] ?>" SIZE=10><br>
      Registrar: <INPUT TYPE="TEXT" NAME="registrar" VALUE="<? echo $myrow["registrar"] ?>" SIZE=45><br>
      Measurer: <INPUT TYPE="TEXT" NAME="measurer" VALUE="<?php echo $myrow["measurer"] ?>" SIZE=45><br>
   
      <input type="hidden" name="cmd" value="edit">
      <p></p>
      <input type="submit" name="submit" value="Update Boat Record"> or 
      <? echo "<a href='database.php'>Cancel Edit</a>"; ?>
      </form>
      
      <?
  //display owner names with their id's
  echo "<h2>Owner Names and ID's</h2>";
  $listresults = mysql_query("SELECT * FROM owner"); 
   
   	  //run the while loop that grabs all the news scripts
      while($r=mysql_fetch_array($listresults)) 
      { 
      	//hull number and certid.
        $ownerid=$r["ownerid"];//take out the ownerid
        $forename=$r["forename"];//take out the forename
    $surname=$r["surname"];//take out the surname
     
    //echo name and id
    echo "$forename $surname ($ownerid)";
        echo "<br>";
  }
    //echo "<p></p>";
  ?>

 

So to summarise:

 

I want to:

 

<>Populate drop down box with results from mysql query.

<>It should be default select the one currently in use by that boat.

<>The user should then be able to select a value from drop down box (only options from the mysql table) and click submit to have their selection saved back into the database.

 

Thanks in advance for your help.

 

(if you require it here is the full code for this php file):

 

<?
// logincheck
session_start();
if (empty($_SESSION['username'])) {
echo "There is a problem with your login";
include "login.html"; 
} 
else {

//Database Information
$dbhost = "****";
$dbname = "*****";
$dbuser = "****";
$dbpass = "****";

//Connect to database
mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());

//If cmd has not been initialized
if(!isset($cmd))
{
   //echo '<p>Select Mode</p>';
   //display all the boat information
   $result = mysql_query("SELECT * FROM boat"); 
   
   //run the while loop that grabs all the boats
   echo "<h2>Edit a Boat</h2>";
   echo "<h4>Select a Hull Number to Edit a Boat</h4>";
   while($r=mysql_fetch_array($result)) 
   { 
      //hull number and certid.
      $hullnumber=$r["hullnumber"];//take out the hullnumber
      $certid=$r["certid"];//take out the certid
  $ownerid=$r["ownerid"];//take out userid
      //echo $ownerid;
 //make the title a link
  echo "<a href='editboat.php?cmd=edit&certid=$certid'>$hullnumber - Edit</a>";
      echo "<br>";
}
echo "<p></p>";
}
?>




<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      //echo '<p>Edit Mode</p>';
  $certif = $_GET["certid"];
      //$owneridif = $_GET["ownerid"];
  $sql = "select * from boat WHERE certid='$certif'";
      //echo $sql;
  $result = mysql_query($sql);        
      $myrow = mysql_fetch_array($result);
  $localownerid = $myrow["ownerid"];
  $sql2 = "SELECT forename, surname FROM owner WHERE ownerid='$localownerid'";
  $result2 = mysql_query($sql2);
  $myrow2 = mysql_fetch_array($result2); 	  	  	  	  
  ?>
  
      <form action="editboat.php" method="post">
      <input type=hidden name="certid" value="<?php echo $myrow["certid"] ?>">
   
      Hull Number: <INPUT TYPE="TEXT" NAME="hullnumber" VALUE="<?php echo $myrow["hullnumber"] ?>" SIZE=10><br>
      Certificate Number: <INPUT TYPE="TEXT" NAME="certid" VALUE="<?php echo $myrow["certid"] ?>" SIZE=15><br>
      Owner's ID: <INPUT TYPE="TEXT" NAME="ownerid" VALUE="<?php echo $myrow["ownerid"] ?>" SIZE=10> - (<?php echo $myrow2["forename"] ?> <?php echo $myrow2["surname"] ?>) See the table below for ID's<br>
      Design: <INPUT TYPE="TEXT" NAME="design" VALUE="<?php echo $myrow["design"] ?>" SIZE=45><br>
      Designer: <INPUT TYPE="TEXT" NAME="designer" VALUE="<?php echo $myrow["designer"] ?>" SIZE=45><br>
      Measurement Date: <INPUT TYPE="TEXT" NAME="dateofmeasurement" VALUE="<?php echo $myrow["dateofmeasurement"] ?>" SIZE=12><br>
      Rigs: <INPUT TYPE="TEXT" NAME="rigs" VALUE="<?php echo $myrow["rigs"] ?>" SIZE=10><br>
      Length: <INPUT TYPE="TEXT" NAME="length" VALUE="<?php echo $myrow["length"] ?>" SIZE=10><br>
      Weight: <INPUT TYPE="TEXT" NAME="weight" VALUE="<?php echo $myrow["weight"] ?>" SIZE=10><br>
      Draught: <INPUT TYPE="TEXT" NAME="draught" VALUE="<?php echo $myrow["draught"] ?>" SIZE=10><br>
      Status: <INPUT TYPE="TEXT" NAME="certstatus" VALUE="<?php echo $myrow["certstatus"] ?>" SIZE=10><br>
      Registrar: <INPUT TYPE="TEXT" NAME="registrar" VALUE="<? echo $myrow["registrar"] ?>" SIZE=45><br>
      Measurer: <INPUT TYPE="TEXT" NAME="measurer" VALUE="<?php echo $myrow["measurer"] ?>" SIZE=45><br>
   
      <input type="hidden" name="cmd" value="edit">
      <p></p>
      <input type="submit" name="submit" value="Update Boat Record"> or 
      <? echo "<a href='database.php'>Cancel Edit</a>"; ?>
      </form>
      
      <?
  //display owner names with their id's
  echo "<h2>Owner Names and ID's</h2>";
  $listresults = mysql_query("SELECT * FROM owner"); 
   
   	  //run the while loop that grabs all the news scripts
      while($r=mysql_fetch_array($listresults)) 
      { 
      	//hull number and certid.
        $ownerid=$r["ownerid"];//take out the ownerid
        $forename=$r["forename"];//take out the forename
    $surname=$r["surname"];//take out the surname
     
    //make the title a link
    echo "$forename $surname ($ownerid)";
        echo "<br>";
  }
    //echo "<p></p>";
  ?>
   
   <? } ?>
   
   
   <?
   if ($_POST["submit"])
   {
      //echo '<p>Submit Mode</p>';
  $hullnumber = $_POST["hullnumber"];
      $certid = $_POST["certid"];
      $ownerid = $_POST["ownerid"];
  $design = $_POST["design"];
  $designer = $_POST["designer"];
  $dateofmeasurement = $_POST["dateofmeasurement"];
  $rigs = $_POST["rigs"];
  $length = $_POST["length"];
  $weight = $_POST["weight"];
  $draught = $_POST["draught"];
  $certstatus = $_POST["certstatus"];
  $registrar = $_POST["registrar"];
  $measurer = $_POST["measurer"];
  	  
  echo $sql;

      $sql = "UPDATE boat SET hullnumber='$hullnumber',certid='$certid',ownerid='$ownerid', design='$design', designer='$designer', dateofmeasurement='$dateofmeasurement', rigs='$rigs', length='$length', weight='$weight', draught='$draught', certstatus='$certstatus', registrar='$registrar', measurer='$measurer' WHERE certid='$certid'";

//insert into boat (hullnumber, certid, ownerid, design, designer, dateofmeasurement, rigs, length, weight, draught, certstatus, registrar, measurer) values ('2', '2', '1', 'motor', 'Daivdson', '2008-01-12', '2', '25', '15', '40', 'Yes', 'ag605', 'tw106')

      $result = mysql_query($sql) or die(mysql_error());
      echo "Thank you! Information updated.";
  echo "<p><a href='database.php'>Return to Administration</a></p>";
   }
}
}
?>

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.