PHP passing an array into a SQL query

Question

I create an array from checkboxes that are "checked" via JS.

Simple Checkboxes:

     <div class="checkbox">
          <label>
            <input type="checkbox" name="checkSearch[]" value="One" checked /> One
          </label>
      </div>
      <div class="checkbox">
          <label>
            <input type="checkbox" name="checkSearch[]" value="Two" checked /> Two
          </label>
      </div>
      <div class="checkbox">
          <label>
            <input type="checkbox" name="checkSearch[]" value="Three" checked /> Three
          </label>
      </div>

JS:

        var selectedItems = [];
        $('input[type="checkbox"]:checked').each(function () {
                selectedItems .push($(this).val());
        });

        //passing array in a function
        searchLocations(pass1, pass2, selectedItems);

At this point what is outputted is (assuming all three checkboxes are "checked"):

["One", "Two", "Three"];

Here's where I'm not quite sure what needs to happen next?

Can I include the array as a URL string and use the PHP .implode function later on within my SQL statement?

Here's what I've tried:

function searchLocations(pass1, pass2, selectedItems) {
var searchUrl = 'searchLocations.php?pass1=' + pass1 + '&pass2=' + pass2 + '&selectedItems=' + selectedItems;
...
}

searchLocations.php

$pass1 = $_GET['pass1'];
$pass2 = $_GET['pass2'];
$selectedItems = $_GET['selectedItems'];
$selectedItems = "  '".implode("', '", $selectedItems)."'  ";

$query = sprintf("SELECT * FROM dbtable WHERE pass1 = $pass1 AND pass2 = $pass2 AND selectedItems IN ($selectedItems)");

$selectedItems needs to read like this in the SQL query: IN ('One', 'Two', 'Three')..

Your $_GET['selectedItems'] is not an array. It's a string. Have a look at var_dump($_GET['selectedItems']);. You might need to explode() it first. You can also use jQuery to create the query string for you: var searchUrl = 'searchLocations.php?'+$param({pass1: pass1, pass2: pass2, selectedItems: selectedItems}); If you do this then $_GET['selectedItems'] will be an array and your code should work. — gen_Eric
– gen_Eric, Commented Jan 9, 2014 at 19:38
Why are you using sprintf, without any %s? This is a very unsafe piece of code. You should be using prepared statements, or at the very least actually escaping the inputs. — gen_Eric
– gen_Eric, Commented Jan 9, 2014 at 19:39

Dave · Accepted Answer · 2014-01-09 19:45:12Z

1

Define a function that implodes and allows you to wrap elements:

function implode_wrapped($before, $after, $glue, $array) 
{
    $out = '';
    foreach ( $array as $item ){
        $out .= $before.$item.$after.$glue;
    }

    return substr($out, 0, -strlen($glue));
}

You can then do stuff like....

Implode the array values and append to your query string:

implode_wrapped('selectedItems[]=', '', '&amp;', $items);

Note that I'm using 'selectedItems[]'. Your code - function searchLocations() - in its current form is not sending selectedItems as an array.

Implode and use with SQL:

implode_wrapped("'", "'", ',', $items);

Note that you need to sanitize any values that get sent to SQL or you will be hacked.

answered Jan 9, 2014 at 19:45

Dave

3,6581 gold badge18 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

PHP passing an array into a SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related