How do I fetch data from table into dropdown in mysql

[adebayo23]

How do I fetch data from table into dropdown menu in mysql and post back to another table? What I want is to create a registration form for customers and attach an agent to each customer, the agent names are stored in a table and i need to post agent name back when am sumitting the customers details.

see code below.

<?php


if(isset($_POST['submit'])){
 $agent_dat1=$_POST['Company_Name'];
 $agent_dat2=$_POST['First_Name'];
 $agent_dat3=$_POST['Other_Name'];
 $agent_dat4=$_POST['Software'];
 $agent_dat5=$_POST['Version'];
 $agent_dat6=$_POST['Agent'];
 $agent_dat7=$_POST['City'];
 $agent_dat8=$_POST['State'];
 $agent_dat9=$_POST['Agent_Email'];
 $agent_dat10=$_POST['Agent_Phone'];




 $insert= "INSERT INTO `capdata`.`agent`(`Company_Name`,`First_Name`,`Other_Name`,`Software`,`Version`,`Agent`,`City`,`State`,`Agent_Email`,`Agent_Phone`) VALUES ('$agent_dat1','$agent_dat2','$agent_dat3','$agent_dat4','$agent_dat5','$agent_dat6','$agent_dat7','$agent_dat8','$agent_dat9','$agent_dat10');";

 $result = mysql_query($insert, $connection);
 // execute the SQL statement
 if ($result) {
 echo "Congratulations <b> $_POST[Company_Name] </b> your Registration is successful!";
 } else {
 echo "Sorry! User Registration Fail";
 }
}



 ?>

Please i would appreciate a little help here.

[cereal]

Hi, let me understand: do you want an autocomplete function over an input text field? Or a select dropdown? Also, can you show the form?

[adebayo23]

`

<table width="700" border="0" align="center" cellpadding="5" cellspacing="5">
 <form action="" method="post" id="user_reg_form" onsubmit="return Validate();">
 <tr>
    <td colspan="3" align="left" class="header_red">Register Agent</td>
    </tr>
  <tr>
        <td width="190">&nbsp;</td>
        <td colspan="2"><?php


if(isset($_POST['submit'])){
 $agent_dat1=$_POST['Company_Name'];
 $agent_dat2=$_POST['First_Name'];
 $agent_dat3=$_POST['Other_Name'];
 $agent_dat4=$_POST['Software'];
 $agent_dat5=$_POST['Version'];
 $agent_dat6=$_POST['Agent_Address'];
 $agent_dat7=$_POST['City'];
 $agent_dat8=$_POST['State'];
 $agent_dat9=$_POST['Agent_Email'];
 $agent_dat10=$_POST['Agent_Phone'];




 $insert= "INSERT INTO `capdata`.`agent`(`Company_Name`,`First_Name`,`Other_Name`,`Software`,`Version`,`Agent_Address`,`City`,`State`,`Agent_Email`,`Agent_Phone`) VALUES ('$agent_dat1','$agent_dat2','$agent_dat3','$agent_dat4','$agent_dat5','$agent_dat6','$agent_dat7','$agent_dat8','$agent_dat9','$agent_dat10');";

 $result = mysql_query($insert, $connection);
 // execute the SQL statement
 if ($result) {
 echo "Congratulations <b> $_POST[Company_Name] </b> your Registration is successful!";
 } else {
 echo "Sorry! User Registration Fail";
 }
}



 ?>


</td>
        </tr>
  <tr>
    <td>First Name:</td>
    <td colspan="2"><input name="First_Name" type="text" id="First_Name" size="40" maxlength="40" /></td>
  </tr>
  <tr>
        <td>Other Names:</td>
        <td colspan="2"><input name="Other_Name" type="text" id="Other_Name" size="40" maxlength="40" /></td>
    </tr>
  <tr>
    <td>Company:</td>
    <td colspan="2"><input name="Company_Name" type="text" id="Company_Name" size="40" maxlength="40" /></td>
  </tr>
  <tr>
    <td>Software/Version:</td>
    <td width="181"><label for="Software">
      <select name="Software" id="Software">
        <option value="#">-Select Software-</option>
        <option value="retailpos">RetailPOS</option>
        <option value="respos">ResPOS</option>
        <option value="tvm">TVM</option>
        <option value="sims">SIMS</option>
      </select>
    </label></td>
    <td width="279"><select name="Version" id="Version">
      <option value="#">-Select Version-</option>
      <option value="2">V 2.0</option>
      <option value="3">V 3.0</option>
      <option value="4">V 4.0</option>
      <option value="5">V 5.0</option>
    </select></td>
  </tr>
  <tr>
    <td colspan="3" class="header_red">Contact Details</td>
  </tr>
  <tr>
    <td>Address</td>
    <td colspan="2"><input name="Agent_Address" type="text" id="Agent_Address" size="50"></td>
  </tr>
  <tr>
    <td>City</td>
    <td colspan="2"><input name="City" type="text" id="City" size="50"></td>
  </tr>
  <tr>
    <td>State</td>
    <td colspan="2"><select name="State" size="1" id="State">
      <option value="Abia" selected="selected">Abia</option>
      <option value="Adamawa">Adamawa</option>
      <option value="Awka-Ibom">Awka-Ibom</option>
      <option value="Anambra">Anambra</option>
      <option value="Bauchi">Bauchi</option>
      <option value="Bayelsa">Bayelsa</option>
      <option value="Benue">Benue</option>
      <option value="Borno">Borno</option>
      <option value="Cross-River">Cross-River</option>
      <option value="Delta">Delta</option>
      <option value="Ebonyi">Ebonyi</option>
      <option value="Edo">Edo</option>
      <option value="Ekiti">Ekiti</option>
      <option value="Enugu">Enugu</option>
      <option value="Gombe">Gombe</option>
      <option value="Imo">Imo</option>
      <option value="Jigawa">Jigawa</option>
      <option value="Kaduna">Kaduna</option>
      <option value="Kano">Kano</option>
      <option value="Kastina">Kastina</option>
      <option value="Kebbi">Kebbi</option>
      <option value="Kogi">Kogi</option>
      <option value="Kwara">Kwara</option>
      <option value="Lagos">Lagos</option>
      <option value="Nasarawa">Nasarawa</option>
      <option value="Niger">Niger</option>
      <option value="Ogun">Ogun</option>
      <option value="Osun">Osun</option>
      <option value="Oyo">Oyo</option>
      <option value="Plateau">Plateau</option>
      <option value="Rivers">Rivers</option>
      <option value="Sokoto">Sokoto</option>
      <option value="Taraba">Taraba</option>
      <option value="Yobe">Yobe</option>
      <option value="Zamfara">Zamfara</option>
    </select></td>
  </tr>
  <tr>
        <td> Email:</td>
        <td colspan="2"><input name="Agent_Email" type="text" id="Agent_Email" size="40" maxlength="40" /></td>
        </tr>
  <tr>
        <td>Phone Number:</td>
        <td colspan="2"><input name="Agent_Phone" type="text" id="Agent_Phone" size="40" maxlength="40" /></td>
        </tr>
  <tr>
    <td>&nbsp;</td>
    <td colspan="2"><input name="submit" type="submit" value="Register" /> 
      <input name="preview" type="button" id="preview" onclick="return confirm('You may loose all data, are you sure you want to reset all');" value="Preview"  /> 
      <input name="reset" type="reset" id="reset" onclick="return confirm('You may loose all data, are you sure you want to reset all');" value="Reset"  /> <a href="includes/login1.php"></a></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td colspan="2">&nbsp;</td>
  </tr>

  <tr>
    <td>&nbsp;</td>
    <td colspan="2">&nbsp;</td>
  </tr>
   </form>
</table>

`

[mexabet]

You have to use the foreach statement to populate the form via autocomplete. Eg:

<?php
                    foreach ($stocks as $symbol)
                    {
                        printf("<option value='$symbol'>" . $symbol . "</option>");
                    }
                ?>

[wikit]

Presuming this is the connect-mysql.php

<?php

DEFINE ('DB_HOST', 'ip');
DEFINE ('DB_USER', 'user');
DEFINE ('DB_PSWD', 'pass');
DEFINE ('DB_NAME', 'dbname');

$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PSWD, DB_NAME);

if (!$dbcon) {
    die('error connecting to database');
    }

?>

and this is the samepage.php

<form method="post" action="samepage.php">

<select size="1" name="whatever">
<?php
include('pathtoconnection/connect-mysql.php');

$sql3="SELECT * FROM table";
$results = mysqli_query($dbcon,$sql3) or die('123');

while($row = mysqli_fetch_array($results)) {

      echo '<option value="'.$row['rowname'].'">'.htmlspecialchars($row['rowname']).'</option>'; 
  }                         
?>
                    </select>
<input type="submit" value="Insert Info"></p>
<input type="hidden" name="submitted" value="true" />
</form>


<?php

if (isset($_POST['submitted'])) {
include('pathtoconnection/connect-mysql.php');

    $whatever= mysqli_real_escape_string($dbcon, $_POST['whatever']);

    $sqlinsert = "INSERT INTO table2 (whatever) VALUES ('$whatever')";


    if (!mysqli_query($dbcon, $sqlinsert)) {
        die('error inserting new record');
        }//end of nested if statement
$newrecord = "Entry Successful";

} //end of main if
?>

<?php
echo $newrecord // New record added statement added at the top
?>

you can do that with multiple drop menus. UPDATING is slightly a different story but this would at least let you insert new records.