将php变量放入mysql查询

If you don't want to get a full summary of what I'm trying to do skip to (Problem starts here)

如果你不想对我要做的事情有一个完整的总结，那就跳过去(问题从这里开始)

I'm setting up my new site and came across a problem.

我正在建立我的新网站，遇到了一个问题。

What I'm basically trying to do is to assign ads to specific countries. So for example if your from the UK you would be shown ads that we have in our UK inventory.

我想做的就是给特定的国家做广告。例如，如果你来自英国，你会看到我们英国库存中的广告。

So I gathered some data from Google on how to detect a user's country based on their IP. I've made a function which does this perfectly.

因此，我从谷歌中收集了一些关于如何基于用户的IP检测用户的国家的数据。我做了一个很好的函数。

$ip_address= $_SERVER['REMOTE_ADDR'];

function ip_location($ip){
    $parts = explode('.', $ip);
    $numeric_ip = $parts[3] + (256 * $parts[2]) + (256 * 256 * $parts[1]) + (256 * 256 * 256 * $parts[0]);

    $sql = "SELECT country FROM iptocountry WHERE lower_bound <= $numeric_ip AND upper_bound >= $numeric_ip LIMIT 1";
    $result = mysql_query($sql);
    $country = mysql_result($result, 0);
    return $country;
}

$country = ip_location($ip_address);
echo $country; // Always echos the correct country

(Problem Starts here)

(问题从这里开始)

So this function works fine. After making this function I created a MYSQL query which uses the data from that function to select an ad to show a user.

这个函数很好。完成此函数后，我创建了一个MYSQL查询，该查询使用该函数的数据选择一个广告来显示用户。

Here is where the problem starts.

问题从这里开始。

When I type this query:

当我输入这个查询时:

$sql = "SELECT *  FROM `nuevo__htmlad` WHERE `country` = 'united kingdom' AND `active` = 1 ORDER BY RAND() LIMIT 1";

using country = 'united kingdom' it works fine but when I put country = '$country'

使用country = 'united kingdom'行，但如果我写上country = '$country'

Nothing works it never displays an ad.

什么都没用，它永远不会显示广告。

Can anyone help me understand why this query doesn't work when I place the PHP variable inside it. This actually is the first time something this simple has troubled me so much.

当我将PHP变量放入其中时，任何人都能帮助我理解为什么这个查询不起作用。这实际上是第一次这么简单的事情让我如此困扰。

Any help would be appreciated.

如有任何帮助，我们将不胜感激。

5 个解决方案

#1

After asking a few questions in the comments, it turned out that the $country variable contained and additional empty space.

在评论中问了几个问题之后，发现$country变量包含了额外的空空间。

OP fixed it using substr_replace ($country , '' , -1). I would personaly choose trim():

OP使用substr_replace ($country， "， -1)修复它。我个人选择trim():

$sql = "SELECT *  FROM `nuevo__htmlad` WHERE `country` = '".trim($country)."' AND `active` = 1 ORDER BY RAND() LIMIT 1";

#2

I avoid this by using a PDO driver for working with databases. This allows me to use parameters and makes it easy to reuse sql statements. Check out this article for more information.

我通过使用PDO驱动程序处理数据库来避免这种情况。这允许我使用参数，并使重用sql语句变得容易。更多信息请参阅本文。

#3

All of the code in the answers provided here contains SQL Injection vulnerabilities. You MUST escape the user input.

这里提供的答案中的所有代码都包含SQL注入漏洞。您必须转义用户输入。

http://en.wikipedia.org/wiki/SQL_injection

#4

-1

$sql= "SELECT *  FROM nuevo__htmlad WHERE country='$country' AND active=1 ORDER BY RAND() LIMIT 1";

or this try this one also

或者这个也试试这个

$sql= "SELECT *  FROM nuevo__htmlad WHERE country='$country' AND active=1 LIMIT 1 ORDER BY RAND()";

#5

-1

try this one: I always use this style since country is a string

试试这个:我总是使用这个样式，因为country是字符串

$sql= "SELECT *  FROM nuevo__htmlad WHERE country='".$country."' AND active=1 ORDER BY RAND() LIMIT 1";

#1