Construct SQL SELECT query out of multiple PHP GET parameters

Question

I'm using the code provided in answer https://stackoverflow.com/a/5203351/1711950 to handle different combinations of SQL WHERE clauses:

$vars = array(
    (!empty($_GET["var1"]))? " keyword = '". $_GET["var1"] ."' ": null, 
    (!empty($_GET["var2"]))? " author  = '". $_GET["var2"] ."' ": null,
    (!empty($_GET["var3"]))? " date    = '". $_GET["var3"] ."' ": null,
    (!empty($_GET["var4"]))? " forums  = '". $_GET["var4"] ."' ": null
);


function myfilterarray($var)
{
    return !empty($var)?$var: null;
}

$newvars = array_filter($vars, 'myfilterarray');

$where = join(" OR ", $newvars);

$sql = "SELECT DISTINCT title, description FROM table ".(($where)?"WHERE ".$where: null);

echo $sql;

The code works when each GET parameter is a single value like this:

http://example.com/getData.php?keyword=a1

I want to be able to handle this scenario:

http://example.com/getData.php?keyword=a1,b1,c1,a2,b2,c2

Therefor I've modified the code like this:

(!empty($_GET["var1"]))? " keyword IN ('". $_GET["var1"] ."') ": null

That off course makes the SQL query look like this, which won't work because of the quotations.

SELECT * FROM table WHERE keyword IN ('SE162321000156-3PQZ,SE2321000164-7381037592311')

How could I get around this problem?

Thanks!

you try to get rows from database where keyword exactly is 'SE162321000156-3PQZ,SE2321000164-7381037592311'. As fast and dirty workaround you can use $in = "'".preg_replace('/,/',"','",$v)."'" or $in = "'".implode("','",explode(',',$v))."'"; — Wizard
– Wizard, Commented Nov 30, 2016 at 21:17

Saerdn · Accepted Answer · 2016-11-30 21:17:49Z

2

You have to split the values , put quotation marks around them and concatenate the string back together. This is a very very basic example:

$foo = (!empty($_GET["keyword"]))? $_GET["keyword"] : null;
$foo = explode(",", $foo);
$fooArray = [];
foreach( $foo as $fooElement ) {
    $fooArray[] = "'$fooElement'";
}
$foo = implode(",", $fooArray);

echo $foo; // output: 'a1','b1','c1','a2','b2','c2'

answered Nov 30, 2016 at 21:17

Saerdn

2283 silver badges19 bronze badges

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

5 Comments

Rawland Hustle Over a year ago

Thanks, but that gave me ''a1','b1','c1','a2','b2','c2'' though.

Saerdn Over a year ago

Mhm not if I execute the exact same code as I've posted it. Did you change sth.?

Rawland Hustle Over a year ago

I don't now what "sth." is but I changed "keyword" to the parameter I'm using.

Saerdn Over a year ago

Ah! Yes, you don't need the quation marks at the end and the beginning of the sql string: keyword IN (". $foo .") should do the trick as the elements are already wrapped

Saerdn Over a year ago

btw "sth." = something :)

Nandan Bhat · Accepted Answer · 2016-11-30 21:35:19Z

0

$arr = explode(",",trim($_GET["var1"]));
$var = "";
for($i=0;$i<sizeof($arr);$i++)
{
    if($i == (sizeof($arr)-1))
    {
        $var .= "'".$arr[$i]."'";
    }
    else
    {
        $var .= "'".$arr[$i]."',";
    }
}

Now you can use $var inside your query, i.e IN($var)

edited Nov 30, 2016 at 21:35

answered Nov 30, 2016 at 21:25

Nandan Bhat

1,5632 gold badges10 silver badges21 bronze badges

3 Comments

Rawland Hustle Over a year ago

Thanks, but that gave me Parse error: syntax error, unexpected ')', expecting ';' on line 3

Nandan Bhat Over a year ago

@RawlandHustle I have re-edited the code. Kindly check.

Rawland Hustle Over a year ago

Sorry, but it doesn't work for me. I have pasted the whole code I've taken from another question/answer. Could you put your code in it like it supposed to be?

Collectives™ on Stack Overflow

Construct SQL SELECT query out of multiple PHP GET parameters

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related