脚本宝典收集整理的这篇文章主要介绍了PHP- MySQL搜索标准，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。
<form name="frm" action="seArch_plants.PHP" method="get"> <table> <tr> <td width="155" height="24">Select Scientific Name:</td> <td width="275"> <select name="spnm" size="1"> <option value="0"> All</option> <option value="Abrus PRecatorius"> Abrus precatorius</option> <option value="Abutilon indicum"> Abutilon indicum</option> <option value="Abutilon theophrasti"> Abutilon theophrasti</option> </select> </td> </tr> <tr> <td>Select Family Name:</td> <td> <select name="fmnm" size="1"> <option value="0"> All</option> <option value="Fabaceae">Fabaceae</option> <option value="Malvaceae">;malvaceae</option> <option value="Mimosaceae">Mimosaceae</option> </select> </td> </tr> <tr> <td>Select Geographic Location:</td> <td> <select name="znm" size="1"> <option value="0"> All</option> <option value="North Gujarat">North Gujarat</option> <option value="South Gujarat">South Gujarat</option> <option value="South East Gujarat">South East Gujarat</option> <option value="Central Gujarat">Central Gujarat</option> <option value="Kachchh">Kachchh</option> <option value="Saurashtra">Saurashtra</option> </select> </td> </tr> <tr> <tr> <td>Select Marker:</td> <td> <select name="pnm" size="1"> <option value="0"> All</option> <option value="rbcL">rbcL</option> <option value="psbA-trnH">psbA-trnH</option> <option value="matK">matK</option> <option value="rpoC1">rpoC1</option> <option value="ITS2">ITS2</option> <option value="yCF5">ycf5</option> </select> </td> </tr> <tr> <td> <input tyPE="submit" value="Go" name="action"> <label> <input type="reset" name="Reset" id="button" value="Reset"> </label> </td> </tr> </table> </form>
表单操作页面如下,仅描绘整个编码的一部分.我想给用户一个选择任何/所有字段的选项,并能够通过从数据库中选择性搜索来显示它.请帮帮我.
if (($species == 0) || ($family == 0) || ($zones ==0 ) || ($marker == 0)) { if(($species==0) && ($family==0) && ($zones==0)) { $sql="SELECT * From `ncbi_bold_ plants` WHERE Marker LIKE '$marker%'" ; } elseif($species==0 && $family==0 && $marker==0) { $sql="SELECT * From `ncbi_bold_ plants` WHERE Zones LIKE '%$zones%'"; } elseif($family==0 && $zones==0 && $marker==0) { $sql="select * from `ncbi_bold_ plants` where Speciesname like '$species%'"; } elseif($species==0 && $zones==0 && $marker==0) { $sql="select * from `ncbi_bold_ plants` where Family like '$family%'"; } /* elseif($species==0 && $marker==0) { $sql="select * from `ncbi_bold_ plants` where Family like '$family%' AND Zones like '%$zones%'"; $search_sql=MysqL_query($sql) or trigger_error("There was an error.<br/>" . MysqL_error() . "<br />sql Was: {$sql}"); } elseif($family==0 && $zones==0 && $marker==0) { $sql="select * from `ncbi_bold_ plants` where Speciesname like '$species%'"; $search_sql=MysqL_query($sql) or trigger_error("There was an error.<br/>" . MysqL_error() . "<br />sql Was: {$sql}"); } elseif($species==0 && $family==0) { $sql="select * from `ncbi_bold_ plants` where Zones like '%$zones%' AND marker like '$marker%'"; $search_sql=MysqL_query($sql) or trigger_error("There was an error.<br/>" . MysqL_error() . "<br />sql Was: {$sql}"); } elseif($species==0 && $zones==0) { $sql="select * from `ncbi_bold_ plants` where Family like '$family%' AND marker like '$marker%'"; $search_sql=MysqL_query($sql) or trigger_error("There was an error.<br/>" . MysqL_error() . "<br />sql Was: {$sql}"); } elseif($species==0 && $marker==0) { $sql="select * from `ncbi_bold_ plants` where Family like '$family%' AND Zones like '%$zones%'"; $search_sql=MysqL_query($sql) or trigger_error("There was an error.<br/>" . MysqL_error() . "<br />sql Was: {$sql}"); } */ else { $sql="SELECT * FROM `ncbi_bold_ plants` " ; } } else { $sql="select * from `ncbi_bold_ plants` where Speciesname like '$species%' and Family like '$family%' and Zones like '%$zones%' and Marker like '$marker%'"; } $search_sql=MysqL_query($sql) or trigger_error("There was an error.<br/>" . MysqL_error() . "<br />sql Was: {$sql}");
请帮我一样.我无法在if语句中找到缺陷.
function create_query($p_species,$p_family,$p_zones,$p_marker) {
$options = array(
'Speciesname' => $p_species,'Family' => $p_family,'Zones' => $p_zones,'Marker' => $p_marker
);
$cond = '';
$noopt = true;
foreach ($options as $column => $value) {
if ($value !== 0) {
$noopt = false;
if ($cond != '') $cond .= ' AND ';
$cond .= "$column LIKE '%$value%'";
}
}
return $noopt ? false : "SELECT * FROM ncbi_bold_ plants WHERE $cond;";
}
如果所有参数都为零,则此函数返回false,否则返回查询字符串.
示例#1：
$species = 'spectest'; $family = 0; $zones = 'zonestest'; $marker = 0; echo create_query($species,$family,$zones,$marker);
输出：
SELECT * FROM ncbi_bold_ plants WHERE Speciesname LIKE '%spectest%' AND Zones LIKE '%zonestest%';
示例#2：
$species = 0; $family = 0; $zones = 0; $marker = 0; var_dump(create_query($species,$marker));
输出：
bool(false)
或者,如果要在没有条件而不是false的情况下返回查询字符串,只需将函数中的返回行替换为：
return $noopt ? "SELECT * FROM ncbi_bold_ plants;" : "SELECT * FROM ncbi_bold_ plants WHERE $cond;";
现在,如果你想用大量的IF,ELSEIF和ELSE语句来做这个,那就有解决方案：
(它的工作方式与上面的功能相同)：
if ($species == 0 && $family == 0 && $zones == 0 && $marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants";
}
elseif ($species == 0 && $family == 0 && $zones == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Marker LIKE '$marker%'";
}
elseif ($species == 0 && $family == 0 && $marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Zones LIKE '$zones%'";
}
elseif ($species == 0 && $zones == 0 && $marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%'";
}
elseif ($family == 0 && $zones == 0 && $marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%'";
}
elseif ($species == 0 && $family == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
}
elseif ($species == 0 && $zones == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Marker LIKE '$marker%'";
}
elseif ($species == 0 && $marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Zones LIKE '$zones%'";
}
elseif ($family == 0 && $zones == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Marker LIKE '$marker%'";
}
elseif ($family == 0 && $marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Zones LIKE '$zones%'";
}
elseif ($zones == 0 && $marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%'";
}
elseif ($species == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
}
elseif ($family == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
}
elseif ($zones == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Marker LIKE '$marker%'";
}
elseif ($marker == 0) {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Zones LIKE '$zones%'";
}
else {
$sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
}
// here you have your $sql ... Now do whatever you want with your query
echo $sql;
此代码已针对所有可能的情况进行了测试,因此如果它仍然不起作用,则问题不在上面发布的PHP示例中.我不推荐这种方式,因为这是完全错误的方法,即使对于初学者也是如此.
如果您仍想使用IF / ELSE / ELSEIF,请考虑检查IF变量IS NOT EQUAL为零的条件.您将获得更少的代码和更容易的实现.像这样的东西：
不同的方法
$species = 0; $family = 0; $zones = 0; $marker = 0; // default query w/out conditions $sql = "SELECT * FROM ncbi_bold_plants"; // conditions array $conditions = array(); // adding condition to array for every parameter <> 0 if ($species != 0) $conditions[] = "Speciesname LIKE '$species%'"; if ($family != 0) $conditions[] = "Family LIKE '$family%'"; if ($zones != 0) $conditions[] = "Zones LIKE '$zones%'"; if ($marker != 0) $conditions[] = "Marker LIKE '$marker%'"; // all we need Now is to concatenate array elements with " AND " glue $sql_cond = join(" AND ",$conditions); // last thing,adding condition(s) to the main query (if there's any) if ($sql_cond != '') $sql .= " WHERE $sql_cond"; // let see what we have Now echo $sql;
前4行仅用于测试.现在,我们有与之前相同的解决方案,但代码较少(仅限8行).
希望这可以帮助.
以上是脚本宝典为你收集整理的PHP- MySQL搜索标准全部内容，希望文章能够帮你解决PHP- MySQL搜索标准所遇到的问题。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。