Add value and arrays to PDO execute method

Question

I want to use PDO and bind an array of data from a select list, and a text field to a MYSQL query. That means I want to bind an array and a value. Now I know you can bind an array to the execute() method, however what about the value? Can I add the value and array to another array and execute() it? How would I do that? Here is an example of what I am trying to do:

Jobsearch Web page example:

<html>
<head>
    <?php
        require_once("include/config.php");
        session_start();
    ?>
</head>
<body>
<h1>Jobsearch</h1>
<form method="post" action="">
    <h2>Keyword</h2>
    <input type="text" placeholder="Keyword" name="keyword">
    <h2>Location</h2>
    <select name="location[]" id="location" title="location" multiple>
        <option value="Central Coast">Central Coast</option>
        <option value="Hunter Valley">Hunter Valley</option>
        <option value="Illawarra">Illawarra</option>
        <option value="Richmond">Richmond</option>
        <option value="Ballarat">Ballarat</option>
        <option value="Riverina">Riverina</option>
        <option value="Murray">Murray</option>
        <option value="Mid North Coast">Mid North Coast</option>
    </select>
    <h2>Occupation</h2>
    <select name="occupation[]" id="occupation" title="occupation" multiple>
        <option value="Accounting">Accounting</option>
        <option value="Education">Education</option>
        <option value="Healthcare">Healthcare</option>
        <option value="Information Technology">Information Technology</option>
        <option value="Retail">Retail</option>
        <option value="Sales">Sales</option>
    </select>
    <br/><br/>
    <button type="submit" name="search_jobs" >Search Jobs</button>
    <?php
        if(isset($_POST['search_jobs']))
        {           
            //create query          
            $sql = "SELECT * FROM job, location, occupaton WHERE job.title LIKE CONCAT('%', ?, '%') ";

            $keyword = $_POST['keyword']; //value
            $occupation = $_POST['occupation']; //array
            $location = $_POST['location']; //array

            if($keyword == NULL)
            {
                $keyword = " ";
            }
            if($occupation != NULL)
            {
                $occs = implode(',', array_fill(0, count($occupation), '?'));
                $sql = $sql . "AND occupation.name IN ($occs) ";
            }
            if($location !=NULL)
            {
                $locs = implode(',', array_fill(0, count($location), '?'));
                $sql = $sql . "AND location.region IN ($locs) ";
            }

            $sql = $sql . "AND job.location_id = location.id
                           AND job.occupation_id = occupation.id";

            echo "<br/><br/><b>" . $sql . "</b><br/>";

            //get results
            try
            {
                $stmt = $DB->prepare($sql);
                //$stmt->execute([$keyword, $occupation, $location]); 
                //$data = array_merge($keyword, $occupation, $location);
                //$stmt->execute($data); //what do i do here?
                $results = $stmt->fetchAll(); 
            }
            catch (Exception $ex)
            {
                echo $ex->getMessage();
            }

            //print results
            echo "<h2>Results</h2>";
            foreach($results as $res)
            {
                echo "<p>Title:" . $res['title'];
                echo "<p>Views:" . $res['views'];
                echo "<p>Created At:" . $res['created_at'];
                echo "<p>Image:" . $res['img_url'];
                echo "<p>Salary:" . $res['salary'];
                echo "<p>Region:" . $res['region'];
                echo "<p>Region:" . $res['location_id'];
                echo "<p>State:" . $res['state'];
                echo "<br/><br/>";

            }

        }
    ?>
</form>
</body>
</html>

Possible duplicate of https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — sticky bit, Apr 30 '18 at 13:25
I have read those but it doesn't really answer my question. Is there a simple way to add an array and a variable to the stmt->execute() method? If it was just the location array I could bind it like this stmt->execute($location); — Magearlik, May 01 '18 at 04:49
Is there a way to add the value, and two arrays into one array that can be used by the execute() method? I am only guessing, but I assume if it contains the correct number of values, and in the correct order, as the '?'s in my SQL statement that would work? — Magearlik, May 01 '18 at 05:22
This is my understanding from reading http://php.net/manual/en/pdostatement.execute.php #5 — Magearlik, May 01 '18 at 05:34
How would you merge the value and the two arrays with array_merge() so it would work with the execute() method anyway? — Magearlik, May 01 '18 at 05:36
I tried $data = array_merge(array($keyword), $occupation, $location) and then $stmt->execute($data) however that doesn't work. — Magearlik, May 01 '18 at 05:37

Magearlik · Accepted Answer · 2018-05-01T07:53:24.543

Ok after some tinkering, I discovered that you just add all the values for the query together using array_merge() and add that array to the $stmt->execute() method. Just ensure the values in this array are in the correct order required for your query. In my example the solution was:

$data = array_merge((array)$keyword, $occupation, $location);
$stmt->execute($data);

$keyword is a value, and $occupation and $location are an array.
add an if test to check if any of these variables are blank and adjust the $stmt->execute() method

UPDATED ANSWER

<html>
<head>
    <?php
        require_once("include/config.php");
        session_start();
    ?>
</head>
<body>
<h1>Jobsearch</h1>
<form method="post" action="">
    <h2>Keyword</h2>
    <input type="text" placeholder="Keyword" name="keyword">
    <h2>Location</h2>
    <select name="location[]" id="location" title="location" multiple>
        <option value="Central Coast">Central Coast</option>
        <option value="Hunter Valley">Hunter Valley</option>
        <option value="Illawarra">Illawarra</option>
        <option value="Richmond">Richmond</option>
        <option value="Ballarat">Ballarat</option>
        <option value="Riverina">Riverina</option>
        <option value="Murray">Murray</option>
        <option value="Mid North Coast">Mid North Coast</option>
    </select>
    <h2>Occupation</h2>
    <select name="occupation[]" id="occupation" title="occupation" multiple>
        <option value="Accounting">Accounting</option>
        <option value="Education">Education</option>
        <option value="Healthcare">Healthcare</option>
        <option value="Information Technology">Information Technology</option>
        <option value="Retail">Retail</option>
        <option value="Sales">Sales</option>
    </select>
    <br/><br/>
    <button type="submit" name="search_jobs" >Search Jobs</button>
    <?php
        if(isset($_POST['search_jobs']))
        {           
            //create query          
            $sql = "SELECT * FROM job, location, occupation WHERE job.title LIKE CONCAT('%', ?, '%') ";

            $keyword = $_POST['keyword']; 
            $occupation = $_POST['occupation'];
            $location = $_POST['location']; 

            if($keyword == NULL)
            {
                $keyword = " ";
            }
            $data = (array)$keyword;
            if($occupation != NULL)
            {
                $occs = implode(',', array_fill(0, count($occupation), '?'));
                $sql = $sql . "AND occupation.name IN ($occs) ";
                $data = array_merge($data, $occupation);
            }
            if($location !=NULL)
            {
                $locs = implode(',', array_fill(0, count($location), '?'));
                $sql = $sql . "AND location.region IN ($locs) ";
                $data = array_merge($data, $location);
            }

            $sql = $sql . "AND job.location_id = location.id
                           AND job.occupation_id = occupation.id";

            echo "<br/><br/><b>" . $sql . "</b><br/>";

            //get results
            try
            {
                $stmt = $DB->prepare($sql);
                $stmt->execute($data); 
                $results = $stmt->fetchAll(); 
            }
            catch (Exception $ex)
            {
                echo $ex->getMessage();
            }

            //print results
            echo "<h2>Results</h2>";
            foreach($results as $res)
            {
                echo "<p>Title:" . $res['title'];
                echo "<p>Views:" . $res['views'];
                echo "<p>Created At:" . $res['created_at'];
                echo "<p>Image:" . $res['img_url'];
                echo "<p>Salary:" . $res['salary'];
                echo "<p>Region:" . $res['region'];
                echo "<p>Region:" . $res['location_id'];
                echo "<p>State:" . $res['state'];
                echo "<br/><br/>";

            }

        }
    ?>
</form>
</body>
</html>

Add value and arrays to PDO execute method

1 Answers1