我在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. }更多推荐
MySQL变量和PHP
发布评论