html< select multiple = multiple> + SQL查询搜索 [英] html <select multiple=multiple> + SQL Query Search

问题描述

我试图根据

I'm trying to do a search based on

<select multiple=multiple name="chkUnr[]">

我通过运行代码从select中获取值：

I'm getting out the values from select by running code:

 for($i=0;$i<count($_POST["chkUnr"]);$i++)
                    {
                    if($_POST["chkUnr"][$i] != "")
                    {
                        $search_country = $_POST["chkUnr"][$i];                     
                    }

            $query = "";
            $query .= "SELECT users.* FROM users";
            if (isset($_POST['singles_online']) ? $_POST['singles_online'] : 0 == 1) {
              $query .= " LEFT JOIN online ON online.user_id = users.id";
            }
            $query .= " WHERE";
            if (isset($_POST['vip']) ? $_POST['vip'] : 0 == 1) {
             $query .= " users.vip = 1 AND";
            }
            if (isset($_POST['profile_image']) ? $_POST['profile_image'] : 0 == 2) {
              $query .= " users.profile_image = '2' AND";
            }
            if (isset($_POST['singles_online']) ? $_POST['singles_online'] : 0 == 1) {
              $query .= " online.is_online = 1 AND";
            }
            $query .= " (id NOT IN (SELECT user_id FROM users_blocked WHERE blocked_id = '$user_id')) AND";

            $query .= " (users.user_age >= '$age_from' AND users.user_age <= '$age_to') AND";

            $query .= " (users.gender = '$gender_search') AND";

            $query .= " users.country IN ('$search_country')";

            $search_query = mysql_query($query);


            }

我可以打印出数值当我执行SQL搜索时，问题就出现了。
在这种情况下，只使用国家：
来获取第一个值因此，当我选择瑞典，德国，美国时，我可以将它们全部打印出来，但是只有在尝试执行SQL查询时才会选择瑞典向上。

And i can print out the values but the problem comes when i do the SQL search. It only pick up the first value in this case im using countries: So when i select Sweden, Germany, Usa i can print them all out but when trying to do a SQL query only Sweden is being picked up.

我试过这段代码，但结果仍然相同。

I've tried with this code but still same result.

推荐答案

这里的问题（以及其他答案）是在子句被引号包围，所以不会产生我们想要的结果。我们需要有效地将数组传递给查询。另外您的代码容易受到sql注入攻击。我强烈建议转向PDO /准备好的声明。我为这些国家增加了一项轻微保护措施，但这并不是万无一失的。

The problem here (and with the other answer) is that the in clause was surrounded by quotes, so that will not yield the result that we want. We need to effectively pass in an array to the query. Also your code is vulnerable to sql injection. I would strongly suggest moving to PDO/prepared statements. I added a slight protection to the countries, but that is not foolproof by any means.

function prepareForSql($value, $key) {
    return addslashes($value);
}

array_walk($_POST["chkUnr"], "prepareForSql");
$search_country = "'" . implode("','", $_POST["chkUnr"]) . "'";

$query = "";
$query .= "SELECT users.* FROM users";
if (isset($_POST['singles_online']) ? $_POST['singles_online'] : 0 == 1) {
    $query .= " LEFT JOIN online ON online.user_id = users.id";
}
$query .= " WHERE";
if (isset($_POST['vip']) ? $_POST['vip'] : 0 == 1) {
    $query .= " users.vip = 1 AND";
}
if (isset($_POST['profile_image']) ? $_POST['profile_image'] : 0 == 2) {
    $query .= " users.profile_image = '2' AND";
}
if (isset($_POST['singles_online']) ? $_POST['singles_online'] : 0 == 1) {
    $query .= " online.is_online = 1 AND";
}
$query .= " (id NOT IN (SELECT user_id FROM users_blocked WHERE blocked_id = '$user_id')) AND";

$query .= " (users.user_age >= '$age_from' AND users.user_age <= '$age_to') AND";
$query .= " (users.gender = '$gender_search') AND";

$query .= " users.country IN ($search_country)";

$search_query = mysql_query($query);

这篇关于html< select multiple = multiple> + SQL查询搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！