当用户在选择字段中选择新选项时，如何运行mysql查询？ [英] How can I run a mysql query when the user selects an new option in a select field?
本文介绍了当用户在选择字段中选择新选项时，如何运行mysql查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想要一个列出产品类别的选择框。选择类别时，我要同时从数据库中选择该类别中的产品。我需要使用AJAX与这个应用程序吗？任何例子，这样做？这是我使用的代码：
这些函数构建每个选择字段的选项。
function buildCategoryOptions（$ catId = 0）
{
$ sql =SELECT cat_id，cat_parent_id，cat_name
FROM tbl_category
ORDER BY cat_id ;
$ result = dbQuery（$ sql）或die（'Can not get Product。'。mysql_error（））;
$ categories = array（）;
while（$ row = dbFetchArray（$ result））{
list（$ id，$ parentId，$ name）= $ row;
if（$ parentId == 0）{
//我们为每个顶级类别创建一个新数组
$ categories [$ id] = array（'name' > $ name，'children'=> array（））;
} else {
//子类别被放在父类别的数组中
$ categories [$ parentId] ['children'] [] = array（'id'=> $ id，'name'=>
$ name）;
}
}
//构建组合框选项
$ list ='';
foreach（$ categories as $ key => $ value）{
$ name = $ value ['name'];
$ children = $ value ['children'];
$ list。=< option value = \$ key\;
if（$ key == $ catId）{
$ list。=selected;
}
$ list。=> $ name< / option> \r\\\
;
foreach（$ children as $ child）{
$ list。=< option value = \{$ child ['id']} \;
if（$ child ['id'] == $ catId）{
$ list。=selected;
}
$ list。=>& nbsp;& nbsp; {$ child ['name']}< / option> \r\\\
}
}
return $ list;
}
/ *
构建收音机选项的产品选项列表
* /
function buildProductOptions（$ catId = 0）
{
$ sql = SELECT pd_id，pd_name，cat_id
FROM tbl_product
WHERE cat_id = $ catId
ORDER BY pd_name;
$ result = dbQuery（$ sql）或die（'Can not get Product。'。mysql_error（））;
$ numProduct = dbNumRows（$ result）;
$ products = array（）？
while（$ row = dbFetchArray（$ result））{
list（$ id，$ name）= $ row;
//为每个顶级类别创建一个新数组
$ products [$ id] = array（'name'=> $ name）;
}
//构建组合框选项
$ list ='';
foreach（$ products as $ key => $ value）{
$ name = $ value ['name'];
$ list。=< option value = \$ key\;
$ list。=> $ name< / option> \r\\\
;
}
return $ list;
}
选择字段：
$ catId =（isset（$ _ GET ['catId']）&& $ _GET [' catId']> 0）？ $ _GET ['catId']：0;
$ categoryList = buildCategoryOptions（$ catId）;
$ productList = buildProductOptions（$ catId）;
<！---类别选择--->
< select name =cboCategoryid =cboCategoryclass =box>
< option value =selected> - 选择类别 - < / option>
<？php
echo $ categoryList;
？>
< / select>
<！---产品选择：类别更改产品需要从选定的cat -
- >
< select name =selectOptionsid =selectOptionsclass =boxmultiple =multiple>
< option> - 选择其他选项 - < / option>
<？php
echo $ productList;
？>
< / select>
解决方案
是的，你需要在这里使用ajax。检查以下代码和注释。
编写返回 ActiveXObject（）的函数，
function getXMLHTTP（）{
var xmlhttp = false;
try {
xmlhttp = new XMLHttpRequest（）;
} catch（e）{
try {
xmlhttp = new XMLHttpRequest（）;
} catch（e）{
try {
xmlhttp = new ActiveXObject（Microsoft.XMLHTTP）;
} catch（e）{
try {
xmlhttp = new ActiveXObject（Msxml2.XMLHTTP）;
} catch（e1）{
xmlhttp = false;
}
}
}
}
返回xmlhttp;
}
然后写一个特定于您的网站的函数， / p>
function getProducts（）{
var select1 = document.getElementById（cboCategory）;
var strURL =getproducts.php？city =+ select1.options [select1.selectedIndex] .value;
var req = getXMLHTTP（）; //函数获取xmlhttp对象
if（req）{
req.onreadystatechange = function（）{
if（req.readyState == 4）{//从服务器检索数据
if（req.status == 200）{// which reprents ok status
document.getElementById（'productsdiv'）。innerHTML = req.responseText; // div要更新
} else {
alert（[GET Products]使用XMLHTTP时出现问题：\\\
+ req.statusText）;
}
}
};
req.open（GET，strURL，true）; //使用get方法打开url
req.send（null）;
}
}
此函数将在 cboCategory 选择选项的更改事件上调用，因此相应的html将为
< select onchange =getProducts（）id =cboCategoryclass =box>
...
< / select>
<！ - 可以在同一页上的任何位置 - >
< div id =productdiv> < / div>
您的getproduct.php页面将返回一个html作为字符串，将覆盖<$
您也可以从php返回数据 json 。检查它的标签wiki了解更多信息。此外，您还可以使用 jquery 来执行ajax呼叫。 / p>
I want to have a select box that list categories of products. When a category is selected I want to simultaneously select the products in that category from the database. Do I need to use AJAX with this application? Any examples about doing this? Here is the code I'm working with:
These functions build the options of each select field.
function buildCategoryOptions($catId = 0)
{
$sql = "SELECT cat_id, cat_parent_id, cat_name
FROM tbl_category
ORDER BY cat_id";
$result = dbQuery($sql) or die('Cannot get Product. ' . mysql_error());
$categories = array();
while($row = dbFetchArray($result)) {
list($id, $parentId, $name) = $row;
if ($parentId == 0) {
// we create a new array for each top level categories
$categories[$id] = array('name' => $name, 'children' => array());
} else {
// the child categories are put int the parent category's array
$categories[$parentId]['children'][] = array('id' => $id, 'name' =>
$name);
}
}
// build combo box options
$list = '';
foreach ($categories as $key => $value) {
$name = $value['name'];
$children = $value['children'];
$list .= "<option value=\"$key\"";
if ($key == $catId) {
$list.= " selected";
}
$list .= ">$name</option>\r\n";
foreach ($children as $child) {
$list .= "<option value=\"{$child['id']}\"";
if ($child['id'] == $catId) {
$list.= " selected";
}
$list .= "> {$child['name']}</option>\r\n";
}
}
return $list;
}
/* Build the product options list for the radio options */
function buildProductOptions($catId = 0)
{
$sql = "SELECT pd_id, pd_name, cat_id
FROM tbl_product
WHERE cat_id = $catId
ORDER BY pd_name";
$result = dbQuery($sql) or die('Cannot get Product. ' . mysql_error());
$numProduct = dbNumRows($result);
$products = array();
while($row = dbFetchArray($result)) {
list($id, $name) = $row;
// we create a new array for each top level categories
$products[$id] = array('name' => $name);
}
// build combo box options
$list = '';
foreach ($products as $key => $value) {
$name = $value['name'];
$list .= "<option value=\"$key\"";
$list .= ">$name</option>\r\n";
}
return $list;
}
This is the page of the select fields:
$catId = (isset($_GET['catId']) && $_GET['catId'] > 0) ? $_GET['catId'] : 0;
$categoryList = buildCategoryOptions($catId);
$productList = buildProductOptions($catId);
<!--- Category Select --->
<select name="cboCategory" id="cboCategory" class="box">
<option value="" selected>-- Choose Category --</option>
<?php
echo $categoryList;
?>
</select>
<!--- Products Select : category is changed the products need to be from selected cat -
-->
<select name="selectOptions" id="selectOptions" class="box" multiple="multiple" >
<option>--Pick the other options--</option>
<?php
echo $productList;
?>
</select>
解决方案
Yes you do need to use ajax here. Check following code and notes.
Write the function that returns a ActiveXObject() which would do a ajax call as
function getXMLHTTP() {
var xmlhttp = false;
try {
xmlhttp = new XMLHttpRequest();
} catch (e) {
try {
xmlhttp = new XMLHttpRequest();
} catch (e) {
try {
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
} catch (e) {
try {
xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
} catch (e1) {
xmlhttp = false;
}
}
}
}
return xmlhttp;
}
Then write a function specific to your site that would get the desired data as
function getProducts(){
var select1 = document.getElementById("cboCategory");
var strURL = "getproducts.php?city="+select1.options[select1.selectedIndex].value;
var req = getXMLHTTP(); // function to get xmlhttp object
if (req) {
req.onreadystatechange = function() {
if (req.readyState == 4) { // data is retrieved from server
if (req.status == 200) { // which reprents ok status
document.getElementById('productsdiv').innerHTML = req.responseText; // div to be updated
} else {
alert("[GET Products]There was a problem while using XMLHTTP:\n" + req.statusText);
}
}
};
req.open("GET", strURL, true); // open url using get method
req.send(null);
}
}
This function would be called on change event of the cboCategory select options, so the corresponding html would be
<select onchange="getProducts()" id="cboCategory" class="box">
...
</select>
<!-- Can be anywhere on same page -->
<div id="productdiv"> </div>
Your getproduct.php page would return a html as string that would over-write the contents of producstdiv tag in your html page.
You can also return data from php as json. Check it's tag wiki for more info. Also you can use jquery to do ajax call.
这篇关于当用户在选择字段中选择新选项时，如何运行mysql查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
