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