I have this mysql table:
CREATE TABLE person (id INT(6) AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,age int(4) NOT NULL);
I run these inserts: INSERT INTO person VALUES(null, 'one', 42); INSERT INTO person VALUES(null, 'two', 49); INSERT INTO person VALUES(null, 'three', 16);
Running:
select avg(age) from person;
gives me "35.6"
I then index these table in Sphinx. Now I want to run more or less the same query in Sphinx as the one I run in MySQL. From command line I issue this command:
select avg(age) from person_idx;
Fine. Even here I get "35.6".
Now to the problem. I need to run this query from PHP. Im using sphinxapi.php script that comes with Sphinx-release. I had a look at the docs and googled a lot. But I cannot find a way to run these kind of functions (avg, sum etc) from PHP. Is there a way to do this?
In PHP I have this code:
require('sphinxapi.php');
$client = new SphinxClient();
$res = $client->Query('', 'person_idx');
Where do I put the "select AVG(age)" part of the sql-query? sphinxapi.php has a methods you can call. But I cannot see a way to decide what to select.
Add before the Query() call
$client->setSelect("AVG(age) AS avg_age");
setSelect is roughly equivalent to the 'SELECT ...' part of sphinxQL. But in general you need to use AS for all function use.