Populating drop-down based on previous selection

Question

I have MySQL tables looking like that

regions table

id     |   region
-------------------
1      |   Region1
2      |   Region2

...

and schools table

id     |   school
-------------------
1      |   schno1
1      |   schno5
1      |   schno6
2      |   scho120

There are multiple select (dropdown) menus in my registration form. The regions dropdown looks like that

<select name="region">
<option value="0">Select the region</option>
<?php
$result=$db->query("SELECT * FROM regions");
while($row=$result->fetch_array(MYSQLI_BOTH))
{
    echo '<option value="'.$row[0].'">'.$row[1].'</option>';
    }
?>
</select>

What i want to do is, get "regions" id, then populate schools dropdown menu based on id (id of previous selection) from "schools" table on the fly. I'm newbie to js. Please help me to fix it. Thx in advance.

Answer 1

1) Make use of jQuery change event (bind to region select)
2) Call your php script, where you should provide the schools data based on region id
3) Attach the data to school select

There are plenty of examples on www - http://remysharp.com/2007/01/20/auto-populating-select-boxes-using-jquery-ajax/

Answer 2

$region = mysql_real_escape_string($_POST['region']);
$query = "SELECT s.school FROM regions r 
          INNER JOIN schools s ON (s.region_id = r.id) 
          WHERE r.region LIKE '$region' ";  <<-- LIKE is case insensitive, '=' is NOT 
$result = $db->query($query);
if not($result) then { die("error"); }
while($row=$result->fetch_array(MYSQLI_BOTH))
{
  echo '<option value="'.$row[0].'">'.$row[1].'</option>';
}