简化SQL查询的PHP脚本

Question

I'm a self-taught, beginner programmer. Recently I've been working on a PHP script to query a database with user-entered keywords. What I've come up with seems much more complicated than it needs to be, so I was wondering if there was a way I could simplify what I wrote. Please let me know if you have any other questions or need any more code. Thank you!

    $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']) . "%' ";

Answer 1

Because your WHERE conditions are so different there wouldn't be any way of reducing the number of lines in the code but each line could be slightly shorter. Also you want to pass the submitted variables through mysql_real_escape_string() to prevent SQL injection attacks.

You can prepare all of your variables in a loop so you don't have to run through mysql_real_escape and str_replace on each line:

foreach ($_GET as $key => $val) {
  $_GET[$key] = mysql_real_escape_string(str_replace(' ', '%', $val));
}

and I think the call to isset() is slightly redundant so after you've run the loop above each line could look something like this:

if (!empty($_GET['year'])) 
  $types[] = "`year` LIKE '%" . $_GET['year'] . "%' ";