动态创建一个mysql搜索字符串? [英] creating a mysql search string dynamically?
问题描述
我正在尝试创建一个简单的搜索页面,但我不是 100% 确定如何编写实际的搜索字符串(如果变量存在,则使用适当的 AND 等)这里是代码:
I'm trying to create a simple search page, but I'm not 100% sure how to write the actual search string (using the appropriate AND's etc if the variable exists) here's the code:
if ($post) {
//get all search variables
$type = JRequest::getVar('type');
$classifications = JRequest::getVar('classifications', array(0), 'post', 'array');
$rating = JRequest::getVar('rating');
$status = JRequest::getVar('status');
$cterms = JRequest::getVar('cterms');
$clientid = JRequest::getVar('clientid');
$company = JRequest::getVar('company');
$address = JRequest::getVar('address');
$name = JRequest::getVar('name');
$surname = JRequest::getVar('surname');
$city = JRequest::getVar('city');
$state = JRequest::getVar('state');
$pcode = JRequest::getVar('pcode');
$country = JRequest::getVar('country');
//create search string
echo "SELECT * FROM #__db_clients "; <- the query is supposed to be done here.. it's in as echo because I was trying to spit it out before trying to make it run.. :)
} else {
echo 'There has been an error, please try again.';
};
我试过使用 (if type != null then searchtype = "where type = 'X'") 但后来我不知道如何在搜索需要之前/之后放置 AND ..如果这是有道理的?
I've tried using (if type != null then searchtype = "where type = 'X'") but then I couldn't figure out how to place the AND before/after if it's required for the search.. if that makes sense?
推荐答案
这是一个简单的例子.我不知道 JRequest::getVar 返回什么样的数据(总是字符串还是混合类型?)但这应该会让你开始.确保使用适用于 foreach 循环的任何转义方法:
This is a quick example. I don't know what kind of data JRequest::getVar returns (always a string, or mixed types?) but this should start you off. Make sure to use whichever escaping method applies within the foreach loop:
if ($post) {
$criteria = array();
//get all search variables
$criteria['type'] = JRequest::getVar('type');
$criteria['classifications'] = JRequest::getVar('classifications', array(0), 'post', 'array');
$criteria['rating'] = JRequest::getVar('rating');
//if there are some criteria, make an array of fieldName=>Value maps
if(!empty($criteria)) {
$where = array();
foreach($criteria as $k => $v) {
//IMPORTANT!!
//$v is the value of the field, needs to be quoted correctly!!
$where[] = "$k = '$v'";
}
}
//create search string
$query = "SELECT * FROM #__db_clients";
if($where) {
$query .= " where " . join(' AND ', $where);
}
} else {
echo 'There has been an error, please try again.';
};
这篇关于动态创建一个mysql搜索字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!