变量串联的mysql查询字符串在phpMyAdmin中运行良好，但在脚本中未运行PHP [英] Variable concatenated mysql query string runs fine in phpMyAdmin but not PHP in script

问题描述

更新14/04/2011

UPDATED 14/04/2011

仍然有麻烦.我将代码简化为最简单的形式.我使用IF函数检查一个复选框的isset()，效果很好.如果选中此复选框，它将连接一个由两部分组成的字符串.很简单.

Still in trouble. I reduced my code to its simplest form. I use the IF function to check isset() for a checkbox, which works fine. If the checkbox is checked it concatenates a string made of two parts. Very simple.

if (isset($_POST[testType1])) {
   $filterQuery .= "(testType1 = '1'"; 
   }
   $filterQuery .= ") ";
}

当我使用mysql_fetch_assoc并在$ rows中回显信息时，它将起作用.但是，当我在Google Chrome浏览器中查看页面源时，它说: Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1;

When I use mysql_fetch_assoc and echo the info in the $rows it works. But when I view the page source it in Google Chrome it says: Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1;

如果我回显$ filterQuery，它会正确显示，并且当我将回显的字符串复制到我的代码中时，MySQL返回正确的结果:

If I echo $filterQuery it displays correctly and when I copy the echoed string into my code MySQL returns the correct results:

SELECT * FROM fdatav1 JOIN ddatav1 ON ddatav1.ID = fdatav1.ID WHERE (testType1 = '1')

我也尝试过将$ filterQuery强制转换为字符串.没有成功.

I have tried casting $filterQuery to a string as well. No success.

更新12/04/2011

UPDATED 12/04/2011

我仍然有问题，这不是错字.参见下面的代码:

I still have a problem, it wasn't a typo. See code below:

$query = "SELECT * FROM fdatav1 JOIN ddatav1 ON ddatav1.ID = fdatav1.ID WHERE ";`
$orTrigger = "";`

function setOrTrigger() {
    global $orTrigger;
    if ($orTrigger=="") {
        $orTrigger="OR ";
    }
}

function getTestFilterQuery($testType) {
   if (!(isset($_POST[test1])) && !(isset($_POST[test2])) && !(isset($_POST[test3]))) {
     $returnString = NULL;
     return $returnString;
   }

}

if (isset($_POST[testType1])) {
   $filterQuery .= $orTrigger ."(testType1 = '1'"; 
   setOrTrigger();
   $addTestFilterQuery = getTestFilterQuery("testType1");
   if ($addTestFilterQuery != NULL) {
    $filterQuery .= "AND " .$addTestFilterQuery;
   }
   $filterQuery .= ") ";
}

$connection = mysql_connect(localhost, $username, $password);

if (!$connection) {
   die('Not connected : ' . mysql_error());

}

$db_selected = mysql_select_db($database, $connection);

if (!$db_selected) {
   die ('Can\'t use db : ' . mysql_error());
}

$result = mysql_query($filterQuery);

if (!$result) {
   die('Invalid query: ' . mysql_error());
}

while ($row = @mysql_fetch_assoc($result)) {
   echo $row['name'];
   echo $row['description'];
}

当我回显$ query时，我得到:

When I echo $query I get:

SELECT * FROM fdatav1 JOIN ddatav1 ON ddatav1.ID = fdatav1.ID WHERE (testType1 = '1')

当我直接将其复制到mysql_query中时，

When I copy this directly into mysql_query like:

mysql_query("SELECT * FROM fdatav1 JOIN ddatav1 ON ddatav1.ID = fdatav1.ID WHERE (testType1 = '1')");

它工作正常.但是当我像这样传递变量时:

it works fine. But when I pass the variable like:

mysql_query($ filterQuery);

mysql_query($filterQuery);

我在''附近收到一个语法错误.有人知道如何解决这个问题吗?

i get a syntax error one near ''. Does anyone know how to resolve this?

推荐答案

您是否真的在字符串中加上了双引号，如:

Are you actually putting the double quotes in the string, like:

$query = '"SELECT * FROM table WHERE col = value"';
echo $query; //output is exactly: "SELECT * FROM table WHERE col = value"

如果是这样，则需要从字符串，mysql_query或带有普通字符串的任何字符串中删除"，例如:

If so, you need to remove the "s from inside the string, mysql_query or whatever takes a normal string like:

$query = "SELECT * FROM table WHERE col = value";
echo $query; //output is exactly: SELECT * FROM table WHERE col = value

以几乎相同的方式，您不会在字符串中以;结尾的SQL查询结束，例如:$query = "SELECT * FROM table WHERE col = value;";

In much the same way, you don't end an SQL query with ; in the string like: $query = "SELECT * FROM table WHERE col = value;";

这篇关于变量串联的mysql查询字符串在phpMyAdmin中运行良好，但在脚本中未运行PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！