Sending dynamic select form to a MySQL database using POST

I am trying to u开发者_如何学Pythonse a dynamic select form to send information to a MySQL database. The user will be able to choose their school, and then select their major from within that school's list (all retrieved from a MySQL table). I then want to send that information to a different table in the database to be stored.

This is what I have for the code thus far:

<select name="school">
  <php
  $sql = "SELECT school_name, school_id FROM school_table ORDER BY school_name";
  $query = mysql_query($sql,$conn);

  while($row = mysql_fetch_array($states))
    {
  echo ("<option value=$row[school_id]>$row[school_name]</option>");        
    }
  ?>
</select>

I don't know how to make the second select, which would ideally recognize the school_id from the first table and match it with the corresponding school_id on the second table, which also lists the majors at that school. Also, I don't know how to send the form when it is finally done to a MySQL table.

---

You could either use a simple form to submit the value from the combobox to the server (as HTTP POST or HTTP GET) and use the value as a variable in you SQL statement or you could use a simple AJAX request to send the necessary information to your php script. Anyway, your serverside code should look like this:

//process.php
$myRetrievedValue = $_POST["school"];
$mySqlStm = "SELECT * FROM foo WHERE bar = '".mysql_escape_string($myRetrivedValue)."'";

On the client side you code could look like this (using a simple form and no AJAX stuff):

<form action="process.php" method="post">
<select name="school">  
<php  $sql = "SELECT school_name, school_id FROM school_table ORDER BY school_name";  
      $query = mysql_query($sql,$conn);  while($row = mysql_fetch_array($states))    {  
      echo ("<option value=$row[school_id]>$row[school_name]</option>");            }  ?>
</select>
<input name="" type="submit" />
</form>

Please remember: Whenever you use a user input in you query use prepared statements (or at least escape methods as above) to avoid SQL injections.

---

answer is to select from both tables in one SELECT using joins: http://dev.mysql.com/doc/refman/5.0/en/join.html

 INNER JOIN

     SELECT `school_table`.`school_name`,
            `school_table`.`school_id`,
            `2ndTable`.`school_id`,
            `2ndTable`.`major`,
                FROM school_table,2ndTable 
                WHERE `school_table`.`school_id`=`2ndtable`.`school_id`
                ORDER BY school_name

 or a 
 LEFT JOIN (returning all columns in the left)

     SELECT `school_table`.`school_name`,
            `school_table`.`school_id`,
            `2ndTable`.`major`,
            `2ndTable`.`school_id`
                FROM school_table
                LEFT JOIN on `school_table`.`school_id`=`2ndtable`.`school_id`
                ORDER BY school_name