简化SQL查询的PHP脚本
问题描述:
我是一名自学成才的初学程序员。最近我一直在研究一个PHP脚本,用用户输入的关键字查询数据库。我想出的东西似乎比它所需要的复杂得多,所以我想知道是否有办法简化我写的东西。如果您有任何其他问题或需要更多代码,请告诉我。谢谢!简化SQL查询的PHP脚本
$types = array();
if(!empty($_GET['location_id']) && isset($_GET['location_id'])) $types[] = "groups.location_id = " . str_replace(' ', '%', $_GET['location_id']) . " ";
if(!empty($_GET['season_id']) && isset($_GET['season_id'])) $types[] = "seasons.season_id = " . str_replace(' ', '%', $_GET['season_id']) . " ";
if(!empty($_GET['event']) && isset($_GET['event'])) $types[] = "(`event` LIKE '%" . str_replace(' ', '%', $_GET['event']) . "%' OR `note` LIKE '%" . str_replace(' ', '%', $_GET['event']) . "%') ";
if(!empty($_GET['place']) && isset($_GET['place'])) $types[] = "`place` LIKE '%" . str_replace(' ', '%', $_GET['place']) . "%' ";
if(!empty($_GET['city']) && isset($_GET['city'])) $types[] = "`city` LIKE '%" . str_replace(' ', '%', $_GET['city']) . "%' ";
if(!empty($_GET['state_abbr']) && isset($_GET['state_abbr'])) $types[] = "`state_abbr` LIKE '%" . str_replace(' ', '%', $_GET['state_abbr']) . "%' ";
if(!empty($_GET['weekday']) && isset($_GET['weekday'])) $types[] = "(`weekday` LIKE '%" . str_replace(' ', '%', $_GET['weekday']) . "%' OR `through_weekday` LIKE '%" . str_replace(' ', '%', $_GET['weekday']) . "%') ";
if(!empty($_GET['month']) && isset($_GET['month'])) $types[] = "`month` LIKE '%" . str_replace(' ', '%', $_GET['month']) . "%' ";
if(!empty($_GET['day']) && isset($_GET['day'])) $types[] = "(`day` LIKE '%" . str_replace(' ', '%', $_GET['day']) . "%' OR `through_day` LIKE '%" . str_replace(' ', '%', $_GET['day']) . "%') ";
if(!empty($_GET['year']) && isset($_GET['year'])) $types[] = "`year` LIKE '%" . str_replace(' ', '%', $_GET['year']) . "%' ";
答
因为你WHERE
条件是如此的不同不会有减少的代码行数的任何方式,但每行有可能会稍微短。您还希望通过mysql_real_escape_string()
传递提交的变量以防止SQL injection攻击。
你可以准备所有的变量在一个循环,这样你就不必通过mysql_real_escape
和str_replace
每行运行:
foreach ($_GET as $key => $val) {
$_GET[$key] = mysql_real_escape_string(str_replace(' ', '%', $val));
}
,我想调用isset()
是略显多余等你以后“已经运行的每个线以上的循环可能是这个样子:
if (!empty($_GET['year']))
$types[] = "`year` LIKE '%" . $_GET['year'] . "%' ";
答
只是一个想法。它可能使代码更清晰,SQL写一个非常简单的工作。
将这个代码用于测试目的:
$_GET['event']='jut for test';
$_GET['place']='jut for test';
$_GET['city']='jut for test';
$_GET['state_abbr']='jut for test';
$_GET['weekday']='jut for test';
$_GET['month']='jut for test';
$_GET['day']='jut for test';
$_GET['year']='jut for test';
然后在下面的是,把实际代码:
$queryTmplArr=Array("(`@field` LIKE '%@value%' OR `note` LIKE '%@value%') ",
"`@field` LIKE '%@value%' ","`@field` LIKE '%@value%' ","`@field` LIKE '%@value%' ",
"(`@field` LIKE '%@value%' OR `through_weekday` LIKE '%@value%') ",
"`@field` LIKE '%@value%' ","(`@field` LIKE '%@value%' OR `through_day` LIKE '%@value%') ",
"`@field` LIKE '%@value%' ");
$i=0;
foreach($_GET as $key =>$rawData)
{
$cleanData= mysql_real_escape_string(str_replace(' ', '%', $rawData)) ;
$queryTmplArr[$i]=str_replace('@value', $cleanData, $queryTmplArr[$i]);
$queryTmplArr[$i]=str_replace('@field', $key, $queryTmplArr[$i]);
$i++;
}
并再次测试目的:
echo '<pre>';
print_r($queryTmplArr);
这将输出这样:
Array
(
[0] => (`event` LIKE '%jut%for%test%' OR `note` LIKE '%jut%for%test%')
[1] => `place` LIKE '%jut%for%test%'
[2] => `city` LIKE '%jut%for%test%'
[3] => `state_abbr` LIKE '%jut%for%test%'
[4] => (`weekday` LIKE '%jut%for%test%' OR `through_weekday` LIKE '%jut%for%test%')
[5] => `month` LIKE '%jut%for%test%'
[6] => (`day` LIKE '%jut%for%test%' OR `through_day` LIKE '%jut%for%test%')
[7] => `year` LIKE '%jut%for%test%'
)
这样行吗?
我只是写了一个类似的答案......唯一的区别是我用'$ _GET'中使用的键设置了一个数组,并且只修改了那些。可能不想扫描和修改整个'$ _GET',因为可能有其他值不想修改。 –
@AaronW。你能告诉我你的代码是什么样子吗?谢谢! –