I've been coding with PHP and MySQL for about a year now and have gotten the hang of it pretty well; constructing really complicated queries with joins and calculated fields and all the other joys of MySQL hasn't been a problem for me in months.

我已经用PHP和MySQL编写了大约一年的编码,并且已经很好地掌握了它;使用连接和计算字段构建真正复杂的查询以及MySQL的所有其他乐趣在几个月内对我来说不是问题。

BUT there's something syntactically screwy with the following chunk of code that I can't figure out - even though it's impossibly simple, and, even more infuriating, is closely related to other parts of the project that I'm working on (and which works flawlessly).

但是在语法上会出现以下大块代码,这是我无法弄清楚的 - 即使它不可能简单,而且更令人气愤,与我正在研究的项目的其他部分密切相关(哪些有效)完美)。

Here's the problem code I'm trying to run, followed by the bugchecking I've already done to try to isolate the problem.

这是我正在尝试运行的问题代码,然后是我已经完成的错误检查以尝试隔离问题。

If anyone has any suggestions, I'd be totally grateful because I'm beginning to lose my mind.

如果有人有任何建议,我会非常感激,因为我开始失去理智。

Problem:

I'm really losing my mind over this, so please don't laugh when you see the code:

我真的对此失去了理智,所以当你看到代码时请不要笑!

$query="SELECT count(somefield) FROM db_name WHERE otherfield='".$myvariable."'";

My query finds no results when using a certain variable as part of a field search - even though I know that there are over 900 records in the database that should match.

当使用某个变量作为字段搜索的一部分时,我的查询找不到任何结果 - 即使我知道数据库中有超过900条记录应该匹配。

Bugchecking:

Because I know the value of the variable I'm passing to the query, I've tried hardcoding it into the query and it works fine.

因为我知道我传递给查询的变量的值,所以我尝试将其硬编码到查询中并且工作正常。

I've run the query in the MySQL console (again, of course, hardcoded instead of with the variable) and it works fine.
- To my mind, these two facts eliminate the possibility that there's something syntactically incorrect with the PHP version of the query.

我在MySQL控制台中运行查询(当然,再次硬编码而不是变量)并且它工作正常。在我看来,这两个事实消除了查询的PHP版本在语法上不正确的可能性。

In order to eliminate all possible database connection issues and to make sure the problem isn't related to iterating through the results returned, instead of trying to get the actual results, I've altered my original query to return only the count of the results and have incorporated the standard or die(mysql_error()) statements during the connection sequence. The query is executing, but is finding 0 results, so that eliminates the possibility that it's a connection issue.

为了消除所有可能的数据库连接问题并确保问题与迭代返回的结果无关,而不是尝试获取实际结果,我已经改变了我的原始查询以仅返回结果的计数并在连接序列中包含了standard或die(mysql_error())语句。查询正在执行,但是找到0结果,因此消除了它是连接问题的可能性。

I've verified that the field I'm checking is the correct field for the information I'm looking for (like I said, it runs fine if I hardcode the variable into the query... which, of course, will not be an option in the finished code).

我已经验证了我正在检查的字段是我正在寻找的信息的正确字段(就像我说的,如果我将变量硬编码到查询中它运行正常...当然,这不会是完成代码中的选项)。

I've checked the type of the variable before trying to pass it into the query (figuring that maybe, because it's pulled from a returned xml earlier in the script, that maybe it was showing up as an array or something). It typed as string.

在尝试将变量传递给查询之前,我检查了变量的类型(想想可能,因为它是从脚本中早期返回的xml中提取的,也许它显示为数组或其他东西)。它输入为字符串。

I've verified that the variable is formatted in the way that I expect it to be found in the database; strtoupper, etc.

我已经验证了变量的格式是我希望它在数据库中找到的方式; strtoupper等

I've tried using LIKE '%".$myvariable."'"; still no dice.

我尝试过使用LIKE'%'。$ myvariable。“'”;仍然没有骰子。

Anyone have any suggestions for what I can do to figure out what the hell is going wrong? Thanks so much!

任何人都有任何建议,我可以做些什么来弄清楚到底出了什么问题?非常感谢!

5 个解决方案

#1

It's not a PHP issue so quotes have nothing to do with it. The query has no error, so you'll need to debug it step by step.

这不是PHP问题所以引用与它无关。查询没有错误,因此您需要逐步调试它。

I. SELECT count(*) FROM table_name

I. SELECT count(*)FROM table_name

II. SELECT count(*) FROM table_name WHERE field='$myvariable'

II。 SELECT count(*)FROM table_name WHERE field ='$ myvariable'

Where you're dealing with PHP variables in query, echo the query and run it directly in database to omit PHP's side of error.

在查询中处理PHP变量的地方,回显查询并直接在数据库中运行它以省略PHP的错误。

III. SELECT count(somefield) FROM table_name WHERE field='$myvariable'

III。 SELECT count(somefield)FROM table_name WHERE field ='$ myvariable'

#2

Is the $myVariable escaped? If not, escape it using

$ myVariable是否已逃脱?如果没有,请使用它

$escapedVariable=mysql_real_escape_string($myVariable);

and then run

然后跑

$query="SELECT count(somefield) FROM db_name WHERE otherfield='$escapedVariable'";

#3

Thanks so much to everyone who tried to help, but I figured it out several hours after posting: The first problem was that I forgot to use mysql_real_escape_string($myvariable)... the reasons for how and why I forgot are manifold, but there it is.

非常感谢所有试图提供帮助的人,但我在发布后几个小时就弄明白了:第一个问题是我忘了使用mysql_real_escape_string($ myvariable)...我忘记的方式和原因的原因是多方面的,但那里它是。

So, after plugging that guy back in (which I had ASSUMED had been in this particular module of my code in the first place, but that's where "assuming" gets you, lol), I thought I had the whole thing licked. Three hours later, and still nothing. THEN I realized that it had to be related to XML that was being parsed into the $myvariable... so around and around we went with that one for a few more hours.

所以,在重新插入那个人之后(我已经把ASSUMED放在了我的代码的这个特定模块中,但那是“假设”得到你的地方,哈哈),我以为我把整个事情都舔了一下。三个小时后,仍然没有。然后我意识到它必须与被解析为$ myvariable的XML相关...所以我们周围和周围再一次使用那个。

FINALLY, I realized that the real culprit was my eyes (which aren't so great).. what looked like a perfectly legit quoted string while reading a debug echo of the query before running turned out to have leading and trailing white space (which, of course, I instantly removed with $myvariable=trim($myvariable, " "), and, that, of course, solved the entire problem...:< ... Yes, I am an idiot, and I'm sorry, but, after working over this UTTERLY INFURIATINGLY stupid line of code for over 48 hours (I'm used to writing things like:

最后,我意识到真正的罪魁祸首是我的眼睛(这不是那么好)..看起来像一个完全合法的引用字符串,同时在运行之前读取查询的调试回声,结果显示具有前导和尾随空白区域(当然,我立即删除$ myvariable = trim($ myvariable,“”),当然,这解决了整个问题...:<...是的,我是个白痴,我是对不起,但是,经过这个UTTERLY INFURIATINGLY愚蠢的代码行超过48小时(我习惯写这样的东西:

$query="UPDATE db_one.table_one SET item1='".(string)$result_array[$i][1]."', item2='".(string)$result_array[$i][2]."' WHERE thing3=".(string)$result_array[$i][19];

... and other assorted fun nonsense), I had to resort to asking (because I - no pun intended - couldn't "see" the problem... ugh)... SO... I am an idiot, and I'm sorry (but encouraged by the efforts of all of you nice people who tried to help) and am sorry for wasting everyone's time. I need to learn how to handle XML much *more*.

...和其他各种各样有趣的废话),我不得不求助(因为我 - 没有双关语意图 - 不能“看到”问题......呃)......所以......我是个白痴,我很抱歉(但是你们所有努力帮助的好人的努力让我们感到鼓舞)并且很遗憾浪费每个人的时间。我需要学习如何更多地处理XML *。

Sorry and thanks again!

对不起,再次感谢!

#4

I always wrote it like this

我总是这样写的

$query="SELECT count(somefield) FROM db_name WHERE otherfield='$myvariable' ";

Try removing the double quotes and dot at the variable name

尝试删除双引号并在变量名称处加点

#5

You shouldn't need the parenthesis around the var. Plus look at other changes.

你不应该需要围绕var的括号。再看看其他变化。

$query="SELECT count(*) FROM table_name WHERE field='$myvariable'";

#1