Mutliple querystring parameters to mysql query

Question

I originally had this working:

url: http://server/blah.php?FacilityCode=FT

$facilitycode = mysql_real_escape_string($_GET["FacilityCode"]);
$sql = "SELECT ..." .
       "FROM ..." .
       "WHERE ..." .
       "AND ('" . $facilitycode . "' = '' OR Facility.FacilityCode = '". $facilitycode . "')";
$result = mysql_query($sql);

But I want to change this so that people can submit multiple values in the query strying somehow, ie: http://server/blah.php?FacilityCode=FT,CC,DD,EE

I tried changing the query to an "IN" clause instead of an "equals" but I'm not sure how to get the ' marks around each element.

$facilitycode = "'" . implode("', ", explode(',', mysql_real_escape_string($_GET['FacilityCode']))) . "'"; Also you should get rid of the old legacy mysql_ functions. Use PDO instead. — clentfort
– clentfort, Commented Sep 26, 2012 at 22:23

Glavić · Accepted Answer · 2012-09-26 22:22:58Z

2

Use implode() function for IN (...).

$a = array('AB', 'CD', 'EF', 'ZE');
echo "field IN ('" . implode("', '", $a) . "')";

... will output:

field IN ('AB', 'CD', 'EF', 'ZE')

+escape every option you get.

answered Sep 26, 2012 at 22:22

Glavić

43.6k13 gold badges79 silver badges108 bronze badges

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

Comments

Moe Tsao · Accepted Answer · 2012-09-26 22:23:16Z

0

$facilitycode = mysql_real_escape_string($_GET["FacilityCode"]);
$array=explode(',',$facilitycode);
foreach ($array as $a){$output.="'$a',";}
$clause=substr($output,0,-1);

answered Sep 26, 2012 at 22:23

Moe Tsao

1,0546 silver badges9 bronze badges

Comments

Flosculus · Accepted Answer · 2012-09-26 22:30:48Z

0

If your trying to create a string which looks like this: 'AB', 'CD', 'EF', 'ZE'

Try this before its placed inside the query:

$facilitycode = preg_replace('/([^,]+)/', '\'$1\'', $facilitycode);

answered Sep 26, 2012 at 22:30

Flosculus

6,9563 gold badges22 silver badges44 bronze badges

Comments

Develoger · Accepted Answer · 2012-09-26 22:34:44Z

0

I wrote this based on your query, but still I dont get this part of query "AND ('" . $facilitycode . "' = ''", anyway you need to check if $_GET data have "," and if does explode that variable by "," so that you can add an OR clausule for everything that was separated by "," in $_GET data.

After that just form your query by doing a foreach for every element in exploded array like I done below:

<?php

$facilitycode = $_GET["FacilityCode"];

$facility_number_chk = strpos($facilitycode, ",");

if ($facility_number_chk > -1) {

    $facilitycode = explode(",", $facilitycode);

    $sql = "SELECT ..." .
    "FROM ..." .
    "WHERE ..." .
    "AND ('" . $facilitycode . "' = ''";

    foreach($facilitycode as $facode) {

        $facode = mysql_real_escape_string($facode);

        $sql .= " OR Facility.FacilityCode = '". $facode . "'";

    }

    $sql .= "')";

}
else {

    $facilitycode = mysql_real_escape_string($facilitycode);

    $sql = "SELECT ..." .
    "FROM ..." .
    "WHERE ..." .
    "AND ('" . $facilitycode . "' = '' OR Facility.FacilityCode = '". $facilitycode . "')";

}

$result = mysql_query($sql);

And if there is only one element in $_GET data just do an else like I done with your regular query.

answered Sep 26, 2012 at 22:34

Develoger

3,9882 gold badges26 silver badges38 bronze badges

2 Comments

Develoger Over a year ago

Be more specific, I wrote that code for few minutes and didnt tested it. It is given for guiding purpose only as one of the possible directions in which this problem could be solved.

Glavić Over a year ago

I just saw "AND ('" . $facilitycode . "' = ''";, where $facilitycode is array, so output off this will be AND ('Array' = ''";.

Brandon · Accepted Answer · 2012-09-27 00:52:05Z

0

I ended up using a combination of a few of the answers. Basically I exploded on the ",", then did a foreach to add the ' marks and call escape_string, and then imploded it back.

$facilitycodes = $_GET["FacilityCode"];
if ($facilitycodes == '') {
  $additionalfilter = '';
}
else {
  $facilitycodearray = explode(",", $facilitycodes);
  foreach($facilitycodearray as &$facilitycode) {
    $facilitycode = "'" . mysql_real_escape_string($facilitycode) . "'";
  }
  $facilitycodesformatted = implode(",", $facilitycodearray);
  $additionalfilter = " AND Facility.FacilityCode IN (" . $facilitycodesformatted . ")";
}

$sql = "SELECT ..." .
"FROM ..." .
"WHERE ..." .
$additionalfilter;

answered Sep 27, 2012 at 0:52

Brandon

9936 silver badges15 bronze badges

Collectives™ on Stack Overflow

Mutliple querystring parameters to mysql query

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related