时间戳

首页 > 解决方案 > 将值和数组添加到 PDO 执行方法

php - 将值和数组添加到 PDO 执行方法

问题描述

我想使用 PDO 并将选择列表中的数据数组和文本字段绑定到 MYSQL 查询。这意味着我想绑定一个数组和一个值。现在我知道您可以将数组绑定到 execute() 方法，但是值呢？我可以将值和数组添加到另一个数组并执行（）吗？我该怎么做？这是我正在尝试做的一个例子：

求职网页示例：

<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>

标签： phpmysqlsqlpdo

解决方案

好的，经过一番修改，我发现您只需使用 array_merge() 将查询的所有值添加到一起，然后将该数组添加到 $stmt->execute() 方法。只需确保此数组中的值符合查询所需的正确顺序即可。在我的示例中，解决方案是：

$data = array_merge((array)$keyword, $occupation, $location);
$stmt->execute($data);
  • $keyword 是一个值，$occupation 和 $location 是一个数组。
  • 添加一个 if 测试以检查这些变量是否为空并调整 $stmt->execute() 方法

更新的答案

<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>

推荐阅读