MySQL变量和PHP

本文介绍了MySQL变量和PHP的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在php中遇到了一个错误.格式化此字符串以在php中传递给mysql_query()的正确方法是什么?

I am getting an error with this in php. What is the correct way to format this string to pass to mysql_query() in php?

SELECT count(*) FROM agents INTO @AgentCount; SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount, COUNT( * ) / ( @AgentCount) AS percentage FROM agents GROUP BY user_agent_parsed ORDER BY thecount DESC LIMIT 50;

在php中，这是我设置$ query

In php, here is how I set up the $query

$query = " SELECT count(*) FROM agents INTO @AgentCount; SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount, COUNT( * ) / ( @AgentCount) AS percentage FROM agents GROUP BY user_agent_parsed ORDER BY thecount DESC LIMIT 50";

如果我通过命令行会话将其直接放入MySql，则该查询将正常工作.我是否需要对mysql_query()发出两个单独的php调用并存储第一个结果?

That exact query will work fine if I put it directly into MySql via a command line session. Do I need to issue two separate php calls to mysql_query() and store the first result?

我收到以下错误:

您的SQL语法有错误；在第3行的'SELECT user_agent_parsed，user_agent_original，COUNT(user_agent_parsed)AS thecount'附近使用相应的语法，查看与您的MySQL服务器版本相对应的手册

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 'SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount' at line 3

不使用子选择而是选择MySql变量的原因是为了避免在每个百分比计算中都发生count().尽管引擎可能对此进行了优化.到目前为止，我还无法确认.我还听说子选择几乎总是非最佳的.

The reason for not using a sub select and instead choosing a MySql variable is to avoid a count() happening on every percentage calculation. Though it may be possible the engine is optimizing for that. So far, I have not been able to confirm that. I have also heard sub selects are almost always non optimal.

EXPLAIN告诉我这一点:

EXPLAIN tells me this:

id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY agents index NULL user_agent_parsed 28 NULL 82900 Using temporary; Using filesort 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

推荐答案

在PHP中，一次只能有一个查询.

You can only have one query at a time in PHP.

$query1 = "SELECT count(*) FROM agents INTO @AgentCount" $query2=" SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount, COUNT( * ) / ( @AgentCount) AS percentage FROM agents GROUP BY user_agent_parsed ORDER BY thecount DESC LIMIT 50";

更新

我有一个包含所有查询的DAL.我的DAL中的典型功能如下:

I have a DAL that contains all my queries. A typical function in my DAL looks like this:

// These functions are reusable public function getAllRows($table) { $sql =" SELECT * FROM $table"; $this->query($sql); return $this->query_result; }

然后在我的BLL(业务层)中，我具有以下内容:

Then in my BLL (Business Layer) I have the following:

public function getUserAgents() { $result = parent::getAllRows(); $row = mysql_fetch_array($result); return $row[0]; // Retrieves the first row // Then you take this value and to a second request. Then return the answer / rows. }