MySql and php variables
I am getting an error in php. What is the correct way to format this string to go 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;
In php, this is how I set up $ 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";
This 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?
I am getting the following error:
You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to "SELECT user_agent_parsed, user_agent_original, COUNT (user_agent_parsed) AS thecount" on line 3
The reason for not using sub-selection and instead selecting the MySql variable is to avoid evaluating count () on every percentage calculation. While this may be possible, the engine will optimize for this. So far, I have not been able to confirm this. I've also heard that sub-selections are almost always sub-optimal.
EXPLAIN tells me the following:
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
source to share
You can only have one request 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";
UPDATE
I have a DAL that contains all of my requests. 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;
}
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.
}
source to share
If you are using mysql_query
, then yes, you need to submit each request separately. From the description at the top of the mysql_query
PHP manual entry : "mysql_query () sends a unique query (multiple queries are not supported) to the currently active database ..."
As far as subqueries are concerned, you might be surprised; the query optimizer usually does a great job with them.
source to share