如何填充了一系列的链式选择框的SQL查询的结果呢？ [英] How to populate sql query result in a series of chained select boxes?

问题描述

我有这四个表（这是分层次的，同时）在我的数据库：

表1：状态

  + ---------- + ------------- +
| STATE_ID | STATE_NAME |
+ ---------- + ------------- +
| 1 |马哈拉施特拉邦|
| 2 |果阿|
+ ---------- + ------------- +
 

表2：地区

  + ------------- + -------- + ------- --- +
| district_id | district_name | STATE_ID |
+ ------------- + -------- + ---------- +
| 1 |孟买| 1 |
| 2 |浦那| 1 |
| 3 |纳西克| 1 |
| 4 |奥兰加巴德| 1 |
| 5 |潘吉姆| 2 |
| 6 |丹德利| 2 |
| 7 |卡瓦尔| 2 |
+ ------------- + -------- + ---------- +
 

表3：Taluka

  + ----------- + ------------- + ----------- -  +
| taluka_id | taluka_name | district_id |
+ ----------- + ------------- + ------------- +
| 1 |阿里巴格| 1 |
| 2 |格利扬| 1 |
| 3 | Matheran的| 1 |
| 4 |瓦伊贾普尔| 4 |
| 5 |冈格阿普尔| 4 |
| 6 | Kannad | 4 |
| 7 |西洛德| 4 |
| 8 | Madgaon | 5 |
| 9 | Vengurle | 5 |
| 10 | Sawantwadi | 5 |
+ ----------- + ------------- + ------------- +
 

表4：村

  + ------------ + -------------- + --------- -  +
| village_id | village_name | taluka_id |
+ ------------ + -------------- + ----------- +
| 1 |的Ranjangaon | 5 |
| 2 | Wadgaon | 5 |
| 3 | Teesgaon | 5 |
+ ------------ + -------------- + ----------- +
 

我怎么能显示选择框而发生以下事件的动态加载：

• 从国家表状态名称显示选择框
  • 在这里， STATE_ID 作为价值＆LT;选项＆GT; 标记和 STATE_NAME 打开和关闭之间的文本＆LT;选项＆GT; 标签）
• 在从第一个选择框的状态选择任何一个国家，显示了由区表格相对于选择状态的选项从第一个选择框中地区名称新创建的选择框
  • 在这里， district_id 作为价值＆LT;选项＆GT; 标记和 district_name 打开和关闭之间的文本＆LT;选项＆GT; 标记
• 类似地创造Talukas下选择了区从第二个选择框，一个新的选择框。
• 再次，类似地根据所选Taluka从第三个选择框创建村一个新的选择框。

---

在上面的方式，通过选择一个接一个的值由新创建的选择框（我们可以称之为家长的孩子/根选择框）去的时候，我们终于也有四个选择框即。州，区，Taluka，村。

这一进程背后的目的是，我需要获得 village_id 的具体村与其他数据一起，喜欢它的 STATE_ID ， district_id 和 taluka_id 。

请提供一个易于使用和灵活的解决方案严格使用的jQuery + AJAX，MySQL和PHP

仅供参考，我已经成功地填充使用jQuery的Ajax方法到后改变选择框的值异步到一个包含查询PHP脚本选择框各国和地区。好了，这个工作，直到我得到了区第二个选择框。这对地区的选择框是由DOM操作创建的，而通过使用jQuery的服务器，所以我不能检查在区选择框的变化事件，以便POST它的选择价值通过AJAX到它的数据库查询特定区，并返回相对Talukas包含MySQL查询PHP脚本。可它用处理绑定，代理或生活事件处理程序，呵呵 - 我不知道！ ：P

---

好了，我的逻辑一定是完全错误的。只是忘记了，请给我一个完整的解决方案。我将学习从该解决方案很多东西。 感谢您阅读这一切厌恶，对了！ ;）

---

我创建了一个原型设计这一点。这将有助于描述可视化的方式的问题（或正是我想要的输出或解决方案来定）：

---

---

更新：工作演示code可以在这里找到：的https：/ /github.com/vishaltelangre/select-chain

解决方案

1. 在显示使用国家表中的下拉列表。 STATE_ID 将期权的价值和 STATE_NAME 将文本。
2. 当的onchange 事件触发这个下拉得到的值选择的选项的
3. 发送Ajax调用加载区的选中状态。该参数将是 STATE_ID
4. 在PHP中到底得到区与匹配 STATE_ID 中的所有行。返回它作为JSON响应
5. 当阿贾克斯获得响应，创建一个新的下拉或填充pviously创建了一个与这个新的结果$ P $。
6. 转到步骤1 区然后 Taluka 然后村

I have these four tables (which are hierarchical, meanwhile) in my database:

Table 1: State

+----------+-------------+
| state_id | state_name  |
+----------+-------------+
|        1 | Maharashtra |
|        2 | Goa         |
+----------+-------------+

Table 2: District

+-------------+---------------+----------+
| district_id | district_name | state_id |
+-------------+---------------+----------+
|           1 | Mumbai        |        1 |
|           2 | Pune          |        1 |
|           3 | Nashik        |        1 |
|           4 | Aurangabad    |        1 |
|           5 | Panjim        |        2 |
|           6 | Dandeli       |        2 |
|           7 | Karwar        |        2 |
+-------------+---------------+----------+

Table 3: Taluka

+-----------+-------------+-------------+
| taluka_id | taluka_name | district_id |
+-----------+-------------+-------------+
|         1 | Alibag      |           1 |
|         2 | Kalyan      |           1 |
|         3 | Matheran    |           1 |
|         4 | Vaijapur    |           4 |
|         5 | Gangapur    |           4 |
|         6 | Kannad      |           4 |
|         7 | Sillod      |           4 |
|         8 | Madgaon     |           5 |
|         9 | Vengurle    |           5 |
|        10 | Sawantwadi  |           5 |
+-----------+-------------+-------------+

Table 4: Village

+------------+--------------+-----------+
| village_id | village_name | taluka_id |
+------------+--------------+-----------+
|          1 | Ranjangaon   |         5 |
|          2 | Wadgaon      |         5 |
|          3 | Teesgaon     |         5 |
+------------+--------------+-----------+

How could I show select boxes dynamically loaded while happening of following events:

• Display select box for state names from "State" table
  • Here, state_id as value of <option> tag and state_name as text between opening and closing <option> tags)
• On selecting any of the state from first select box for states, display a newly created select box for district names from "District" table relative to selected state option from first select box
  • Here, district_id as value of <option> tag and district_name as text between opening and closing <option> tags
• Similary create a new select box for Talukas under selected District from second select box.
• Once again, similary create a new select box for Villages under selected Taluka from third select box.

---

In the above manner, when going through selecting one by one values from newly created select boxes (which we may call children of parent/root select boxes), we finally would have four select boxes for viz. State, District, Taluka, Village.

The aim behind this process is that I need to get the village_id of specific Village along with other data like it's state_id, district_id, and taluka_id.

Please provide an easy to use and flexible solution strictly using jQuery + AJAX, MySQL, PHP.

FYI, I have succeeded to populate select boxes for States and Districts using jQuery's ajax method to POST value of changed select box asynchronously to a query containing PHP script. Well, this worked until I get second select box for Districts. This select box for Districts is created by DOM manipulation rather by server using jQuery, so I couldn't check for change event on Districts' select box so as to POST it's selected value via AJAX to a MySQL query containing PHP script which queries Database for specific District and returns relative Talukas. Can it be handled using bind, delegate or live event handlers, huh--I don't know!!! :P

---

Well, my logic must be totally wrong. Just forget it, and please give me a complete solution. I will learn many things from that solution. Thank you for reading this all disgust, by the way! ;)

---

I have created a prototype design for this. This should help to describe the question in visual way (or exactly what I wanted as output or solution to be):

---

---

UPDATE: Working demo code is available here: https://github.com/vishaltelangre/select-chain

解决方案

1. Show a dropdown using State table. state_id will be option value and state_name will be text.
2. When onchange event fires on this dropdown get the value of selected option.
3. Send an Ajax call to load district for selected state. The parameter will be state_id
4. In the PHP end get all the rows from District that with matching state_id. Return it as JSON response
5. When Ajax get the response, create a new dropdown or populate previously created one with this new result.
6. Go to Step 1 for District then Taluka then Village

这篇关于如何填充了一系列的链式选择框的SQL查询的结果呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！