Simplify PHP Script for SQL Query

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

Clive · Accepted Answer · 2011-09-27 23:54:00Z

2

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

answered Sep 27, 2011 at 23:54

Clive

1

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron W. Over a year ago

I was just writing up a similar answer...only difference was I set an array with the used keys in $_GET and modified only those. Probably don't want to scan and modify the entire $_GET since there may be other values he doesn't want to modify.

Lucas Schneider Over a year ago

@AaronW. Could you show me what your code would look like? Thank you!

Melsi · Accepted Answer · 2011-09-28 00:41:34Z

Just an idea.. It might make the code more clear and the sql writing a very easy job.

Put this code for testing purposes:

 $_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';

Then below that, put the actual code:

$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++;
}

And for testing purpose again:

echo '<pre>';
print_r($queryTmplArr );

This will output this:

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%' 
)

Is this okay?

Collectives™ on Stack Overflow

Simplify PHP Script for SQL Query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related